2 Replies Latest reply on Jan 6, 2012 1:54 AM by RichardSmith

    Update the same field in all linked records



      Update the same field in all linked records



      I am using FileMaker Pro 11 connecting to a MySQL db using the ActualTech ODBC connector. I have three linked tables: Customers, Orders, and Collection_Data, as follows:

      • Customers: contains details of customers, one record per customer.
      • Orders: contains details of orders, can have 0 or more records per customer, joined to Customers by Affiliation_No field. Each record is an order for a single product, and records the serial number (Serial_No) of the product and the date of the order, amount requested, etc.
      • Collection_Data: contains details of products. Joined to Orders by Serial_No.

      I have made a layout which shows the details of a customer, and in a portal also displays the details of every order associated with that customer along with the details of the product in that order. What I want to do is to update a field for all the order records associated with a customer, so that all the fields update to the same value. I am trying to categorise orders. For example, if I have a customer with ten orders, there is a field called Category which I have set to be selectable from a drop-down list. I would like to make it so that when I select the category for one order, all the other orders for that customer update to have the same category.

      I guess this will require a script. Can anyone guide me on what to use here? I can see that I can use the Script Trigger 'OnObjectModify', and that I can 'Go to Portal Row [Select; Next]' to step through the portal rows. But I can't see how to set all the values to the same thing. Guidance would be much appreciated.

        • 1. Re: Update the same field in all linked records

          First question: If all orders for a given customer will have the same value in category, is category defined in the correct table? If you define category in the Customers table instead of orders, selecting category for a customer will not require such a mass update of related orders records.

          Keeping the current design, a script could work like this:

          Freeze Window
          Go To Related Record [Show only related records; From table: Orders; Using layout: "Orders" (Orders)]
          Replace Field Contents [no dialog; Orders::Category ; Customers::Category]
          Go To Layout [original layout]

          • 2. Re: Update the same field in all linked records

            Thanks very much for the rapid response. It does make sense to define category in the Customers table, but I'm also grateful to know how to do it the other way round as working through it will help me get to grips with scripting.