I have a set of access rules based off 3 variables (e.g., region, school, department) that limits what rows each user can see in a table (e.g., person A can only see rows in his region & his school, person B can only see rows in his department & his region).
The problem I’m encountering is folks can’t enter new data rows properly due to these access rules. For example, if person A is entering a new row, the row automatically poofs & an error message pops up saying they don’t have access rights, since the region & school data hasn’t been entered yet & don’t match what was given under the access rule section.
Are there any ways to bypass the access rules temporarily? I tested entering the limiting variables (which the access rules are based off) first, but as some users have access rules based off 2 variables, it doesn’t quite work.
Add a column named CreatedBy, with a trigger formula of user.Email (set for new records). You can hide this column.
Add a column-specific access rule for columns region, school, department: for the condition user.Email == $CreatedBy and not ($region and $school and $department), give permission “Update”. This allows the row’s creator to make updates, but only while least one of these fields is blank. As soon as all are non-empty, the extra permission no longer applies.
Add a column-specific access rule for the column CreatedBy, denying “Update” permission. This is to ensure this column can’t be overridden by users.
There is another approach if each user should ONLY be able to create rows with region, school, and department that they are specifically associated to:
Add a User_Info table mapping email addresses to values associated with them. E.g. email@example.com has a specific Region and Department associated with her email.
For the table where users add records, you add a trigger formula (triggered for new records) with formulas like
This looks up the Region associated with this user in the User_Info table, and fills it in automatically. That way, you can deny the user all permission to make changes to these fields, but they’ll still get filled in automatically when a new record is added.
I put together an example with the second approach here: Here is an example: Trigger Auto-Fill based on User Attribute - Grist. It doesn’t have actual rules, but has the trigger formulas. I recommend saving a copy and adding a real email address, if you’d like to try it in action.