Hi there!
I created an example for you here: Community #2753 - Grist
There is no good way to compare a list with a list in Access Rules but what we can do is add a helper column in the Members table to find what Items each Member should have access to, then write a condition for that!
I have a table of Members, each assigned to various Roles and Projects.
I have a list of Items, each assigned to various Roles and Projects.
In both tables, Role and Project columns are Choice List columns.
In the Members table, add a new Reference List column labeled Items, containing the following formula:
items = Items.lookupRecords()
items_access = []
for item in items:
has_role = set($Role).intersection(item.Role)
has_project = set($Project).intersection(item.Project)
if has_role and has_project:
items_access.append(item)
return items_access
This formula checks to see if a Role for an Item in the Items table matches a Role for this user then checks to see if a Project for that Item matches a Project for this user. If a Role and Project match, the record is added to the items_access list.
Finally, the formula will return the list of records in the Items table that the user should have access to.
Be sure to make this helper column a Reference List column that pulls data from the Items table.
Now to set up Access Rules -
First, you’ll want to create a User Attribute to match the logged in user to a Member in the Members table. We look up the logged in user’s email with the attribute user.Email then attempt to find a match in the Email column of the Members table. If there is a match, then this record is our Member.
Now we can use our new Member attribute in our Rules for the Items table. First, we grant all permissions to users with OWNER access using the condition user.Access == OWNER.
Next, we add a condition to grant access to Members with an Item listed in the Items column of the Members table using rec.id in user.Member.Items. If the id for an Item appears in the Items column for this Member, then we grant that Member all permissions.
Everyone Else is denied all permissions.
User 1 should only have access to Items[3] and Items[5]. When we log in as User 1, we see they only have access to those Items.
Please let me know if you have any questions!
Thanks,
Natalie
