AnsweredAssumed Answered

proper relationship link for data update?

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

Title

proper relationship link for data update?

Post

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

Outcomes