4 Replies Latest reply on Sep 5, 2012 11:00 PM by alanfair

    Relationships, Lookups, and Storing the record ID From a lookup



      Relationships, Lookups, and Storing the record ID From a lookup


      I am new to filemaker, and perhaps i am missing something, but cannot get this to work.  Something i do in access without any issue.

      Keeping the example simple.  Two tables, a) Customers Details, b) invoice details.

      The customer table,  - each record has a unique number in the field C_ID , and the name of the customer in the Field C_NAME

      Invoice table - Key fields are I_ID - unique record no, I_Details = some invoice text,  I_C_ID = number of customer record to which ir relates.

      Have defined a relationship between C_ID and I_C_ID


      When entering an invoice on my Invoice layout screen , I want a drop down list of customer names, to allow selection of the customer, but i want the customers ID (C_ID) stored in the invoice record in the field I_C_ID. - Cant seem to do this.

      Can store the name but i need to store the link.

      Can any of you Pro's Help, Thanks

        • 1. Re: Relationships, Lookups, and Storing the record ID From a lookup

          Open Manage | Value lists

          Start a new value list and select the "Use values from a field" option.

          Select Customers Details in the drop down and click C_ID to select the ID field as the primary field. This field's value will be entered when you select a value in this field.

          Click the "Also display values from second field" checkbox

          Select C_Name as the secondary field.

          At this point you have a value list that displays both the ID and name values, but enters the ID number when a value is selected.

          You can select the the values be sorted by Name and/or that only the names are shown, but if you use either or both options, make sure that customer names are always unique as duplicate names will then be omitted.

          This is the "beginner" level value list to do what you asked. More sophisticate approaches are also possible.

          • 2. Re: Relationships, Lookups, and Storing the record ID From a lookup

            Thanks for that, but it does not quite do what i am trying to do. I have the value list ok,

            on my invoice layout if i attach this value list  to the I_C_ID field, I get a drop down list, as you say, and can make this just a list of customers if i wish. Once a selection is made the customer id is entered in the I_C_ID field.

            However there are two issues.

            1. There is no form of auto complete durng the data entry, and the option seems to be greyed out, I want the person to start typing the customer name, and it auto-complete from the list as they type, or of course they can scroll down the list to select the customer name they are after. I guess the problem may be the  field I_C_ID is expecting a number.

            2. I dont really want the customer number showing.  I am after a drop down box, on the invoice layout, that contains the list of customers, that auto completes, as the user types, or allows them to scroll down the list and select. Once a selection is made the customer name is displayed in this field, not their ID, but in my invoice table the customer id is stored, (C_ID) in the I_C_ID field.

            Hope that makes sense.

            • 3. Re: Relationships, Lookups, and Storing the record ID From a lookup

              1) this is a basic limitation of FileMaker value lists. One that is very irritating, but it is possible to devise work arounds that involve some scripting so that you can get an auto-complete enabled value list to work for this. Remember that I said that my first suggestion was a "beginner level" solution. Wink

              2) If you select pop up menu and set up the value list to only display names, the numbers will not be shown. If you want to use the drop down list, you can place the name field from the related table on top of the drop down list and use field behavior settings to prevent accss to this name field. When you click on this name field, control is passed to the hidden drop down list behind it and it pops to the front and deploys a list of values. When you exit this value list and field, it pops back behind the name field.

              Here are some more sophisticated approaches that enable auto-complete. They also handle duplicate names so that you no longer have to require that the customer names be unique.

              A simplified method I recently worked up:


              A more sophisticated demo file with several "search portals" also included. If you are using FMP 12, it will convert to FMP 12 format and the converted copy should work just fine. (Open it from the file menu to convert it.)


              • 4. Re: Relationships, Lookups, and Storing the record ID From a lookup

                Many thanks, for he time an effort.