cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Display Next SharePoint ID

Is there a way to auto populate a number field with the next item id of a SharePoint list? So if I have items with the id's of 1,2,4,9 - 10 will be the next one. It should be the highest number of all the items because there will be times when items will be deleted. 

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User II
Super User II

Hi @Anonymous,

I use First(Sort(datasource,ID,Descending)).ID+1. This allows you to avoid problems with large SharePoint lists as Last() and Max() are not delegatable and your list may grow beyond 2000 items. However, be aware that SharePoint automatically assigns a consecutive primary key as part of the list’s metadata and this value is exposed in PowerApps and can be referenced in formula. If this answers your question, please mark the discussion as solved. 

View solution in original post

9 REPLIES 9
Drrickryp
Super User II
Super User II

Hi @Anonymous,

I use First(Sort(datasource,ID,Descending)).ID+1. This allows you to avoid problems with large SharePoint lists as Last() and Max() are not delegatable and your list may grow beyond 2000 items. However, be aware that SharePoint automatically assigns a consecutive primary key as part of the list’s metadata and this value is exposed in PowerApps and can be referenced in formula. If this answers your question, please mark the discussion as solved. 

View solution in original post


@Drrickryp wrote:

Hi @Anonymous,

I use First(Sort(datasource,ID,Descending)).ID+1. This allows you to avoid problems with large SharePoint lists as Last() and Max() are not delegatable and your list may grow beyond 2000 items. However, be aware that SharePoint automatically assigns a consecutive primary key as part of the list’s metadata and this value is exposed in PowerApps and can be referenced in formula. If this answers your question, please mark the discussion as solved. 


According to this page, First isn't delegateable either. But just so I understand, your solution works because Sort is delegatable and so it doesn't matter how many records are in the Sharepoint list, the sort will always put the last record on top (due to descending sort order) and you can grab that via First()?

yep

I have a similar situation to this, however if the last record in the was deleted prior to the new record being patched/added, then the number is incorrect, as sharepoint will have already used the deleted ID number. Any ideas on how to get around this?

Christoph15
Helper I
Helper I

I'm facing the same problem if the last list item has been deleted (and the ID became reserved). Anyone found a workaround for this scenario?

cvetek
Frequent Visitor

Also same problem here

Drrickryp
Super User II
Super User II

@cvetek 

Best practices of database design require that the Primary Key of a table be unique, cannot be null and can never reused once the record that it is assigned to is deleted. Ideally, it should have no significant meaning to data in the table as there is no need for a user to see it.  SharePoint follows this principle with regards to its lists. 

cvetek
Frequent Visitor

maybe better to generate a GUID and use that as the record is being created?

cvetek
Frequent Visitor

actually i think in practice rarely will an item ever be deleted so this won't be an issue. 

 First(Sort(datasource,ID,Descending)).ID+1 works well for my use case

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

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

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (23,703)