4 Replies Latest reply on Aug 18, 2017 11:42 AM by user29748

    Relationships act randomly when cascading deletions are enabled

    user29748

      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.

      Screen Shot 2017-08-11 at 17.10.17.png

       

      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:

      Screen Shot 2017-08-11 at 17.08.59.png

       

      The relationship is defined as follows:

      Screen Shot 2017-08-11 at 17.09.06.png

       

      I have TWO layouts on ADMIN, one bound to the "QE_Problem_Type" table, the other bound to "QE_Problem_Type_CASC".

      Screen Shot 2017-08-11 at 17.09.56.png

      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?

        • 1. Re: Relationships act randomly when cascading deletions are enabled
          Benjamin Fehr

          I would like to know your specs:

          Product and version: 

          OS and version:

          Hardware:

          Description:

          How to replicate:

          Workaround: 

          Notes:

           

          Your data model for me looks like kind of a data-separation model.

          I had a similar occurrence where arguments in a relationship with a foreign file scrambled.

          Kind of:

           

          Table_A::Name     =      Table_B::Name

          Table_A::Street     =     Table_B::City

          Table_A::City          =     Table_B::Street

          • 2. Re: Relationships act randomly when cascading deletions are enabled
            user29748

            Product and version:  16.0.2 Server, as indicated

            OS and version: Windows Server 2012 R2 Server, OSX Client

            Hardware: VM, Mac

            Description: Told

            How to replicate: Told

            Workaround:  None

            Notes: Damn

             

            Yes, it's a data separation model. I use it to handle product updates better and to avoid filemaker's useless import/export system. There are other files that work with the DATA file.

             

            The relationship is very simple:

            Screen Shot 2017-08-11 at 17.43.27.png

            1 of 1 people found this helpful
            • 3. Re: Relationships act randomly when cascading deletions are enabled
              TSGal

              user29748:

               

              Thank you for your posts.

               

              From what I see in your diagram, "QE_Problem_Type_CASC" is related to "QE_Problem_Description 2" via ~id and type~id fields.  If a record is deleted from the "QE_Problem_Type_CASC" table, all related records in the "QE_Problem_Description 2" table are also deleted where the type~id field matches the deleted ~id field.

               

              It's not clear from your description, but it seems when you delete a record from "QE_Problem_Type_CASC", the related records from "QE_Problem_Description 2" are not deleted, but from another table?  Can you be a little more specific?

               

              TSGal

              FileMaker, Inc.

              • 4. Re: Relationships act randomly when cascading deletions are enabled
                user29748

                I'll try to simplify it. I've made two tables, one with cascading (_CASC) and the other without it. If I delete a record from either master table, it gets deleted in both cases, even if in the non-cascading table it shouldn't happen.