cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lefty
Impactful Individual
Impactful Individual

SharePoint Online List ID with incremental number

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
leyburn19
Memorable Member
Memorable Member

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
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lefty
Impactful Individual
Impactful Individual

Hi @v-xida-msft 

works perfectly

 

Thanks

Lefty
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,624)