5 Replies Latest reply on Jun 12, 2013 11:01 AM by pbedouk

    How to delete join table records only?

    pbedouk

      Title

      How to delete join table records only?

      Post

           Hi

           I have this relationship set up:  distributors ---< join table >----- products

           The join table has data fields related to the unique distributor-product pair ( such as product cost, which varies from distributor to distributor).

           I need to be able to remove a product from the distributor without deleting the product record itself, only the connection between them, ie the join table record (I think).  It says 'we no longer buy this product from this distributor', and implies that we do buy the same product from other distributors.

           I have a layout based on the distributors table which has a portal to the products table (not the join table).   I'd like to remove the join table record via a button on the portal row.  Is this possible?  How?

           If not, how would I do it another way?

           Thanks

           Peter.

        • 1. Re: How to delete join table records only?
          symbister

               Firstly, in the portal, you 'allow deletion of records' then insert a button in the portal to run this script:

               Set Variable ($productID ; yourproductstable::productID)
               Set Variable ($distributorID ; yourdistributortable::distributorID)
               Go To Layout ["Join Table" (YourJoinTable)]
               Enter Find Mode
               Set Field [YourJoinTable::ProductID ; $productID]
               Set Field [YourJoinTable::DistributorID ; $distributorID]
               Perform Find
               Delete All Records // make sure 'Perform Without Dialog' is unchecked when you're testing :)

               You may also want to add logic to test if there's more than one join_table record for that combination of Product and Distributor

          • 2. Re: How to delete join table records only?
            symbister

                 update..just realised that of course you're not deleting records of the portal, so that first bit about allowing their deletion is spurious

                  

            • 3. Re: How to delete join table records only?
              pbedouk

                   Thank you.

                   The Filemaker Fog has lifted a bit more.  I was mentally missing the bit where you can enter values into a 'field' in find mode without actually changing a record.

                   I can see that I may end up with orphaned product records if all the records of the join_table are deleted for a particular product, ie no distributor supplies a particular product.  Any thoughts on how to detect this situation?

                   Thanks again!

              • 4. Re: How to delete join table records only?
                philmodjunk

                     If you use a script to delete the join table record, the script can first go to the related products record and count the number of related join table records. If the count is 1--the record you are about to delete, then the product record will be an orphan and the script can either delete that record or ask the user if it should be deleted.

                • 5. Re: How to delete join table records only?
                  pbedouk

                       Brilliant.

                       Thanks!!