Almost each online database comprises reference columns participating in relations. As you already know One-to- Many relation consists of the Master table and the Details table. One Master record can be related to many Details records, while each Detail can be related to one Master only.
There are many questions about automatic deletion of Details records when the Master record should be deleted.
Let’s consider the Driving School online database example, where the Customers table is a Master table and the Customer Payments and the Lessons tables are Details tables. Relation: One Customer-Many Payments (or Many Lessons).
The online database owner has decided to delete outdated customer profiles, because they are not needed any more. He can do it easily using the Delete button displayed near every Customer profile.
The issue is that the Payments and Lessons related to this Customer can’t be deleted simultaneously with the Customer profile deletion.
To solve this issue you may consider these two workarounds:
1. Make Master and Details records inaccessible.
For example, there is the “Status” column with “Active” and “Not Active” choices. Using the “Not Active” status, you can mark outdated customers’ profiles. After that you can easily exclude these records with record access and/or view filter condition instead of deleting them.
The next step is to make related details inaccessible or invisible. For doing this you should create the lookup column referring to the “Status” column.
Using this lookup you can exclude details with the “Not Active” status via record access and/or view filter condition.
When it’s done, the customer profiles with the “Not Active” status and their related details will not be accessible in your online database. Therefore you don’t need to delete these records at all. Moreover, if it will be necessary for some reasons, the record access can be changed and you can make “Not Active” records data accessible again.
2. You can disable master record deletion while details records exist.
This approach can be applied if it is obligatory to delete all data related to a customer from online database. In this case, you need to check the availability of all related details via summary columns.
For example, you can calculate the number of related Customer Payments and Lessons, just create two summary columns with the “# of records” function in the “Aggregate” field.
The next step is to allow to delete Customer records, where the summary column values are 0 (zero).
The table access settings should comprise the following custom rule in the “Allow Delete” section:
To simplify Details records deletion, you can build a Details view with the Mass Delete option and choose this view in the relation settings. So, you’ll be able to delete many records at a time.
When all Customer Payments and Lessons details related to a customer are deleted, the “Delete” button will become accessible for the Customer record.
In TeamDesk online database you may apply any of these two workarounds. Just choose what method works for you better.
