I have a simple 2 table application. The parent table is Setups. The child table is Trades. It is a one:many relationship. Each Setups record can have multiple Trades records. Is there a way to protect the Trades records so that a Setups record can’t be deleted if linked child records exist in the Trades table?
Never tried, but I think it’s possible. Child records will have a reference to main table. Each main record can have a cell where it lookUpRecords in the child table that reference their id.
If the lookup returns more than 0, you can have it return “yes”.
Then in the Rules table, you can set a rule for users where they can´t delete records if their lookupcell = “yes”.
2 Likes
Wohoo, Jarek liked my post! It’s like Albert Einstein liking a post you made about Relativity! 
1 Like
Worked perfectly
Thanks for your help
1 Like
I am wondering if a similar system can´t be created to fullfill a problem in Grist and other “Excel like systems”: it’s too easy to accidentaly change a record without noticing what you did. Because every record is open for editing.
Most business systems will have buttons for you to edit a record already saved. And another to save the record being edited.
I THINK the first part can be done in the following way. All records are closed to the user. They have a column for “status”, where depending on status, user can´t edit them.
I guess that will also make it impossible to CHANGE the status so you can edit the record.
But MAYBE an ACTION BUTTON can change the status to “editable” even if the user can´t directly change it.
So user select record he wants to edit, click the action button on top of the page called EDIT, and action button changes the status of the record, so the record is now open for editing.
As for saving. No idea really. Anytime you type anything in any cell, it already saves it.
A possible solution would be to also have a column status where the record is in status EDITING unless user clicks “SAVE” Action Button. While in editing status, the record won´t be available for calculations, etc. It’s like in Limbo.
I put together an example and response to a similar support question, and sharing it here, since preventing child records from becoming orphans seems generally useful. The approach is exactly what @Rogerio_Penna suggested.
The way to do it is using Access Rules. These, in particular, allow controlling when a record can be deleted. The approach is to create a formula column which is True or False depending on whether a parent record has children. When this column is True, the rule can deny the permission to delete.
I created an example that shows it in action here: Prevent Orphaned Records - Grist. Because of Access Rules, to try the example in action, please make a copy, so that you are the Owner of the copy. Then you’ll be able to view the access rules and could try deleting and modifying data. The idea is simple:
-
There is a formula column
Has_Children, in theParentstable, with the formula:bool(Children.lookupRecords(Parent=$id))Which returns
Truewhenever lookupRecords finds any children with this parent, andFalseotherwise. -
There is a rule on the
Parentstable, which denies Delete permission for the simple condition$Has_Children:
If you make a copy and try deleting records A or C, you’ll get an error (with the message that you can write in the Access Rule memo field) because those have children. But record B (with no children) may be deleted freely.
This Rogerio_Penna is a nice fellow. I often meet him, when I look at the mirror haha.
(created a topic about it)
@dmitry-grist I wonder if this sort of behavior couldn´t be directly integrated into Grist. Whenever a reference is created a hidden (just like the ID column is hidden) column is created in the referenced table. Maybe when creating a reference, there could be a check for “protect orphaned data”, if checked, the lookup column is created at the referenced table and the Access Rules are AUTOMATICALLY created
I talked about some other similar functions here (editing and saving records buttons, based on status column functionality and Access Rules based on those status columns.
1 Like