Display Next SharePoint ID

[Anonymous]

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. 

[Drrickryp · Accepted solution]

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. 

[EdHansberry]

---

@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()?

[Drrickryp]

yep

[DaveNicholls]

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]

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]

Also same problem here

[Drrickryp]

@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]

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

[cvetek]

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