proper relationship link for data update?
2 years later, I am trying to clean up a flaw that my co-workers hate.
We have a jobs database, related to a customer database [JOBS & CUST]. Now runniung on Filemaker pro 11.
When I built this application I related the JOBS table and the CUST table by the cust_name (names are unique). When a job is entered, the cust_name field in the JOBS table is completed using a drop down list. The drop-down is driven by a value list which is populated automatically from CUST:cust_names. This works fine except when one later edits the customer name in customer table (say, fixing a typo). When one edits the customer name field in CUST, the link is busted for jobs that had that customer. The cust_names field in the JOBS table has the pre-edit text, but there is no corresponding customer in CUST.
It seems to me that I want CUST and JOBS related by the cust_number (a serial number and unique) field. I fear I am missing something basic and simple, but here is the question: How does a record in the JOBS table get the cust_number?
what I think I want to happen:
-- during data entry or edit for a job, the value list of cust names shows as a drop down (yes I know I can show the cust_number as a second field, but seeing the number doesn't help)
-- user selects a name and JOBS:cust_name is all good
--now JOBS:cust_number needs to get the corresponding value from the CUST table
in JOBS:cust_number I believe I want to auto-enter a value. A direct lookup does not seem to work (nor would I expect it to since the link requires corresponding cust_numbers and we don't have that yet). So I tried a calulation with the lookup function w/o success. (easily operator erroe here)
I fear I am missing something very, very simple about another approach. Surely this is an everyday problem and the solution is obvious?
thank you in advance- michael