AnsweredAssumed Answered

Update the same field in all linked records

Question asked by RichardSmith on Jan 5, 2012
Latest reply on Jan 6, 2012 by RichardSmith

Title

Update the same field in all linked records

Post

Hi,

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.

Outcomes