AnsweredAssumed Answered

How to properly trim records in a many-to-one relationship?

Question asked by StevieP on Nov 23, 2017
Latest reply on Nov 27, 2017 by philmodjunk

The situation is as follows:

I have a table which is pretty much the core of the entire database. Originally I just added more and more fields until the database started to groan and slow down (mainly due to the amount of records having grown over 30.000). So, to speed up the database, I've started to split up the main table into several sub tables.


Because of this, I've ran into a bit of a problem, however. While nearly all relationships are one-to-many, I have one which is many-to-one. Basically, the table I work in, delete and copy records in, etc. is the one which is the "many" side. Several of these records tie to a single record in the other table. This works fine except when it comes to deleting records. Filemaker only seems to have the option to delete the record in the sub-table whenever I delete a single record in the main table and I do not want this for obvious reasons.


Basically, I ONLY want Filemaker to delete the record in the sub table when ALL records in the main table that are linked to this record are deleted. Right now, Filemaker just deletes it when even a single record is removed which is VERY dangerous.


Since we add so many new records a day, I do not want to have to manually clean the sub table records to look which are "orphaned" since it's a tedious process. Of course, I could schedule a daily script to do some automatic housecleaning, but I wonder if there's no simpler solution?