cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
guyinazo
Level 8

Prevention against duplicates in SQL table

I am creating a Power App that is going to assign a network user a simple "private" application user name where I am taking the first inital of their last name and then appending a Round(Rand()*1000),0) to that letter.  So I get something like A123 and then on the registration screen, I am going to let them choose their own 4 digit PIN.

 

Because this app user ID has to be unique, is there any way to iterate through the table and if found, then regenerate until there is no record found?  Is there an IsFound function in Power Apps?  

 

Thanks

 

Brad

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
OneThing
Level 8

Re: Prevention against duplicates in SQL table

Hi Brad,

 

Just Curious why you are creating a username and password for the users? You shouldn't be able to access the App without an office 365 Login so unless you are using a shared Account / Computer, I can't think of a reason you would want to have your users log in twice.

 

I can think of a couple of ways to do this using Filter, isBlank and a Timer to run though until it no longer matches but i seems a horrible solution so I'll let someone else comment before I try and whip that up.

 

Another solution may be to use an auto incrementing key with SQL. If you have less than 1000 users You could use this id as the login and SQL will make sure its Unique.

 

Thanks,
Nicky

 

View solution in original post

2 REPLIES 2
Highlighted
OneThing
Level 8

Re: Prevention against duplicates in SQL table

Hi Brad,

 

Just Curious why you are creating a username and password for the users? You shouldn't be able to access the App without an office 365 Login so unless you are using a shared Account / Computer, I can't think of a reason you would want to have your users log in twice.

 

I can think of a couple of ways to do this using Filter, isBlank and a Timer to run though until it no longer matches but i seems a horrible solution so I'll let someone else comment before I try and whip that up.

 

Another solution may be to use an auto incrementing key with SQL. If you have less than 1000 users You could use this id as the login and SQL will make sure its Unique.

 

Thanks,
Nicky

 

View solution in original post

guyinazo
Level 8

Re: Prevention against duplicates in SQL table

Nicky,

 

The app I am making is one where people are a member of a team, but their data entry needs to remain anonymous, but yet aggregated for team information, if that makes sense?  And I think to make the end users more comfortable is to give them that "anonymous and ambiguous" login (just for this app).  Of course anyone with access to the underlying database or an administrator of the application will have access to that data, but to everyone else, it is "anonymous" outside of a little four character string along with a PIN number.  That is the only reason I am doing it this way.  And when they initially register, or any new member registers, it is likely NOT to happen, but I cannot have any dupes in the User table.

 

This is what I did and I think it will work: 

 

If(CountRows(Search('[dbo].[Users]',WWWID,"User_ID"))>0,UpdateContext({WWWID: Left(lblMyName.Text,1) & Text(Round(Rand()*1000,0))}))

 

It does a search for that ID (stored in the local variable WWWID) and if the returned row count is greater than zero, then there is a match and it will just refresh that WWWID variable with another RAND() number.

 

Brad

Helpful resources

Announcements
firstImage

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.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

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

Top Kudoed Authors
Users Online
Currently online: 185 members 5,028 guests
Please welcome our newest community members: