Access rules for multi-roles and multi-projects

Hi, I have a team with different roles and a portfolio of different projects.

Each member can belong to some roles and some projects.

So each member has 2 choice lists - role list and project list
Each item record also has 2 choice lists - role list and project list

I want a record to be seen by someone only in a particular project and a particular role. How to do that?

I tried user.Team.roles == rec.roles but it obviosly won’t work because each is
a) a choice list, and
b) have different combinations of choices

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:
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 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!


1 Like

Thank you so much!

What do “set” and “intersection” do? Can’t find them in the help documents. The following two lines seem to return [Blank] for me:
has_role = set($Role).intersection(item.Role)
has_project = set($Project).intersection(item.Project)

Nevermind. I got it! Thank you so much!

1 Like

Hi @school.

“set” and “intersection” are actually part of Python (the language underlying all Grist formulas), which is why they don’t appear in the help pages. It’s closely tied to the idea of a set in mathematics. Basically, you can think of it as a “set” of values that is guaranteed not to have duplicates. So if you were to pass in a list with duplicates into set(), the duplicates would be “collapsed” into one.

In this example, we’re not actually concerned about duplicates - the reason why we convert the list into a set is because it gives us the ability to then ask it to calculate the intersection of the values in the set with those in another list (i.e. the values that appear in both the set and the other list). So with roles and projects, that means we’ll get back a list of all the roles/projects that both the member and the item have, which may be a blank list.

1 Like