Assigning a unique reference number to a SharePoint list column via form

(0) Share

Report

Posted on by JimboSey

321

Hi,

I have an App which users send commissions to my team, via a form in the App. I use the ID column to generate a unique reference number, which on submission of the form writes the reference to 'Workplan Reference Number' column in my SP list, using the code below...

If(
 varTabSelected = 1,
 "LST" & Last('DLD Team Commissions').ID + 1,
 "" Or varTabSelected = 2,
 "PPT" & Last('DLD Team Commissions').ID + 1,
 "" Or varTabSelected = 3,
 "LST" & Last('DLD Team Commissions').ID + 1 & "CR",
 "" Or varTabSelected = 4,
 "PPT" & Last('DLD Team Commissions').ID + 1 & "CR", SubmittedGallery_2.Selected.'Workplan Reference number')

This works fine, but, the problem is, if two users as in the form at the same time, they would both have the same ref, and only the first one to submit would submit the form successfully, then second person would have to restart.

Any ideas how to resolve this? Btw, I appreciate my code may not be the best, I am quite new to the world of PowerApps.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

seanbrogan 430 on at

Like (0)

Report
Copy link

Link copied!

Do you have an employee ID number you could use? The hacky method I use to generate a unique number is to convert the date and time into a number and then append the user ID number on to the end. That guarantees that two users cannot generate the same number.

Was this reply helpful? Yes No
JimboSey 321 on at

Like (0)

Report
Copy link

Link copied!

Hi, yes I do have a employee ID number, interested to see how this would work.

Was this reply helpful? Yes No
seanbrogan 430 on at

Like (0)

Report
Copy link

Link copied!

So what you can do is take something like...

Set(ListNumber, DateDiff(DateValue("1/1/22"),Now(),Seconds) & EmployeeID)

I would recommend from there one small change to prevent that number from getting abnormally large though, by dividing and rounding it

Set(ListNumber, Round(DateDiff(DateValue("1/1/22"),Now(),Seconds) / 100,0) & EmployeeID)

This would mean that it would theoretically be possible for one user to generate the same number themselves within 100 seconds (A new number gets generated every 100 seconds) But you can catch that in the app by putting

If(ListNumber = Round(DateDiff(DateValue("1/1/22"),Now(),Seconds) / 100,0) & EmployeeID
,
false
,
Set(ListNumber, Round(DateDiff(DateValue("1/1/22"),Now(),Seconds) / 100,0) & EmployeeID)

So that means if the list number is potentially going to be duplicated nothing happens. In my own case though 100 seconds is more than enough because it takes longer than that to fill out the form

Was this reply helpful? Yes No