PowerApps/SharePoint List - Generate automatic ID

(1) Share

Report

Posted on by mate

Hello all.

I have a PowerApp that allows entering inspection data into a SharePoint list (each row is an inspection).

I want to automatically generate an ID that increments every time a new inspection is created in the format FY24/01, FY24/02,...

Is there a way to do so without using SharePoint ID?

I have tried the below formulas but it is linked to the ID and I want to avoid using it as I can't reset it and there will be some gaps.

If(
Form1.Mode = FormMode.New,
Last('Data Source').ID + 1, // Generate an ID value when you add a new record using the app
ThisItem.ID
)

I appreciate your help.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

KeithAtherton 3,705 Most Valuable Professional on at

Like (0)

Report

Hi @mate,

The code you have looks good. It does ID only atm but would need only small changes to make it create an ID using format FY24/01, FY24/02, etc.

Are you looking for code which creates an ID with this format?
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
mate 29 on at

Like (1)

Report

Hello @KeithAtherton,
Yes, I am looking for a different way to generate an ID that is not the SharePoint ID to avoid gaps. Any idea?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
@mate

First(Sort('Data Source', newID, Descending)).newID+1 would be the formula for automatically calculating the next newID. Last() and Max() are not delegatable and will create errors once your list exceeds the row maximum in the Settings. You can add the Month and Year as a prefix as follows:

"FY"&Text(Year(Today()),yy)&Text(Month(Today()),mm)&First(Sort('Data Source', newID, Descending)).newID+1

To automatically set it for a form, you can put it in the Update property of the newID card as

Coalesce(Parent.Default, "FY"&Text(Year(Today()),yy)&Text(Month(Today()),mm)&First(Sort('Data Source', newID, Descending)).newID+1 )

and set the Display mode of the card to View or Disabled so the user can't change it. Coalesce() reads its elements from left to right until it finds a nonblank value. In New forms, Parent.Default is blank so it will use the formula. In edit forms, Parent.Default will not be blank. By putting it in the Update property, the user won't be able to edit it and you can hide the card if you wish. You can also put the second formula in the control inside the card so it can be viewed.

Was this reply helpful? Yes No
mate 29 on at

Like (0)

Report

Hello @Drrickryp,
Thank you for your help!
I believe I am doing something wrong (I am a beginner) and it doesn't work.
In the Update property of my Title card (where I want the Ref number) I have used:
Coalesce(Parent.Default, "FY24/"&First(Sort('FY24 Regulatory Tracker', Title, Descending)).Title+1)
But I get the below errors:
What am I doing wrong?

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (1)

Report
Hi @mate ,

Not sure if you ever got this sorted, but it seems there were some double quotes missing in @Drrickryp 's formula:

Coalesce( Parent.Default, "FY"& Text( Year( Today() ), "yy" ) & Text( Month( Today() ), "mm" ) & First( Sort( 'Data Source', newID, Descending ) ).newID+1 )

Was this reply helpful? Yes No