3 Replies Latest reply on Oct 11, 2011 9:05 PM by DavidJondreau

    Drop down lookups [like Access?]



      Drop down lookups [like Access?]


      I’m new FM user, switching from Access.

      Am testing lookups with two tables:

      tblCities has 3 fields: CityID [number], City [text], StateID [number].

      tblStates has 2 fields: StateID [number], State [text].

      Exported these from Access into Excel, then imported them into FM Pro 11 Advanced.

      A relationship exists between StateID in the two tables.

      The objective seems simple…

      I merely would like to have a drop-down in tblCities:StateID that looks up to tblStates:StateID.

      What I would like to *see* in tblCities:StateID is the actual text [ie AL for Alabama, etc], but the number itself is what would actually be in the record.

      This is easily accomplished in Access [indeed standard practice], but I have managed to implement some rather bizarre and frustrating events by attempting it in FM.

      Please can anyone provide the necessary steps to make this happen.

      ** Perhaps I’m approaching it incorrectly, and FM will not work exactly like Access …??

      Thank you,



      PS -- Glad to forward the file if you like.


        • 1. Re: Drop down lookups [like Access?]

          This can be set up in Value list setup. Filemaker doesn't provide all the options you'd see in an MS Access combo box but this one can be done.

          Define a value list with the specify field option so that tblStates::StateID is listed in field 1 and tblStates::State is listed in field 2. "Specify show only values from field 2."

          If you use this value list with the pop up menu option, you have what you describe.

          This can also be approximated with a drop down list, but it takes a bit of creativity to manage it so that the number stays hidden and the state text is visible after you exit the field.

          • 2. Re: Drop down lookups [like Access?]

             Thanks Phil.

            That worked.

            Going a bit crazy trying to duplicate the combo box ...!!

            Am presently uncertain of the difference between a drop down list and a pop up menu.

            Much appreciated if you'd elaborate, or point me to a clarifying link.

            • 3. Re: Drop down lookups [like Access?]

              Well, you can only do an Access-style combo box with a pop-up menu. It won't work with a drop down list.

              A pop-up menu only allows entry of the values in the value list (unless you've checked the "Allow editing.." or "Allow other..." options). With a drop down list, you can type your own values directly into the field.

              *With a pop-up menu (as with a checkbox or radio button) users can still *paste* values into the field control. A major flaw in my opinion.