Prevention against duplicates in SQL table

[Sienna]

If anyone have figure out how to prevent duplicates in unique SQL table column then please share it here. I have already create an idea for it because I think at the moment it is impossible when 2 users press the patch button at a same time with same data on 2 devices

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Prevent-patching-duplicates-in-SQL-unique-column...

[nickduxfield]

Thats an interesting problem

Have you considered creating a list that can also serve as a log/task.

First get the app to write to the log so you can line up items that will be written to the target list.

The first person to populate that list will be the first to populate the target list.

You can action that via Flow.

When Flow processes that Action, it can flag the Log/task as Done.

Duplicates can be determined via the Log/task

If item in Log/task exists && Not Complete, warn.

If item in Log/task exists && Complete && Target list exists, Duplicate Wrn.

Some ideas.

I find that this is a problem with Patch driven forms.

With the Standard Submit based form you have OnSuccess, or OnFailure.

[Sienna]

@nickduxfield
Hi
The point is that SQL table has an option to set the column to unique which means it won’t let you to add duplicates into that column even if 2 users will try to add the same value at the same time. Problem is that the PA doesn't comunicate with SQL on unique column so the PA will try to patch the same value into the unique column anyway which eventually crash the app. I didn’t find the way to prevent it. Validate function doesn’t catch the error...

[nickduxfield]

I see, does MS Flow communicate with the SQL to toggle Unique for a column. You can write a custom action.

How about 2013 Workflow?

[Sienna]

@nickduxfield
I haven't tried to play with a flows yet. But I think this kinda basic functionality should be part of the validate function. As far as I could try the validate function catch most of the errors in SQL appart from this one. If you got time then try it with flow and let me know

[nickduxfield]

Just asking, 

What step crashes the app? Is it the part when the patch reaches the writing to the unique column?

Patch has no validation, only if you write it in to generate some kind of halt in the write and app.

So Patch a Validation, then create item in task list. Popup to continue patch.

Just throwing ideas out there.

[Sienna]

The PA trying to patch the record into a unique column where it get stack and crash. I tried to play with validate even without patch function where I inserted the validate function into a label text property and let it display some text with same value already in the table but it turns nothing
which means the validate doesn’t comunicate with unique sql column. If you got acces to sql then try it so I’m not alone here

[Sienna · Accepted solution]

So after emailing with one of PG I get some answers. 

The crashing or erroring the code out with yellow triangle in studio when you try to record same value into unique SQL column is only runtime error which I assume doesn't have to be collected or protected. The app itself will run properly even after that. I have tried that by publishing the test app and it worked.. You can display an error code with Errors function to user and clear it with Refresh function. However there are still some bugs with errors function. I get Unknow error message on my phone and The requested operation is invalid on my PC