Unanswered

Create automatic unique number in SharePoint custom Form with big DATAset

(0) Share

Report

Posted on by Katrina96

Hi All!
I have SP list with 10000+ items in it. I have a column (single line text) which Name 'ACRNumber', now i am creating custom form with PowerApps. My task is capability to automatically assign a unique ACR number to any submitted ACR form, each ACR number must be unique i.e. ACR Number : ACR2543. In my form this DataCard is invisible.
I understand that i need Sort my column and get the last entry then separate the part with numbers and do +1 , but i cant realize this because of delegation warnings 😞

Id in SP list and ACRNUmber not te same(
Maybe somebode can help and have good ideas to solve such problem,
thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

JR-BejeweledOne 5,836 Moderator on at

Like (0)

Report

You will not be able to get past the delegation warning. Not only is your SharePoint list in danger of having view threshold issues, the limit for SharePoint and Power Apps is 2000 items.

So unless you can bring in a collection using filtering that reduces the number to less than 2000, you won't be able to do what you are trying to do.

Here is an excellent explanation of the SharePoint delegation limitations of Power Apps

https://youtu.be/UaBmVUQx_Fk

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@Katrina96

I would never say never here. It is possible to create a number column in SharePoint and in PowerApps augment it by one without a delegation issue. Once you have your columns of numbers, you can add the next one to it by using First(Sort(SPlist, numbercolumn)).numbercolumn+1 to add the next number in sequence. This will obtain the last number in the column and augment it by one. First(Sort(... is better than Last() or Max() since these are not delegatable. To show it in a gallery you can put in a label and make its text property Concatenate("ACR"&ThisItem.numbercolumn).

A unique number field is actually better than ID because ID is only partially delegatable (only = is delegated), while a number column will allow delegation on additional operators (<, >, <=, >=) . @WarrenBelz has a discussion of "Shadow ID" columns in his blog here https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/

Was this reply helpful? Yes No
WarrenBelz 153,040 Most Valuable Professional on at

Like (0)

Report
Hi @Katrina96 ,

Adding two things to @Drrickryp 's comments.

You can collect the complete list (10,000 should not be an issue on a PC) with this process, however you do not need to do that here (and involves another numeric field as mentioned by @Drrickryp as well).

To set the next ACR number (this puts it in a Variable) you would do this

With( { wLastID: Sort( YourListName, ID, Descending ) }, Set( varNewACR, With( { wACR: Substitute( First(wLastID.'ACR Number'), "ACR", "" ) }, "ACR" & Text( Value(wACR)+1 ) ) ) )

Was this reply helpful? Yes No