Power Community

Power Community

Auto-Increment Custom ID Counter


Here is a simple solution to create an auto-incrementing custom ID to use in your Power Apps.

This solution is nice because it’s so easy to customize to your needs.   The examples used here are for an auto-incrementing ID that contains the year and resets at the beginning of the next year.

Start by creating a SharePoint list with these columns.   Formulas and Starting Values are shown in blue. 

  • Title – Item Title
  • CurrentIDNumber (Single Line of Text) – 0 or the first number below your starting number
  • CustomID (Calculated Column) – =”ATB”&TEXT(Modified,”yy”)&”-“&TEXT(NextIDNumber,”000;(000)”)
  • NextIDNumber (Calculated Column): = CurrentIDNumber +1
  • Year (Single Line of text) – The year you are starting with.

The CustomID column formula can be modified to meet your needs.   You can create the entire ID here or if you need information from the Power App to generate the full ID, you can create the incrementing pieces here and then put them together in the Power App.  Note that:  TEXT(NextIDNumber,”000;(000)”), adds leading zeroes.  To make the ID a specific length, add the number of zeroes you need to both sets in the formula.  

The custom ID using the formula above looks like:  ATB24001, where 24 is the current year and 001 is the incrementing number.   The incrementing number portion will look like 001-009, 011-099, 111 and so on.   To configure it with no leading 0’s use TEXT(NextIDNumber,”0;(0)”.

Next add formulas to your Power App.

Example #1 – Calendar Year

On Start of the app:  This checks the year and if the current year, obtained by extracting the Year from the dynamic Today() formula, does not match the year in your counter, the counter will be reset and the year updated to the current year.

Set(varYear, Year(Today())); Set(varIndexYear, LookUp(YourListName, Title = "ItemTitle").CurrentYear);
UpdateIf(YourListName, Title = "ItemTitle" && CurrentYear <> Text(varYear), {CurrentIDNumber: 0, CurrentYear: Text(varYear))

On Submit – These formulas must run before any other submit processes

Set(varIDCounter, LookUp(YourListName, Title = "ItemTitle").NextIDNumber);
Set(varFullID, LookUp(YourListName, “ItemTitle” = ItemTitle).CustomID);
UpdateIf(YourListName, Title = "ItemTitle", {CurrentIDNumber: CurrentIDNumber + 1})

Set your custom ID field to:  varIDCounter.  By design this number will load at the end.  This is to prevent duplicate IDs from being created.

Example #2 – Fiscal Year

Add an additional column to the SharePoint list

  • FiscalYear (Single Line of Text) – The starting fiscal year, i.e. 23-24

Modify the formula in this column

  • CustomID (Calculated Column) – TEXT(NextIDNumber,”0;(0)”)

The CustomID looks like this:  11    The rest of the custom ID is put together in the App instead of in the list.

On Start of the App:

Add this to the on start formulas

Set(varFiscalYear, LookUp(YourListName, Title = "ItemTitle").FiscalYear)

Modify the UpdateIf formula as shown

UpdateIf(YourListName, Title = "ItemTitle" && CurrentYear <> Text(varYear), {CurrentIDNumber: 0, CurrentYear: Text(varYear), FiscalYear: $"{Right(varYear, 2)}-{Right(varYear, 2)+1}"});

Modify this On submit formulas:

Set(varID, $"IT/{varFiscalYear}/{First(Split(varIDCounter, ".")).Value}");

This post was originally published on this site

- Advertisement -spot_img


Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Preferred Solution | New feature | Microsoft Dataverse

Click on the below image to navigate to my YouTube Channel. Please like and share your valuable feedback on this...

More Articles Like This

- Advertisement -spot_img