FileMaker Relationship Setup That Could Contribute To Out Of Control Cascading Deletes

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

When you define a relationship, you can define an option to delete related records  when the parent record is deleted. This is a great way to cleanup orphaned related  records when the parent record is deleted. It is a nice feature but I do ask that you  consider the consequences of this feature about before implementing.


In this file, we have an invoice and a products sold table. In the relationship graph area, we have a relationship from invoices to products sold to easily allow adding products to an invoice. We also have a second relationship from products sold to the invoice table using a second table occurrence in the invoice.

Although this is not an uncommon setup in advanced FileMaker solutions. This second relationship has the cascading delete option checked and it causes us some problems. This example is NOT code you would want to include in your files but more of an example what NOT TO DO.

To illustrate the problem with this design, click the trash icon button in one of the portal rows to test it out. You will notice that all records in the file are deleted! This is an extreme example but you could lose dozens of records with the cascading delete option checked if you are not careful.

The Restore button will bring the two example records back.

An example file can be downloaded by clicking (here)

There is a related movie for this example file. It was inspired from a reader question and is is a bit of a different slant on this setup. It can be viewed from our YouTube channel by clicking (here) or a smaller version can be viewed below.

 

© 2010 - 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.