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 (
Add another column, Access Rule, with the following formula:
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
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