cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

If Statement Lookup for list record

Hi All,

 

I have an app, where I only want a button to be visable if the current users email address matches a record stored within a Sharepoint List called 'Users'.

 

I have been trying to achieve this with a IF Statement with the Lookup function, however I have been unable to get it to work, I can't get my head around the syntax.

 

Current Code: 

If(LookUp(Users, Txt_Email = User().Email, true), true, false)

 

Hope someone can help, thanks 🙂 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: If Statement Lookup for list record

Hi @InformedHawk

 

The issue here is that when the predicate of a LookUp function includes Users().Email (or the return value of something else), a query that is usually delegable will become non-delegable.

 

If you were to hardcode your email address into the expression like so...

 

Not(IsBlank(LookUp(Users, Txt_Email = "yourname@domain.com")))

...you'll hopefully find that this results in a delegable expression that works (ie one without the blue lines). If so, you can resolve your problem by storing the result of User().Email in a variable, and using this value in your call to the Lookup function.

 

To do this, you would set the variable somewhere in your app (for example, the OnVisible property of your screen) using the following code:

 

UpdateContext({currentEmail:User().Email})

You should then be able to make Carlos's formula work by adapting it like so.

 

Not(IsBlank(LookUp(Users, Txt_Email = currentEmail)))

With regard to ignoring the warning, choosing to do so will result in incorrect results when the number of rows in your data source exceeds the number that's specified under App settings > Advanced Settings > Data row limit for non-delegable queries. I assume this is the case for your Users SharePoint list, which explains the incorrect behaviour that you're seeing.

View solution in original post

5 REPLIES 5
Highlighted
Power Apps
Power Apps

Re: If Statement Lookup for list record

You probably are looking for something along the lines of the expression below for the Visible property of the button:

Not(IsBlank(LookUp(Users, Txt_Email = User().Email)))

LookUp will return a blank item if it doesn't find any matches, so IsBlank will return true if the user is not found, and since you want the opposite of it for the visibility (button visible if the user is found) then you can use Not to invert that result.

Highlighted
Frequent Visitor

Re: If Statement Lookup for list record

Thanks for the reply, what you sent me should work, however when I insert it into powerapps I get the following error and it always returns false

Highlighted
Resolver I
Resolver I

Re: If Statement Lookup for list record

@InformedHawk,

You can ignore the warning. Please make sure the the filed you are storing the email address have correct format and value. 

 

Try to use label controls on the screen and print the values to validate if the values are correct.  

Highlighted
Super User
Super User

Re: If Statement Lookup for list record

Hi @InformedHawk

 

The issue here is that when the predicate of a LookUp function includes Users().Email (or the return value of something else), a query that is usually delegable will become non-delegable.

 

If you were to hardcode your email address into the expression like so...

 

Not(IsBlank(LookUp(Users, Txt_Email = "yourname@domain.com")))

...you'll hopefully find that this results in a delegable expression that works (ie one without the blue lines). If so, you can resolve your problem by storing the result of User().Email in a variable, and using this value in your call to the Lookup function.

 

To do this, you would set the variable somewhere in your app (for example, the OnVisible property of your screen) using the following code:

 

UpdateContext({currentEmail:User().Email})

You should then be able to make Carlos's formula work by adapting it like so.

 

Not(IsBlank(LookUp(Users, Txt_Email = currentEmail)))

With regard to ignoring the warning, choosing to do so will result in incorrect results when the number of rows in your data source exceeds the number that's specified under App settings > Advanced Settings > Data row limit for non-delegable queries. I assume this is the case for your Users SharePoint list, which explains the incorrect behaviour that you're seeing.

View solution in original post

Highlighted
Frequent Visitor

Re: If Statement Lookup for list record

Thanks this method worked perfectly.

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,144)