cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
JR-BejeweledOne
Super User
2 weeks ago

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}");

 

 

 

 

 

 

 

 

Labels: