Security filter help

AppSheet AppSheet Q&A
, ,
1

I’ve been at it for a while today, maybe I’m overlooking an obvious solution but here goes.

I have a table with an ID field and an email field among others. How can I convert a list of IDs from that table of unknown length, to a list of corresponding emails?

For context, this concerns three of my tables - owners, properties, and property specialists. Each property is assigned one owner and one specialist. This is for a security filter on the owner’s table with the goal of only displaying owners for which the user is assigned as the property specialist for at least one of the owner’s properties. Here’s the query so far. This query is looking for USEREMAIL() in a list of property specialist IDs. It’s the right list of rows but the wrong field. I need to look for USEREMAIL() in a list of property specialist emails…

IN(USEREMAIL(),SELECT(properties[property_specialist],[owner]=[_THISROW].[id]))

A constraint with security filters is you can’t use virtual columns, so the property’s or specialist’s related owner lists are unavailable.

EDIT: The solution is a more elegant approach to security filters. Did you know you can stack security filters (the filter on table 2 can reference the filtered dataset on table 1)? I didn’t.

2

Row filter on My property specialist slice on Property specialists table:

(USEREMAIL() = [Email])

Security filter on Properties table:

IN([property_specialist], My property specialist[ID])

Security filter on Owners table:

IN([ID], Properties[owner])
1 Like
3

Took my a minute to understand how this was even working. The owners table security filter leverages the resulting property list from the property security filter? I didn’t know you could do that. Excellent, thanks.

1 Like
4
  1. Property specialists table loads in full.
  2. My property specialist slice on the Property specialists table resolves to a single row matching the USEREMAIL() of the current user of the app. The one row of this slice now identifies the ID of the property specialist using the app.
  3. The Properties table loads with its security filter including only those properties with a property_specialist column value that matches the ID column value of the property specialist currently using the app, as identified by the one row of the My property specialist slice.
  4. The Owners table loads with its security filter including only those owners associated with the properties loaded by (3).
2 Likes