If statement: Checking a users email against multiple emails from a SharePoint list? Is it possible?

[Alfie_Smith]

Hi!

I've been researching into users and all the metadata that goes with it, and have come across a question for an idea I have, but don't know how to implement.

So, a user is logged into an app. Their email is test@email.com. I have a sharepoint list of emails. 

I want to have a filtered gallery where the users email is checked against the sharepoints list of emails, and if true, show specific set of rows, if not, show nothing. 

So if test@email.com was in the list, the user would gain access to view specific rows in the gallery. I am more focused on the issue of checking the user email against the list of emails. 

Sorry if it poorly explained!

Thanks in advance 🙂

[timl]

Hi @Alfie_Smith 

The filter function would allow you to filter a list by email address.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-filter-lookup 

The syntax you would use would look like this:

Filter(YourSharePointList, YourEmailColumn="test@email.com")

[Alfie_Smith]

Ah I should've explained better.

So theres two sharepoint lists in this.

The sharepoint list with the emails, and other with some data. 

My idea is best described in the following pseudo-code:

If(User().Email = EmailSharepointList.EmailsColumn, OtherSharepointList, Blank())

The issue is shown in bold. Where the User().Email equals any of the emails in the 'EmailsColumn' in EmailSharepointList show the OtherSharepointList data in the gallery. I don't know how to check the user email against all the records in EmailSharepointList. 

I was considering using a hidden gallery with all the emails.

Hope that clarifies 😃

[timl]

Hi @Alfie_Smith 

I think this is what you're trying to achieve.

Hopefully, you get the gist of what the formula does. Feel free to post back if I've misunderstood you.

If(CountRows(Filter(User().Email = EmailSharepointList.EmailsColumn)) > 0,
   OtherSharepointList,
   Blank()
)

Just tagging @Pstork1 here, in reference to your other post. He is correct about saving the user's email as a variable in the App OnStart to avoid delegation problems.

Set(currentuser,User().Email)

[v-siky-msft · Accepted solution]

Hi @timl 

Do you want to check if the login user email is in the EmailsColumn, right?

I agree with @timl 's workaround, but 'CountRows' function is also nondelegable, so I just come to make a little change to make it work.

Set OnStart of App: 

Set(currentuser,User().Email)

Modify the codes as below:

If(IsEmpty(Filter(EmailSharepointList, EmailsColumn=currentuser )) ,
Blank(),
OtherSharepointList
)

Hope this helps.

Sik