SharePoint Online List ID with incremental number

[Lefty]

Hello,

I have set up an incrementing reference number using the SharePoint ID column.

I hadn't noticed it was not using the ID field, until i deleted a bunch of records, it started using the last number in my RefNumber column as opposed to continue incrementing based on the ID column. Can someone advise what I've done wrong:

On App: 

Set(vRef,First(Sort(List,ID,Descending)).ID);

 On the RefNumber field:

Concatenate("AY",Text(vRef + 1, "[$-en-GB]0000"))

Currently in my SharePoint list, the ID field is 40 but my RefNumber is AY0022

The reason why I thought it was using the SharePoint list ID was when i set up my RefNumber  it did not start from AY0001 rather it picked up the same number as the List ID AY0017

[leyburn19]

It all looks correct to me.  But I would simply create a button with Set(vRef,First(Sort(List,ID,Descending)).ID) see the result created in the variable.  1 it matches the ID and 2 it is a number (not returning text)

[v-xida-msft · Accepted solution]

Hi @Lefty ,

Based on the Set formula that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. The Set formula that you mentioned would store the Maximum ID value from your SP list into the vRef variable.

If the Maximum ID value in your SP List is 40, then the vRef variable would be 40. Please make sure the Maximum ID value in your SP List is 40 rather than 21.

Within your canvas app, please make sure the vRef variable is populated with proper ID value. As an alternative solution, I think it is not necessary to store the ID value into a variable, instead, you could reference the Maximum ID value from your SP List directly. Please consider set the Default property of the RefNumber TextInput Box to following:

Concatenate(
              "AY",
              Text(
                   First(Sort(List, Created, Descending)).ID + 1, // Or type First(Sort(List, ID, Descending)).ID + 1
                   "[$-en-GB]0000"
              )
)

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Lefty]

Hi @v-xida-msft 

works perfectly

Thanks

[Lefty]

Hi @v-xida-msft 

It seems regardless of what I use on the default property of the text input in my data card, if its the first time a user is completing the form, it seems to always use AY0001 even though currently the SP ID field is 49.

I think it is do with the SPO Item level permissions I have set up, where a user can only view and edit items they have created.

What I cannot understand is why the ID field is not being referenced by PowerApps rather it seems its not looking at the ID field if its a new users ?
This is my code on the RefNumber field in PowerApps:

Concatenate("AY",Text(First(Sort(ListName,Created,Descending)).ID + 1, "[$-en-GB]0000"))

 I'm fairly certain this isn't working because of the item level permissions set up on the SPO list, as the admin of the site, if i do a new form via PowerApps, it correctly skips the ID49 (which is set as AY0001 for this new user) but if i submit a new form,  it gives me AW0050 skipping ID49 which was used by the above user.

Would be great if you can help fix this

Any ideas how I can fix this please