Security Filter User Table

[School_Bus]

Hello great big world. I’m running into a problem. I created a user table to assign access. This particular table has e-mail, district, and location. For district and location I use an enumlist so I can assign more then one thing to that user. I’d like to use that to establish my security filter, but I’m having a problem figuring out the right syntax to make that occur.

I know that if I had built this allowing the user to select it in user settings, this would be easy. It would look like
in([location],usersettings(location))
but alas I need to assign users rights instead of them just selecting it.
So I built it to look like this
in([location],list(lookup(useremail(),“usertable”,“e-mail”,“location”)))

I get no true values when I do that, so I’m not sure what I did wrong here. I’m guessing it’s the list/lookup function, but I don’t know what else I should use.

Please let me know your thoughts

[Marc_Dillon]

The expression appears like it should work fine. You don’t need to LIST() wrapper if the location column is already an enumlist, but it also shouldn’t affect anything in this case.

Put the LOOKUP() expression in some column somewhere and test it to make sure it is returning the correct list.

The table that you’re setting the security filter for also has a “location” column?

Are you sure that your logged-in email has an entry in the user table?

[School_Bus]

Yep, tried the lookup function in a virtual column and it returned the two locations I expected. So that’s working fine.

Yes, the table that I am setting the location filter for also has a column named location.

And yes, the logged in email is also in my table, I can see that works too because my lookup function is working.

I’m using the List wrapper because when I tried it as just a lookup it didn’t allow for multiple possibilities. For example I have two locations, Camden and Smyrna. If I only choose one site then the function works fine with just a look up because it can just match the single location Camden=Camden. I put the list function back in there to try to have a list of possibilities, but it seems that when I have multiple sites, ability to select Camden and Smyrna, the system is returning a false because I don’t have Camden and Smyrna as a site in my record. Should there be another function or combination of functions that I should use to bring back a list?

I’ve gone through just now and changed the column name to “site” instead of “location” to see if there was some odd conflict, but still no luck. All my records turn back as negative. I think I’m not returning a usable list to the function, but I’m not sure of another good way to do it.

[Steve · Accepted solution]

Try:

in([location],split(lookup(useremail(),“usertable”,“e-mail”,“location”), " , "))

[School_Bus]

Yep, that did it. Thank you

[Marc_Dillon]

@steve Can you explain why the split() is needed? Is lookup() not able to return a list value?

[Aleksi]

LOOKUP() is basically the same than ANY(SELECT()) where the result is one value. Even if it’s EnumList, it’s just a string and you need to use SPLIT so it will be list again.

[Marc_Dillon]

Ahh, gotcha!