Generate Unique ID

[ShanP]

In my app, everytime a user creates a form, that form is given an ID. This ID is based on the number of forms currently in the datasource + 1. Meaning if there are currently 10 forms in the datasource, the next created form is generated at 11.

My app also has a delete form function, that deletes the form based on its ID. The problem here is that if a newer form is deleted, such as 9 (and assuming there are 11 forms in the datasource), my app will count that there are 10 forms in the datasource and generate the newest form at ID = 11. So now if that form needs to be deleted, there are two ID 11s, meaning that both will be deleted.

I'm wondering if there is a way to lookup which numbers are unused and assign that unused number as the ID? Or if there is a way to bump the IDs down (if 9 is deleted, 10 becomes 9 and 11 becomes 10)? I am using excel as my datasource.

[CarlosFigueira · Accepted solution]

There's no simple way to find an unused number (more on that below). But you can find the "next number" by using the Max function to get the higher number and increment it. Something along the lines of the code below:

Patch(
    forms,
    Defaults(forms),
    {
        ID: Max(forms, ID) + 1,
        Name: TextInput1.Text,
        Phone: TextInput2.Text,
        ...
    })

When you delete a form from your table, you'll get "missing IDs", but that's usually ok.

There is a way to find the first "unused" ID, using the formula below, but it's not very reliable. The idea is to create a collection with all numbers from 1 to the maximum number of forms that you may have (the '...' below would need to be replaced with the actual numbers). If you believe that you'll never have more than 100 rows in your Excel table, then that would work, but that's an assumption that you can make and be broken in the future, so I wouldn't really recommend that.

Min(
    Filter(
        [1,2,3,4,5,6,7,...,99,100], !(Value in forms.ID)),
    Value)

[NigelP]

Hi

Which event do I hang the Patch Command  off ?

Thanks

Nigel

[CABIRD]

Thank you @CarlosFigueira

I'm still thinking through the best solution to my question. While my original thought is OK for a single user if multiple users were to be using the app form then, when users submit the audit form to SharePoint I'm assuming SharePoint rightly won't accept the user app assigned ID as users would be submitting the same number because users would have acquired the same last ID number. 

Can I assume I use  - Patch( forms, Defaults(forms), { ID: Max(forms, ID) + 1, Name: TextInput1.Text, Phone: TextInput2.Text, ... }) will find the last ID used in the SharePoint list then in Power Apps add 1 to the ID number then when the form is submitted the ID plus 1 number is used? 

Not as easy as I thought and my research suggests there isn't an answer.

[fer23gs]

I have the same problem

[Anonymous]

Hi @fer23gs @CABIRD 

I have the same problem too was wondering if there is any work around to stop duplicating the ID when multiple users are submitting the form?

[lesaltzm]

I would suggest if you need a truly unique ID, incrementing numbers isn't the way to do that. Try using the GUID() function to generate ids.