cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisj
Level: Powered On

Determine if record exists in data source

Hi all,

 

I have a data source linked from CRM and an attribute on this entity called SRP Client Code (this value is a number, not string) which acts as the primary key.  I am trying to see if this ID already exists before assigning it, and if it does/doesn't exist, display a green check or a red x respectively.  This is the formula I am using:

 

Accounts_1 = Data source

GenerateCode_Output.Text = Label that contains the generated id

 

Set(new_client_code,LookUp(Accounts_1, 'SRP Client Code' = Value(GenerateCode_Output.Text));
If(IsBlank(new_client_code),Success.Visible=true;Failure.Visible=false,Failure.Visible=true;Success.Visible=false))

 

Initially I have both the green check and red x images visibility turned off, and the upon clicking the 'check code' button, neither image displays.  Please see attached image for better idea.

 

Thanks in advance.

 

Edit:

 

I have also tried the following without any success:

 

If(CountRows(Distinct(Filter(Accounts_1,'SRP Client Code'=Value(GenerateCode_Output.Text)),'Account Name'))=0,Success.Visible=false,Failure.Visible=false)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
chrisj
Level: Powered On

Re: Determine if record exists in data source

Hi all,

 

Ended up creating two buttons, one called 'check code' and the other 'assign code'.

 

check: 

If(IsBlank(LookUp(Accounts_1, 'SRP Client Code' = Value(GenerateCode_Output.Text))),UpdateContext({client_code_exists: false}),UpdateContext({client_code_exists: true}))

 

assign:

Patch(Accounts_1,First(Filter(Accounts_1, 'Account Name' = Accounts_CB.Selected.Result)), {'SRP Client Code': Value(GenerateCode_Output.Text)});

6 REPLIES 6
TimRohr
Level 10

Re: Determine if record exists in data source

Pull the If() statement out of the process where you Set() the variable. Just Set() the variable when you generate the potential ID.

Then, in your Success (green check) and Failure (red X) controls, set their Visibility properties to read the variable as follows:

for Success----->    IsBlank(new_client_code) && !IsBlank(GenerateCode_Output.Text)
for Failure----->    !IsBlank(new_client_code)

This should solve your issue as it stands...

 

...but I have to point out the possibility of data collisions with multiple users in this app. If you don't somehow "lay claim" to the ID at the same time that you generate it and test that it is valid in the data source, you could have 2 people generating the same ID, checking against the datasource, having it report as "valid", and then later trying to commit their record. Only the first person will get their info to write. The second person will suffer a collision.

 

TimRohr
Level 10

Re: Determine if record exists in data source

Although I have seen squirrelly results with global variables in this kind of case. You might try a context variable, instead. Or your CountRows() option...

 

The point is that the Visibility property of the checkmark and X is a boolean value, and it will accept a formula that produces a boolean... so you can put your test right in their property lines:

(CountRows(...)=0)

!(CountRows(...)=0)

...OR...

IsBlank(LookUp(...))

!IsBlank(LookUp(...))

...OR...

IsBlank(Filter(...).'SRP Client Code')

!IsBlank(Filter(...).'SRP Client Code')

 

(You don't need your Distinct() function to wrap the Filter(), since all you are looking for is the binary choice of whether there are rows that match the Filter(), or not.)

 

 

Highlighted
MRNBagtas
Level: Powered On

Re: Determine if record exists in data source

Hi, @chrisj !

 

I tried the same scenario and this worked fine for me:

 

table1  - Datasource (this is a collection) 

Unit - Column in the data source
TextInput1 - input text box or can be a label to compare with

-------------------------

if success (Visibility property of check mark):

If(CountRows(Search(table1, TextInput1.Text,"Unit"))>=1,true ,false)

if not (Visibility property of x mark):
If(CountRows(Search(table1, TextInput1.Text,"Unit"))<1,true ,false)

Let me know if I understand the issue correctly or if this works out fine for you Smiley Happy 

chrisj
Level: Powered On

Re: Determine if record exists in data source

Hi all,

 

Ended up creating two buttons, one called 'check code' and the other 'assign code'.

 

check: 

If(IsBlank(LookUp(Accounts_1, 'SRP Client Code' = Value(GenerateCode_Output.Text))),UpdateContext({client_code_exists: false}),UpdateContext({client_code_exists: true}))

 

assign:

Patch(Accounts_1,First(Filter(Accounts_1, 'Account Name' = Accounts_CB.Selected.Result)), {'SRP Client Code': Value(GenerateCode_Output.Text)});

TimRohr
Level 10

Re: Determine if record exists in data source

And what happens with the following scenario:

 

Time: 15:00:00 -- User1 checks code "ABC" availability. Gets positive response.

Time: 15:00:01 -- User2 checks code "ABC" availability. Gets positive response.

Time: 15:00:02 -- User1 assigns code "ABC" to record. Success.

Time: 15:00:03 -- User2 assigns code "ABC" to record...

chrisj
Level: Powered On

Re: Determine if record exists in data source

Thankfully this app will be used by only one person :^)  Appreciate your input.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

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

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 174 members 5,113 guests
Please welcome our newest community members: