Over 46,000+ Business Solution Developers Find answers, ask questions, and connect with our community of business solutions developers, business owners and partners.
In the bottom third of the define relationship operator box, you can see options for each table occurrence in that relationship. These settings include … “Allow creation of records in this table via this relationship”, “Delete related records in this table when a record is deleted in the other table” and Sort Records. The settings are on both sides because FileMaker relationships are bidirectional.
For the purpose of this discussion, we are looking at the “Delete related records in this table when a record is deleted in the other table” setting. You can setup the relationship so that FileMaker will automatically delete related records when a parent record is deleted. This is helpful because it helps eliminate orphan related records. However, all automatic deleting options should be carefully thought out before you implement them. There is no “undo” for record deletion and a problem could go unnoticed for hours, days or even weeks!
Your security account privilege setting can cause the auto related delete setting to quit working. If you do not allow a user to delete records in the related table occurrence, the cascading delete will not work. So you can have an option of allowing the user to delete records in that file and perhaps take away the menu option that makes the Delete Record option available. Another option is to build a maintenance script that cleans up orphaned records attached to another script or an after business hours timed script.
CAUTION: It is possible to have an out of control cascading delete in this situation. It is possible that one deleted record can trigger multiple related record deletions until you have a snow ball effect. Be sure this setting is well planned out and tested before implementation.
PLEASE NOTE: What if that child record you are going to automatically delete is related to another record. The deletion of the child record could hurt that other relationships integrity. So here is where you break out your documentation and give it a very good look over.
© 2007 – Dwayne Wright – dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.