cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShanP
Level: Powered On

Generate Unique ID

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Generate Unique ID

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)

View solution in original post

6 REPLIES 6
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Generate Unique ID

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)

View solution in original post

NigelP
Level 8

Re: Generate Unique ID

Hi

 

Which event do I hang the Patch Command  off ?

 

Thanks

 

Nigel

CABIRD
Level: Powered On

Re: Generate Unique ID

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.

Highlighted
fer23gs
Level: Power Up

Re: Generate Unique ID

I have the same problem

sheetal
Level: Power Up

Re: Generate Unique ID

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?

 

 

PowerApps Staff lesaltzm
PowerApps Staff

Re: Generate Unique ID

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.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,420)