3 Replies Latest reply on Sep 22, 2010 10:55 AM by philmodjunk

    proper relationship link for data update?

    MichaelM

      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

        • 1. Re: proper relationship link for data update?
          philmodjunk

          Make the Customer number the first field in your drop down's value list and the name the second value. Then you are entering the customer number instead of the customer name--which should only reside in the customer table.

          I think I can anticipate a complaint from you about this setup--auto-complete on the customer name no longer works and this makes selecting a customer a lot more time consuming as you scroll through a long list of customer names.

          If that's the case, you might consider this approach so that you can have your auto-complete drop down, but link by ID numbers too:

          Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

          • 2. Re: proper relationship link for data update?
            MichaelM

            Thank You--

            Ahhhh-   first field in the value list.  I knew that.

            Your more complex posted solution is the right one, albeit not simple to implement in an existing application with plenty of different layouts.  I totally agree that good practice does not allow links through user alterable fields.  That said, with all the functions in FMP this seems like a place where developers could use a neater solution.   I was so wishing I had missed a super clean and neat solution.

            THANK YOU-  michael

            • 3. Re: proper relationship link for data update?
              philmodjunk

              It shouldn't be too terribly difficult to adapt the method to use it with multiple layouts. If you enforce unique names, you can remove a big part of the system that's intended to handle duplicate names and you can pass intended layout destinations as parameters so you can use one set of scripts for all situations where you need this, even if you have a large number of different layouts involved.