LinkedIn
Twitter
Scenario
- Multi-user app, with tables for Client, Invoice, Line Item
- Client table contains client’s email
- Invoice table contains Ref to Client row
- Line Item table contains neither the email or Client Ref
That’s the full Line Item table. There are no virtual columns to reference the Client.
Security Filters (Goal)
- Each Client can see their own Client record, plus any Invoices & related Line Items
- App-owner can see all records in all tables
Client
This one is easy because the Client’s email is stored in the row we are filtering.
Invoice
Here, we can use a similar expression, but we have to ‘dereference’ through the [Client] Ref to get the [Email]
Line Item
Normally I would create a virtual column in the Line Item table to pull in the Client (Grandparent) Ref, and access the Email using the same method as in the Invoice table.
However, I’ve just found a new technique:
TIP
- If the Invoice table (child) security filter is working correctly, then Invoice[Key] does not return a FULL LIST of Invoice keys-- it’s only the keys the Client is allowed to see.
- This filtered list can be compared to the existing [Invoice] ref column, instead of having to add a new virtual column.
So if the Line Item references an Invoice they are allowed to see, then they can see the Line Item too. And any Line Items that reference an Invoice they can’t see will be hidden as well.
By defining the grandchild security filter on the visibility of child keys, the grandchild table effectively inherits the security filter from the referenced child table.
This allows for defining the actual filter in only one location, and all child records will follow.
@GreenFlux Don’t worry. Even though I read this article months / years ago, I didn’t understand it very well. Now it was you that made the usecase clear to me 
