cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sienna
Level 10

Prevention against duplicates in SQL table

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

1 ACCEPTED SOLUTION

Accepted Solutions
Sienna
Level 10

Re: Prevention against duplicates in SQL table

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

View solution in original post

8 REPLIES 8
nickduxfield
Level 8

Re: Prevention against duplicates in SQL table

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
Level 10

Re: Prevention against duplicates in SQL table

@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
Level 8

Re: Prevention against duplicates in SQL table

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
Level 10

Re: Prevention against duplicates in SQL table

@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
Level 8

Re: Prevention against duplicates in SQL table

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
Level 10

Re: Prevention against duplicates in SQL table

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
Level 10

Re: Prevention against duplicates in SQL table


Sienna
Level 10

Re: Prevention against duplicates in SQL table

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

View solution in original post

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 (3,909)