Create a random number that does not alredy exists in SharePoint list without Flow.

Like (0) Share

Report

Posted on 30 Dec 2021 11:57:40 by Community Power Pla...

Hi Everyone,

I'm building a post-registration form and need to assign a custom, unique ID to each list item using YYYY.<number>.

Initially I built this logic using Flow but since our license results in a 5minute delay I need to figure out another way.

One way to assign a custom ID would be to create a random number on the form (text label) between 0-999999. I however need to check if this value does not already exist in the list and if it does, create a new number.

Is this possible without using Flow?

Thanks!

Categories:

General Questions

All responses (7)

Answers (0)

chairat 64 on 08 Jun 2024 at 00:04:57

Like (0)

Report

Re: Create a random number that does not alredy exists in SharePoint list without Flow.

Your solution is the best ever. Thank you very much @CodrutFt All the best to you and family.
chairat 64 on 07 Jun 2024 at 23:59:35

Like (0)

Report

Re: Create a random number that does not alredy exists in SharePoint list without Flow.

Thank you so much.
CodrutFt 46 on 02 Jun 2023 at 09:17:30

Like (1)

Report

Re: Create a random number that does not alredy exists in SharePoint list without Flow.

Hei, I am not sure if this will solve your problem because you said you might want to reset the numbers every year.
What I found also useful is in generating either a random number or a unique number is that you can work with the previously added item in that sharepoint list.

For example i had to create a 5 digits unique number for every line that will further go in a more complicated project number.
To get a number like that i used Text(Last('yourSPListName').ID)+10000.

I leave this here, because maybe it will be useful for other people.

Cheers.
hemrhk 361 on 30 Dec 2021 at 17:44:41

Like (0)

Report

Re: Create a random number that does not alredy exists in SharePoint list without Flow.

You could have used the timestamp with Date, Text(Now(),"yyyymmddhhmmssfff")
Community Power Pla... on 30 Dec 2021 at 16:10:46

Like (0)

Report

Re: Create a random number that does not alredy exists in SharePoint list without Flow.

Hi Everyone,

My users require(d) a list of unique numbers per year, not over just the complete set. Ideally even with numbering, starting every new year at 1.

Now I tried to fix that with a flow based on the 'item created', but the delay is too long.

Then I opted for the option to combine YYYY with SharePoint's ID in a calculated column, but it turned out that SharePoint calculates the column before giving it an ID, so that didn't work either.

Now I have just made it so that whenever the user opens the form in New mode, a direct Flow is triggered returning a unique ID based on YYYY & random number between 0 and 999999. That's probably the best solution I can come up with to work around the delay and (!) not calculating a number that can be calculated by two users who would work simultaneously.

Thanks for the input though!
rubin_boer 4,841 Super User 2024 Season 1 on 30 Dec 2021 at 15:43:38

Like (0)

Report
Re: Create a random number that does not alredy exists in SharePoint list without Flow.

hi there @Anonymous simply curious why do you need the unique id to be random? will the ID column in SharePoint not meet your need?

as you do not have a loop function in power apps you could use a timer control with the randbetween function to generate this.

for e.g. (data = {1,2,4,5,9})

Add timer

duration = 100

auto start = true

OnTimerStart = Set(__RandBetweenB, RandBetween(1,10))

Repeat = __RandBetween in data.Value

With the above the timer will be true when the value in data matches a generated number and it will stop when a random number is generated which is not in the data.

When the number is generated, the time is switched off at 6

Hope it helps,

R
BCLS776 8,988 Super User 2025 Season 1 on 30 Dec 2021 at 15:32:34

Like (0)

Report
Re: Create a random number that does not alredy exists in SharePoint list without Flow.

Power Apps, as you've probably figured out, does not have a looping function with an exit condition, which is what you would use to compare generate IDs until a unique one comes up. A couple of ways around this:

I frequently use the Now() function to generate a timestamp to work into a unique ID - that's typically unique enough

You can use an If() statement to have a couple (or more when you use nested If()s) of tries to generate a truly unique random number. It is unlikely, but not impossible, that you'll end up with a duplicated random number that way.

Hope that helps,

Bryan