How To: Security Filter USEREMAIL CONTAINS

Hello,
I am creating a dispatch app that is used to assign students to volunteer opportunities. Ultimately, I would will be using a security filter so that each student only sees the opportunities they have been assigned to. I have read the following article and watched the app demo video below, but still have a question.

Suppose I am assigning John and Jane to an opportunity together. When I am assigning a student to an opportunity I select which student(s) I want to assign from an enumlist in a column called [confirmed student]. Once the students are selected, their email addresses automatically appear in [Team email]. [Team Email] is the column I would like to use to use as the security filter so that John only sees opportunities assigned to him and Jane only sees opportunities assigned to her.

My question is:
How do I write the expression for the security filter so that it recognizes when [Team Email] contains a useremail instead of when it = useremail.

Thank you for you help!
-Danny

IN(USEREMAIL(),[TEAM EMAIL])
Should work.

6 Likes

Hello Austin,
Thank you for help on the expression. I am struggling on how to use it though.

I do have a table that lists approved users email addresses. Not sure if it is needed for what I am trying to do thought.

I have attempted to use your expression in creating a slice like the video suggests and I get the following errors.

Where is USEREMAIL() collecting the users email address? Is this based on how they logged into the app or do I need to have a list of users created someplace?

I think ideally this would be a security filter and not a slice, but I cannot seem to get that to work either

Yep, that’s what you should be using.

See also:



Is [Team Email] a list field? and is it located in the same table as the slice?
If it is in the same table the field will need to be a list, otherwise if the field is in a different field it needs to not be a list and you should use Table[Team Email]

1 Like

[Team Email] is is the same table where I am trying to set up the slice.

I can get IN(USEREMAIL(),Confirmed Jobs[Team Email]) to be accepted, but it is still not working right.

When I try to view as an email address that doesn’t appear in [Team Email] I see nothing. When I try to preview the app as John@gmail.com (a known user) I see all the data in Confirmed Jobs instead of only the jobs assigned to John@gmail.com

I tried changing the column type from TEXT to LIST; however only Virtual columns can have type LIST. So I created a virtual column [VR Email] and used the expression =[Team Email]. I attempted to have [VR Email] be a LIST, but it did not work. Also tried using type EMAIL, which did not work.

When I use IN(USERNAME(),[Team Email]) I get the same error and the app crashes.

Hello!

So the table I have that lists users is called Students. One column is Email Address, what do I need to do to get that column recognized as USEREMAIL()?

Thank you for your help

If the column is real and you want to be able to select more than one email to be valid you can use EnumList

Hey Austin!

Thank you! I was able to set up a security filter instead of a slice with the method you suggested -

IN(USEREMAIL(),[Team Email])
Changing the [Team Email] to an Enumlist.

This method is successful as long as [Team Email] only contains one email address. There are times when up to 22 students could be assigned to a task, meaning 22 email addresses appear in [Team Email] In this situation the current approach will not work.

While I would prefer to use the Security Filter approach, I will try the expression as a slice to see if this provides the desired results.

Update:
Using IN(USEREMAIL(),[Team Email]) was accepted as valid in the system; however, it is not filtering data the way I am intending. It appears to be filtering in the same way that the Security Filter did.

The IN() formula does not work in slice filters! I had encountered this before and quiet recently as well. @Steve I don’t know if there was ever a ticket or something put in about this but I distinctly remember there being a post about it.

Solution is
COUNT([Team Email)-COUNT([Team Email]-LIST(USEREMAIL()))=0

Explanation:IF count of emails minus count of emails without USEREMAIL() is 0 then USEREMAIL() does not exist.

I use IN() extensively in slice filters and have observed no problems.

:confused:

Please provide a screenshot of the content of the/a Team Email column that contains more than a single student and that isn’t matched by the filter. Please obscure the addresses themselves, but be sure to show all spaces and punctuation.

Hello Steve,

Is it easier to have the screen shot be of my source spreadsheet or the app sheet platform?

Thank you,

This post was my most recent encounter with IN() not working in a slice filter.
No matter what I tried the IN() formula in app would produce either 0 or 1 record and the test for the expression or the view data would show plenty of results. Might be more related to using SELECT or LOOKUP but the IN() formula was the issue as everything performed completely as expected when not using IN() and instead using the COUNT()-(COUNT without item)=0.


This is the previous encounter I had with IN() and slices/security filters giving messed up results.

Probably the spreadsheet. I’m interested in the raw value of the column.

The [confirmed student] is a enumlist where I select the students I want to assign. Their email address automatically populate in [Team Email]

1 Like

In order to use Team Email as an EnumList, the values must be separated by a space-coma-space sequence, rather than by just a comma. Notice how the values in the Confirmed Student column are separated. As you add new values to the EnumList from with the app, AppSheet will use the proper separator sequence for the new entries.

Steve,
Thank you! That makes sense.

The email addresses in [Team Emails] are automatically populated based on the expression below. What adjustments need to be made to the expression to get it to be values separated by a space-coma-space as you suggested.

Thank you,

1 Like