4 Replies Latest reply on Sep 17, 2010 10:31 AM by brisance

    Junction table layout / field lookup advice needed

    brisance

      Title

      Junction table layout / field lookup advice needed

      Post

      I have an Assets table with a numeric primary key and Asset Code as fields. Another table is called Customers, also with a numeric primary key and Customer ID as fields. There is a junction table called Assets_Customers which has the following fields:

      Asset primary key, Asset Code, Customer primary key, Customer ID.

      I have set up relationships between all three tables.

      In the Asset_Customers layout, I have drop-down lists for both Asset Code and Customer ID. They are made from Value Lists which are based on the individual Asset and Customer tables.

      How can I replace/edit the Customer primary key field in the Asset_Customers junction table based on the user's selection from the Customer drop-down list?

      I'm unsure if this is the "idiomatic"/conventional way to do things in FMP: use Asset Code fields in each table to establish the relationship. Then in the Asset_Customers junction table set the Asset primary key field to Lookup and copy the Asset table's primary key.

        • 1. Re: Junction table layout / field lookup advice needed
          philmodjunk

          What is different between "Asset Code" and its "numeric primary key"? What is different between "Customer Primary Key" and Customer ID? You seem to have a pair of fields in each table that server the same purpose.

          This is your table relationships, correct?

          Assets---<Assets_Customers>----Customers

          This implies that more than one customer can own the same asset. Is this truly the case? If not, you don't need the Assets_Customers join table.

          The Join table should only have two fields: Asset Primary Key, Customer Primary Key

          To select a customer in a record in Asset_Customer, format the Customer Primary Key field with a 2 column value list. Column 1 must be the Customer Primary Key field from Customers. Column 2 should be a name field or a calculation field that combines name with several other fields so you can see which customer goes with a given primary key. When you select a customer from this list, their primary key will be entered into Asset_Customer.

          The table names are different, but here's a demo file of this concept:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          • 2. Re: Junction table layout / field lookup advice needed
            brisance

            Thanks for responding. The example was very helpful. However, in the portal row I need a drop-down list with auto-complete and not a popup menu, because the list is going to be long i.e. the user should be able to enter an asset code, and the list will allow auto-completion. However, direct text entry means the user has to enter the primary key itself (and not the asset code, which is more meaningful), which is not a reasonable expectation from a user perspective. That is the very problem I'm experiencing.

            • 3. Re: Junction table layout / field lookup advice needed
              philmodjunk

              Yes, that's an annoying limitation of FileMaker value lists. Just when you most need the auto-complete feature, you find it's not available because you need the auto complete for column 2 ( the asset code ) while the drop down enters the column 1 (primary key) data and FileMaker won't do that.

              Here's a workaround that I've come up with. Perhaps you can adapt it to your needs. If asset codes are unique, then you can simplify the process quite a bit as this thread includes a method for dealing with duplicate names.

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

              I'm assuming there are reasons why you don't use asset codes as your Primary Key.

              • 4. Re: Junction table layout / field lookup advice needed
                brisance

                I'll have a look at the example, thanks.

                The Asset code is not being used as the primary key as it could change in future.