8 Replies Latest reply on Aug 19, 2016 5:32 PM by philmodjunk

    Auto-Complete Lookup


      Hello FM Community,


      I have been programming in MS Access for years, but I am new to FM.  My apologies, if i get FM terminology confused...


      Q:  i have an 'Equipment' layout with an 'Owner' popup list.  The list provides the owner names to Users, but stores the OwnerID in Equipment table.  I want to update the functionality to 'auto-complete' for the end users.  the rules indicate that i have to convert popup to either an editbox or drop-down list.  i also have to change the type to text.  When i do this, the value that i am trying to store is shown in my list not the name that i want to search.


      Is it possible to show the Owner names in the text control while using auto-complete, and store the ID?  do i need to add another control box?


      Thanks in advance,


        • 2. Re: Auto-Complete Lookup

          Yes. You do this by creating a Value List of owners. The principal field in the list is ownerID, which is the value that gets posted into the field using the list, but you set the list to show the owner name as a second value, and set it to show only that value. That way the user sees the name, but enters the ID.

          • 3. Re: Auto-Complete Lookup

            You might consider inverting this process in FileMaker.


            You use an auto-complete value list of just the names, but then use a relationship to look up the corresponding ID. This enables you to have more than one record with the same name but different ID's as you can use a script to handle cases where the selected name matches to more than one record. The ID lookup then enables you to link records by ID as you should.


            Adventures in FileMaking #2-enhanced value selection


            Has a working example of that and many other ways that you can present the user with a way to pick values from a list.

            • 4. Re: Auto-Complete Lookup

              Thanks philmodjunk,

              this is great stuff!  i am digging into it, and hopefully make this work for my application...



              • 5. Re: Auto-Complete Lookup

                This is the way that i originally had it.  When I set it up like this, the 'Auto-Complete' becomes disabled??.  i am guessing it is because my field source is the fk_ID field?




                • 6. Re: Auto-Complete Lookup

                  That's why I "invert" the process. I use a text field that is formatted with a "use values from field" value list that does not specify the ID field, only the name field. Since this is text being entered into a field of type text, auto-complete works.


                  This uses a relationship, gasp, that matches records by name field rather than ID, but this relationship is only used to support this auto-enter task. With that relationship, I can set up a looked up value setting to copy the ID of the matching record into an ID of my layout's table. I then use the standard relationship matching on ID fields in all other parts of my solution. Note that this then allows me to provide scripted support should the name entered not match any record (Offer to create a new record?) or matches more than one record (Provide list of matching records with supplemental info (address, phone....) so the user can select the specific record that they want.)

                  • 7. Re: Auto-Complete Lookup

                    Hello philmodjunk,


                    Thanks so much!  I Think am starting to see how this works.


                    One more question:

                    referring to this:  "This uses a relationship, gasp, that matches records by name field rather than ID".

                    Does this require that I have to add a Child 'Name' field to the Parent table, as well?  Where before i was just storing the ID_fk.


                    Example:  originally i was only storing the pk_OwnerID from tblOwner in tblEquipment as a fk.  Do I need to add an OwnerName column into tblEquipment for the relationship?


                    Does that make sense, or am I totally missing the boat?




                    • 8. Re: Auto-Complete Lookup

                      Yes, and that's the main draw back (a small one IMO) to this approach. You set up this added text field in your table and use it only for this purpose. It can require using an OnRecordLoad script trigger to copy values from the related table (By ID based relationship) in order to make sure that it still shows the correct text even if edits from other locations have changed the text associated with that ID number in the related table. Now that I think of it, this can even be a global field--which is as close as you can get in FileMaker to an unbound text box like you can use in the forms of other applications.


                      The Adventures in FileMaking file that I recommended earlier has a working and documented example of this set up.