AnsweredAssumed Answered

proper relationship link for data update?

Question asked by MichaelM on Sep 22, 2010
Latest reply on Sep 22, 2010 by philmodjunk


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

solution A:

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)

Solution B:

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