You’re offline. This is a read only version of the page.
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.
Hi @mate ,
Not sure if you ever got this sorted, but it seems there were some double quotes missing in @Drrickryp 's formula:
Share
Report
All responses (
Answers (
Coalesce(
Parent.Default,
"FY"& Text(
Year(
Today()
),
"yy"
) & Text(
Month(
Today()
),
"mm"
) & First(
Sort(
'Data Source',
newID,
Descending
)
).newID+1
)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?
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.
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?
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.
#1
WarrenBelz
637
Most Valuable Professional
#2
stampcoin
570
Super User 2025 Season 2
#3
Power Apps 1919
473
