You can use Access Rules to restrict a user from creating new records until all mandatory fields are filled in for existing records in a table.
Example Document: ACCESS RULES - Restrict Row Creation - Grist
In this example, we have three columns in our Contacts table; Name, Phone and Email.
First, we need to add a column that checks if all mandatory fields are filled in. We can add a new column, Row Complete? with the following formula:
bool($Name and $Phone and $Email)
This formula uses Pythonâs bool() to return a boolean value (True or False) if $Name, $Phone and $Email exist for this record.
Add another column, Access Rule, with the following formula:
Contacts.lookupOne(Row_Complete_=False)==rec
This formula uses lookupOne to find the first record in the Contacts table where the value in the Row Complete? column is False. Then, if the record found in the lookupOne formula is the same as this row, it returns True. Only one row will ever be True. All others will be False.
Weâll use this column in setting up our Access Rules.
Navigate to Access Rules by clicking the Access Rules link under the Tools menu of the left-hand navigation panel.
Under âAdd Table Rulesâ, select the table where you want to restrict Create access. For this example, we want to restrict record creation in our Contacts table when mandatory data is missing from existing records.
Add the condition not rec.Access_Rule and restrict the Create permission.
Be sure to leave a memo for the condition so users understand why they are receiving an error.
Click the green âSaveâ button at the top of the page to save your changes.
Now, if you attempt to add a new record to the table before all mandatory fields are filled in for existing records, youâll receive the error.
HOW THIS WORKS:
The formula in the Access Rule column is written so that only one row will be True and all others will be False. The row that is True is the only one that can be âcreatedâ. For example, when you start with an empty table, the âAdd Newâ row will have True in the Access Rule column which means the condition not rec.Access_Rule will be false since it is inverting the value with not and the rule wonât apply, allowing you to create your first record. Once the record is created, the value in the Access Rule column will be True which means all others, including the âAdd Newâ row, will be False. Our Access Rules condition will invert this to True, which will cause the rule to be applied and prevent you from creating another record. The only way you can create a new record is by filling out all of the required fields in the last record, changing the value in Access Rule to False and making the value for the âAdd Newâ row True.
