1 Reply Latest reply on Jun 26, 2014 6:11 AM by philmodjunk

    Calculations utilizing data from separate records



      Calculations utilizing data from separate records


           Hi there,

           I am very new to the FileMaker Pro system, and like it already. However, I am still trying to figure out using formulas and functions for the calculation feature, and am not sure how to "phrase" one function that I am going for. To give some background information, I have an "account number" and a "main customer" for each account. So in my contacts, when I designate one contact's "account relationship" as the "main customer" for their account (as opposed to an assistant or other account relationship type), I would like the "main customer" field for all other contacts on that account to be populated with the new "main customer's" name. Hope this makes sense.

           So to go about this, I have created a script that finds all records with that account number, and replaces the "main customer" fields with the new contents. However, in the "replace field contents" script step, I cannot figure out what calculation to enter to have it find the right information. I know I need to tell it to find the record that has (for instance) account number "XYZ123" and relationship "Main Customer," and then use that record's "contact name," but how can I tell it to look at the value of a field on a completely different record? 

           I hope this makes sense, thanks for the help.

        • 1. Re: Calculations utilizing data from separate records

               If your script first sets a $Variable to this value, the "calculation" option can be used with the variable in your script to assign this value to all records in your found set.

               But then you will need to consistently run this script every time you:

          1.           Change this data
          3.           Add new contact records for this account

               A better approach would be to set up the needed relationships so that you never need to run this script in the first place.