This problem is pretty complex to explain, but I think I pinned it down.
I have two databases, ADMIN, and DATA. Both hosted on FileMaker Server 16.0.2, FMPA as client.
DATA holds the tables, ADMIN the procedures. Both have table instances that follow the table names.
DATA defines table instances that follow the table names, so if a table is named "QE_Problem_Type", then that instance is named accordingly. Also DATA holds all formal relationships, which include cascading deletions.
ADMIN has a copy of these tables, with the exact same names, with the only difference that these tables do not have any relationship defined on them. There are separate tables which end with "_CASC" which define cascading deletions:
The relationship is defined as follows:
I have TWO layouts on ADMIN, one bound to the "QE_Problem_Type" table, the other bound to "QE_Problem_Type_CASC".
The problem is that if I delete records from whichever layout, the cascading deletions act randomly. Some times they cascade (but while being on the non-cascading table instance), and sometimes they don't.
I believed that FileMaker somehow confuses the name of the table instance in ADMIN with the name in DATA, and sometimes it uses the DATA table instance to delete the records, inheriting its relationship. So I tried deleting the relationship in DATA, while keeping the one in ADMIN.
It still does the same. If I delete the record from the layout bound to the non-cascading table, it still deletes its foreign records.
Tried setting the relationship in the DATA file, and deleting the one in ADMIN... still the same.
Tried disabling the cascading deletion in DATA... it doesn't happen now.
Enabling cascading in the _CASC table in ADMIN... it works, but it still deletes it from the non-cascading table instance.
so what i'm thinking is that if FileMaker finds *any* table instance with a relationship that does cascading, then it will delete related records from that relationship. The documentation doesn't make it very clear.
... and to my actual problem, I need to have some table instances where if I delete records they don't get deleted from other tables, for mass import/export procedures.
Is it a bug? Any idea how this actually works?