cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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
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
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
Users Online
Currently online: 147 members 5,487 guests
Please welcome our newest community members: