cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Memorable Member
Memorable Member

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
Highlighted
Memorable Member
Memorable Member

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
Highlighted
Responsive Resident
Responsive Resident

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.

Highlighted
Memorable Member
Memorable Member

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

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?

Highlighted
Memorable Member
Memorable Member

@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
Highlighted

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.

 

Highlighted
Memorable Member
Memorable Member

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
Highlighted
Memorable Member
Memorable Member


Highlighted
Memorable Member
Memorable Member

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (3,125)