How To: Security Filter USEREMAIL CONTAINS

[Danny_Frank]

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.

help.appsheet.com

Limiting Users to Their Own Data

You can use Security Filters to control which records a user can see and update.

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

[Austin · Accepted solution]

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

[Danny_Frank]

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

[Steve]

Danny_Frank:

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.

Yep, that’s what you should be using.

See also:

help.appsheet.com

IN()

Is item in list?

help.appsheet.com

List Expressions and Aggregates

List expressions use and/or produce lists of values.

help.appsheet.com

USEREMAIL()

User's email address

[Danny_Frank]

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

[Austin]

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]

[Danny_Frank]

[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.

[Austin]

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

[Danny_Frank]

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.

[Danny_Frank]

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.

[Steve]

Danny_Frank:

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.

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.

[Danny_Frank]

Hello Steve,

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

Thank you,

[Steve]

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

[Danny_Frank]

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

[Steve]

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.

[Danny_Frank]

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,

[Danny_Frank]

@Steve

I poked around a bit and found I could separate the values by changing a setting in the column view.
Thank you and @Austin_Lambeth

For your help! The function now works perfectly!

[Steve]

The column should be type EnumList.

[lerezende]

I put the column as EnumList ando so, worked. Tks...

[Austin]

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.

[Steve]

Austin_Lambeth:

The IN() formula does not work in slice filters! I had encountered this before and quiet recently as well.

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

[Austin]

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.

[Austin]

Data not showing Questions

So I have a slice that has records when I View Data and has True when I test the expression in the expression assistant but none are showing up in my table view. [image] My view is using the right slice. [image] This is the result of hitting View Data in my slice. [image] This is the results when I hit test on my expression. This is my expression:[image] I’m thinking its something to do with how the Lookup returning a list interacts with IN()? Employees not working is also a virtual c…

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.

Security Filters Expression Problem Questions

Hi @Jonathan_S Could following expression be tried ? OR(In(useremail(),Admin Emails[Email]), IN(useremail(), Select(Security Filters[email],[Vendors] = [_THISROW].[ID]))

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