cancel
Showing results for
Did you mean:
Super User

## 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

## 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)```
6 REPLIES 6
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)```
Level 8

## Re: Generate Unique ID

Hi

Which event do I hang the Patch Command  off ?

Thanks

Nigel

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
Level: Power Up

## Re: Generate Unique ID

I have the same problem

Level: Power Up

## Re: Generate Unique ID

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

## 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.

Announcements

#### PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

#### Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

#### Power Platform World Tour

Coming to a city near you

#### PowerApps Community User Group Member Badge

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

#### Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

#### Power Platform Summit North America

Register by September 5 to save \$200

Top Kudoed Authors
Users Online
Currently online: 374 members 5,572 guests
Recent signins: