How do I prevent duplicate number generation?

Like (2) Share

Report

Posted on 16 Apr 2025 12:15:44 by futr_vision

825 Super User 2025 Season 1

I have an app that is used to create tracking for marketing campaigns. When a user goes to create a new tracking URL, or copy an existing one, the system is supposed to autogenerate a new sequential numeric ID and assign it to the user. If a concurrent user were to also start creating a tracking URL the programming is supposed to look at the table that contains these autogenerated IDs and get the next ID in sequence. Theoretically, this should prevent any user from having the same numerical ID. However, as you can see in the image below, both users (two different people) got the same numeric ID. I'm not certain how this happens and was wondering if anyone had any ideas about what is going on and if it can be fixed.

That being said, the example below is not technically an issue for our systems. The ID, in its entirety, is unique but I have had cases where they are identical and that is an issue. To at least catch when these issues happen, I have a Power Automate action that checks to see if the submitted ID already exists and if it does I get an email and hopefully I can catch on of the users before they go live with the tracking URL that contains this ID.

Categories:

General Questions

All responses (9)

Answers (0)

Suggested answer

BCBuizer 22,027 Super User 2025 Season 1 on 23 Apr 2025 at 19:42:06

Like (0)

Report
How do I prevent duplicate number generation?

Hi!

What you may do, is to save the generated ID by the patch function in a variable which can then be used for furhter use:

Set( gblGeneratedPlacementID, Patch( 'Placement IDs', Defaults('Placement IDs'), {'ID Used': "No"} ).'Generated Placement ID' )

Like this, there is only 1 moment in time where an ID is generated which should ensure uniqueness. A downside of this solution is that if users decide to abort their actions, the used IDs will not be continuous.

If this reply helped you in any way, please give it a Like 💜 and in case it resolved your issue, please mark it as the Verified Answer ✅.
ronaldwalcott 3,530 on 17 Apr 2025 at 21:03:14

Like (1)

Report

How do I prevent duplicate number generation?

When the button is clicked you run this

Patch(
'Placement IDs',
Defaults('Placement IDs'),
{'ID Used': "No"}
);

but when do you run this

First(
Sort(
'Placement IDs',
'Generated Placement ID',
SortOrder.Descending
)
).'Generated Placement ID'

The proposed solution below by Bhaskar

Set(
varNewPlacementRecord,
Patch('Placement IDs', Defaults('Placement IDs'), {'ID Used': "No"})
)

ensures that varNewPlacementRecord contains the new ID value that is patched then saved in a variable so that you don't need to access the table sort and get the first record. His method is more efficient for your approach and should eliminate duplicates.
stampcoin 676 on 17 Apr 2025 at 20:49:52

Like (0)

Report
How do I prevent duplicate number generation?

futr_vision Hi,

kind of like this one.

If( // Check: does any record already have this ID? IsBlank( LookUp( MyTable, AutoNumField = varMyID ) ), // --- if blank (i.e. not found) then go ahead and create Patch( MyTable, Defaults(MyTable), { AutoNumField: varMyID, /* …your other fields… */ } ), // --- otherwise increase the current id with feed, for example +1, and patch. Patch( MyTable, Defaults(MyTable), { AutoNumField: varMyID +1, /* …your other fields… */ } ), )

to enhance the ID unque , you can go to the table, add that column as key.
futr_vision 825 Super User 2025 Season 1 on 17 Apr 2025 at 20:20:31

Like (0)

Report

How do I prevent duplicate number generation?

@stampcoin How would I do the check?
stampcoin 676 on 17 Apr 2025 at 20:07:37

Like (0)

Report

How do I prevent duplicate number generation?

@futr_vision Hi,

I agree with BhaskarDhone, , one more thing: you can check if current ID if exists in table or not. if not patch it. otherwise generate a new one and patch it.
futr_vision 825 Super User 2025 Season 1 on 17 Apr 2025 at 18:35:23

Like (0)

Report

How do I prevent duplicate number generation?

Thanks. The times, when I have seen duplicates, are often minutes apart not milliseconds. The example I provided was around 4 minutes apart. That was at the time if submission so I could see how they might have requested an id around the same time.

I'm not really sure I understand how your code solves the problem. The initial patch happens when the user clicks the 'New' or 'Copy' button. You're saying that the retrieve, of the most recent placement ID, is happening before the patch is complete?
BhaskarDhone 810 Super User 2025 Season 1 on 17 Apr 2025 at 18:06:15

Like (1)

Report

How do I prevent duplicate number generation?

This issue is happening because you're retrieving the most recent auto-numbered record.
This isn't reliable in multi-user environments, since another user may have created a record millisecond before or after.
Best way will be to store recent patch in a variable and then use variable to display.

Set(
varNewPlacementRecord,
Patch('Placement IDs', Defaults('Placement IDs'), {'ID Used': "No"})
)

Then use it like

varNewPlacementRecord.'Generated Placement ID'
futr_vision 825 Super User 2025 Season 1 on 17 Apr 2025 at 14:21:54

Like (0)

Report
How do I prevent duplicate number generation?

Thanks. I am actually using a Dataverse table with an auto-number column. The auto-number is being incremented like this

Patch( 'Placement IDs', Defaults('Placement IDs'), {'ID Used': "No"} );

This Patch command is fired when a user clicks on 'New' or 'Copy'. When you click on either of these buttons you are brough back to the form screen where the most recent placement ID is retrieved which should be the one created with the patch command. This is the code I use to retrieve the placement ID

First( Sort( 'Placement IDs', 'Generated Placement ID', SortOrder.Descending ) ).'Generated Placement ID'

I assume that the patch command is failing to execute or a user is getting to the form screen in some way other than the 'New' or 'Copy' buttons. The only thing I can think of is that they are using the back button but that doesn't seem plausible since the duplication is happening with two different users. I suppose it is possible for one user to submit a form and then another user, that already had the app open, uses the back button. Seems unlikely but possible. If it is a back button issue, I'm not sure how to mitigate that.
BCBuizer 22,027 Super User 2025 Season 1 on 16 Apr 2025 at 19:19:08

Like (0)

Report

How do I prevent duplicate number generation?

Hi @futr_vision,

In my experience it is not possible to generate unique consequential numbers unless they are generated at the data source, for instance a SharePoint ID column or a Dataverse autonumber column, so I suggest using those if you want to be certain.

In case this is not an option, perhaps you can share how you are currently assigning the ids? I see there is a 4 minute gap between the two ids in your example, which suggests it takes quite long to assign or save an ID. Perhaps the performance can be improved to reduce the window within which there is a chance of a duplicate being generated.

If this reply helped you in any way, please give it a Like 💜 and in case it resolved your issue, please mark it as the Verified Answer ✅.