deleting relational records

[jossmeele]

I have built a relational database with one main-table and some 1-many and some many-many (via Join-tables) relationships from the main-table to other tables.

Deleting a record from the main table works fine and deletes related records as expected. However a delete of one of the relational records can be performed without any warning or protest from Filemaker. You at least want to know if one or more main-table records are related to the record you want to delete.

I come from the MSAccess-world and there you are warned if there are active relationships.

So my question is: does FM have any mechanisme for auto detect or do I have to build a script to check for active relations.

Thanks, Jos.

[DukeS]

I usually create button on related records in portal and attach a script to it to delete related records. With script you can check if related table ( even more than one table away ) have record or not with IsValid () function for example.

[jossmeele]

Thanks, shame that FM does not have this kind of thing built in.

Basically relational integrity; i.e. making sure there are no orphna records and child-reocrds are not deleted when parent still "alive", is left up to the developer, correct?

Jos.

[T-Square]

Jos--

Since you don't describe your situation with much detail, it's difficult to determine the correct answer to your trouble, but FM does have referential integrity, and it can be used quite effectively. To implement referential integrity on a table, open the Relationship Graph and open the properties for a given relationship. In the dialog, you have the option to delete records on either side of the relationship.

However, there are situations where relational integrity can't be applied for various reasons.

Let me expound a little:

Take a simple one to many relationship, say Client to Notes. If you delete a Client record, you want to delete every Note record associated with that client, and you set up your relationship to do this.

Now, let's move to a many to many with a join table: Invoices to LineItems to Products. When you delete an Invoice, you want all the LineItems to go. Again, no problem. Similarly, removing a single LineItem is no problem.

But, what do you do if you want to remove a Product? If you cascade delete LineItems (which use ProductID), you put holes in your Invoices, and nothing balances out. In other words, there's no way referentially-speaking to handle this. You have to test for this situation and prevent deletion if a product has been used--ever. Since you're scripting this, it's a simple one-line test to see whether there are linked records to the selected record, and to act accordingly. BTW, I never did like the Access message regarding cascading deletes. I always felt it made sense to a programmer, but not a typical user.

If this doesn't address your situation, let us know and give us some more detail.

David

[bruceR]

Why should there be any warning? What sense does that make? If you want to delete a child record, which seems to be the subject of your question, there is no issue of relational integrity. You can do whatever you want - and if you want warnings then you can add them.

[David Jondreau]

I've never had the case where SOME of the time I wanted a related record to be deleted and other times not. I find the all or nothing checkbox in the relationship to be enough.

[jossmeele]

Well the issue is that if a user is in the child-layout and decides to delete a record it would be good to inform him/her that one or more parent records will loose that information. This is not always obvious to the user as he/she might be concentrating on one particular parent-child relationship. This is of course no problem in the 1-to-many but in many-to-many it matters!!

Jos.

[David Jondreau]

I think I see what you're saying and I'd say you need to script record deletion to check for related records, which you can do if they're in the same TOG.

Otherwise, you may have to add some TOs to your TOG to get what you want.

But it's hard to say without an actual example.

[T-Square]

Bruce--

The warning thing is an Access "feature" that I usually turn off. It says something like "You have requested deletion of a record that will result in deletion of 4 child record(s). Do you want to continue?" As I said, it was made by programmers for programmers.

I also thought I was clear about circumstances under which I thought a cascading delete would be inappropriate.

To put the circumstances in a different light, the relationship between Products and LineItems in my example is not a Parent-Child relationship, but rather one of related items. LineItems aren't generated from the Products table, but from the Invoices table. Using a cascading delete from the Products table would yield problematic results (outlined before), as would allowing deletion without it.

That's why I suggested inserting a test in your Delete script.

Of course, it does NOT help when your users start deleting records using the menus or keyboard. You have to control for that as well. I think (but don't know since I am using 7) that Custom Menus allow you to do that, since there aren't hooks in FM for the OnDelete action.

David

[jossmeele]

Thanks everyone! This has cleared up the issue for me and YES I will need to catch the deletes through a script. FM8 has Custom Menus which is a great way to do this.

Jos.