6 Replies Latest reply on Jan 17, 2013 1:08 AM by tomasd

    Delete related record ignore relation parameters

    tomasd

      Summary

      Delete related record ignore relation parameters

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      Windows XP

      Description of the issue

      I have 3 related tables:
      Product <> Prices <> Pricelist

      1) Product <> Prices have 2 relationships, and on the Prices side is set option "Allow delete", when related record in  Product is deleted. Relations are: ID = ID Product; TagActive = Tag  

      2) Prices <> Pricelist have one relationships and Allow delete option on both sides: ID Pricelist = ID

      Problem is that Filemaker deletes all records in table Prices with TagActive and ignores ID Product relation.

      Steps to reproduce the problem

      Delete record in Product table.

      Expected result

      It should delete only records with specified ID Product and Tag in table Prices.

      Actual result

      Filemaker deletes all records in Prices with TagActive and ignores ID Product relation.

      Workaround

      Uncheck allow delete option in relationship editor.

        • 1. Re: Delete related record ignore relation parameters
          philmodjunk

               Can you upload a screen shot of Manage Database Relationships showing these relationships?

          • 2. Re: Delete related record ignore relation parameters
            tomasd

                 Relationship diagram (Pricelist Used="Prices" and Products Stock="Product" from my first comment)

            • 3. Re: Delete related record ignore relation parameters
              tomasd
              /files/42322b0a26/Manage_Database_for_%E2%80%9CProducts%E2%80%9D_000020.png 559x578
              • 4. Re: Delete related record ignore relation parameters
                tomasd

                     PricelistTagActive is global field

                • 5. Re: Delete related record ignore relation parameters
                  philmodjunk

                       Cascading deletes can be very useful, but they are also tricky at times. The devil is always in the details. Let me sum up what you just posted...

                       Products Stock-------Pricelist Used-------PriceList

                       Products Stock::ID = Pricelist Used::ID Product AND
                       Products Stock::gPricelistTagActive = Pricelist Used::PricelistTag

                  Pricelist Used::ID Pricelist = Pricelist::ID AND
                  Pricelist Used::Pricelist Tag = Pricelist::Tag

                       Your original posts says to reproduce this, you need to delete a record in Product Table. I must assume that is deleting a record in Products Stock.

                       That should delete all matching records in Pricelist used--any record with matching values in ID Product and Pricelist tag. This then trips a cascading delete, deleting any record in Pricelist with a matching ID and Tag.

                       First question: Do either Pricelist::Tag or Pricelist Used::PricelistTag have global storage specified?

                       Are there any other occurrences with the same data source table as Pricelist Used in your relationships graph? I'd check relationships for any such to see if another delete option is specified there that is responsible for the delete that you are seeing.

                  • 6. Re: Delete related record ignore relation parameters
                    tomasd

                         I agree that cascading deletes might be tricky and I am sorry, that i have posted wrong table names. 

                         Pricelist::Tag and Pricelist Used::PricelistTag are standard indexed fields. There aren' t any other occurences with delete option. 

                    If I uncheck second delete option in relationship Pricelist Used = Pricelist (on the Pricelist side), the delete in products stock is ok. It isn't necessary to have both options checked in our solution.