Cascading delete won't delete!

[keshalyi]

Grrr!

I have a database that stores virtual information for paginating a newspaper. The structure goes like this:

One Issue to Many Pages

One Page to Many Foreign Items (info on stuff that was put on the page before the database examines it)

One Page to Many Gaps (Blank spaces on the page)

Now, here's the problem. In the Issue DB, if I delete a page, it deletes all of its foreign items records, but not all its gaps. The two are related in the same fashion, and the relationship to Gaps is valid. Both have the 'delete related records' box checked in relationships. Nonetheless, no error, nothin, just a whole bunch of orphan Gaps. Thoughts? Feelings?

[falkaholic]

My thought is that FM just doesn't cascade that way. I've never tried it myself.

I feel like this is a bad thing.

You might have to do the delete by script.

in the page db, use the show related records (only related) then delete that found set.

[Vaughan]

It can delete this way. I've emptied whole database like this... try it with a self-join some time.

If any records are left over they could be orphans or some incorrectly-related records.

[keshalyi]

I know it works! Like I said, it deletes all the records related in the foreign items database! I knwo they're related, cause they show up in a portal.... no other ideas?

[danjacoby]

Silly question:

Are you deleting an Issue or a Page? If you're deleting an Issue, there may be an issue (pun? what pun?) with a second-level relationship.

Not saying this is the answer, but it might help to find the answer.

[keshalyi]

Yes, I'm deleting an issue. What's the issue with second level relationships? I mean, if the issue deletes the page, won't the page delete the gaps?

[IdealData]

If you are relying on FMP to "cascade" delete, ie. by virtue of relationship that allows deletion of related records then this must ne initiated in the "master" file.

I suspect that your second relational delete fails because you have already deleted the original master record by initiating the delete record command in the master file to delete the first relational set.

I also recently tested a recursive delete based on relationships - but that does not work. Seems that FMP will only delete related records from one level down.

[IdealData]

Here's the workaround

Script roughly as

Master file:

GoTo Related records (show only related record)

Perform script (external) [as below]

Related file

Delete All Records

You can then retain the master record for continued deletion sets by related set. Bear in mind you need to move onto each related set in the GoTo Related recs command.

[keshalyi]

Ah... I see... well, that's illuminating. It still bugs me that the one related database deletes its objects and the other doesn't, but I'm sure there's something I'm missing. Thanks for the thorough answer!

[keshalyi]

Well, I tried what you said, it didn't work. So, as an experiment, I went to the pages database, and deleted a record manually. No dice, still didn't delete the records. The gaps display in a portal on the very page I'm deleting from, so I know they're related correctly. The checkbox for cascading deletes is checked in the relationship properties. Its, like I said, virtually identical to the Foreign Items link in every respect. And yet....

Grr!!!

Other ideas?