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.