cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lefty
Post Prodigy
Post Prodigy

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.

View solution in original post

Hi @v-xida-msft 

works perfectly

 

Thanks

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (66,791)