Calculations utilizing data from separate records
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.