Question about cascading deletes

[Mike Soultanian]

Hi,

This is my first post on this message forum so I hope I'm posting this in the correct place. Here is my setup:

I have a relational database with two files. One stores events, the other stores event activites. It's a 1 to many relationship respectively based on related eventIDs. The relationship is working great.

What I'd like to happen is when an event is deleted from one file, the associated activities will be deleted from the other file so I don't have orphaned activites. This was easy for me in Access because I would just check off "cascade delete" in the relationship between the two tables and voila, it's done. However, I'm not exactly sure how to do this in filemaker.

What I mean is when I go to the "Edit Relationship" window in one of the files, there are two places to select "Delete related records in this table ..." - the left column being the table in that file, right being the relationship name. Then, when I go to the other file, I have a similar setup.

So which "Delete related records in this table..." checkbox should I check to get the desired results?

Any help would be much appreciated!

Thanks!

Mike

[mr_vodka]

If you are in your parent file, and the table from your parent file is on your left, then on your right is the child table/file. You would want to check the right side box to delete the child records when the parent record is deleted.

[Mike Soultanian]

Hey John,

Just want to make sure.. when you say parent file, are you referring to the file that stores the primary key (Events) and not the foreign key (Event Activities).

Also, just out of curiousity, and so I understand FM a little better, when would I check off the box in the left column - under the table name, not the relationship name.

Also, why wouldn't I check off "Delete related records..." in the file that stores the foreign key? This is the part that confuses me. Does the parent file control the child file when there's a relationship?

Thanks for your help!

Mike

[mr_vodka]

If you have it checked off the other way, whenyou delete a child record, the parent record would be deleted as well.

Prior to FM7 and the ability for tables, you would have to create 2 relationships between files. one going from fiel 1 to file 2, and the other one goifn from file 2 to file 1.

With the ability for tables in 7 and 8, relationships became bi-directional. Now only one is needed between tables. For files, you now have to create a file reference to each other and then put an instance of the table on your relationships graph to use it.

? Why are you using 2 files instead of 2 tables for you solution?

[Ginxy33]

If you have "grandchildren" not sure what it's called, connected to the child table that is being deleted by the relationship to the Parent table, is the grandchild table also deleted?

Does it cascade all the way down?

[Mike Soultanian]

I was waiting for that question. I hate the multiple file setup from the first time I touched this DB, which was in FM5. On top of that, this database had so many bad file references it was rediculous. Thank god for fm8 advanced because it's reporting tool was awesome to clean up everything.

Unfortunately, I'm stuck for the time-being with the multiple file mess as it's not really my job to maintain this FM database.

Now, you mentioned that 8's now bi-directional. Does that still hold true even with file-to-file relationships or just for multiple tables internal to one file.

So, since I'm in 8 are you saying that I don't have to create another relationship from my child file to the parent if I want to reference a field in the parent file from the child? I have a relationship right now in both directions so I am able to do that. Boy I *wish* I didn't have to deal with these multiple files..

mike

[Mike Soultanian]

That's interesting. I wonder if you'd have to specify on the child file that when you delete a record in the child file that it will delete the record in the grandchild.

Mike

[Ender]

Now, you mentioned that 8's now bi-directional. Does that still hold true even with file-to-file relationships or just for multiple tables internal to one file.

No, each file needs its own copy of the other file's table occurence(s).

[Ender]

If you have "grandchildren" not sure what it's called, connected to the child table that is being deleted by the relationship to the Parent table, is the grandchild table also deleted?

Does it cascade all the way down?

Not necessarily. Each relationship has this delete option, and each is independant of the others. If you want to delete the grandchildren too, then the delete option must be checked on the Child <=> Grandchild relationship.