14 Replies Latest reply on Feb 15, 2011 4:23 PM by philmodjunk

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

    philmodjunk

      Title

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

      Post

      The Problem:

       Good database design requires that we relate records with values that

      a) uniquely identify the parent record and
      b) are not entered/edited by the user.

      Thus, linking contact records by a field that contains the name of an individual or company isn’t a good idea. Using a name field as the link sets you up for all kinds of trouble when you discover that names aren’t unique and also when someone changes their name and you find you can’t easily update their name without breaking the link to other records.

      Instead, we link the records via an ID field that in Filemaker is usually an auto-entered serial number. If we need to select a contact by name, you’ll see advice in this forum telling you to set up a 2 column value list where column 1 displays the ID and column 2 displays the contact name. In this manner, you can select the record by name, but enter the ID number into the field and thus establish a link to the related record.

      This creates a problem, however, when your list of names in your drop down becomes long. It’d be really nice if you could use auto-complete with such a value list so that you could type in the first few letters of a person’s name and see only those names listed that start with the same letters you entered. But filemaker doesn’t permit auto-complete on this type of drop down list.

      A Solution:

      One way to make life easier for your users in this situation is to create a special search tool that locates your contact records by name and which does use the auto-complete feature on this drop down.

      Let’s explore this with a simple database with two tables:

      Contacts:

      ContactID (serial number)
      Name (text)
      City (text)

      Invoice:

      ContactID (number)
      gNameSearch (global text field)

      Create several records in contacts with some sample data:

      1. Fred        San Francisco
      2. Fred        San Diego
      3. Fanny     Sacramento
      4. Filmore   Stockton

      In your relationship graph, link the two tables by ContactID.

      Next, define a value list that specifies the name field in contacts as its source of values.

      Set up your layouts:

      On your Contacts layout, re-arrange the fields into a single horizontal row with the field names in the header. Shrink the body until its lower border is as close as possible to the bottom edge of these fields. Specify List View for this layout.

      On your invoice layout, format gNameSearch as a drop down list that uses our value list of contact names. Select the auto-complete option for this drop down.

      While still in layout mode, add the name and city fields from contacts to this layout.

      Write a script:

      Freeze Window
      Go To layout [ "Contacts" (Contacts)]
      Enter Find Mode []
      Set field [Contacts::Name; Invoices::gNameSearch]
      Set error capture [on]
      Perform Find []
      If [get ( foundcount ) = 1]
          Set Variable [$Contact ; Value: Contacts::ContactID]
          Go To layout [invoices]
          Set field [Invoices::ContactID; $Contact]
      Else If [Get ( Foundcount ) = 0 ]
           Show Custom Dialog [“No contacts match the name you entered”]
           Go To layout [ Invoices ]
      End If

      Write a second script:

        Set Variable [$Contact ; Value: Contacts::ContactID]
        Go To layout [invoices]
        Set field [Invoices::ContactID; $Contact]

      Now either put a button next to the gNameSearch field on the Invoices layout or set up an OnObjectExit script trigger to perform the first script.

      Add a button to the Contacts layout. Place it in the body to the left of the fields and set it up to perform the second script.

      Now try out your name search field. You can enter a name by typing in just a few letters. If you enter or select Fanny, You’ll see Fanny and Sacramento appear on your invoice layout. If you enter/select Fred, You’ll see both records with “Fred” in the name field in a list where you can choose between Fred in San Francisco and Fred in San Diego by clicking the button next to their name.

      That’s the basic concept. You can enhance this in a number of different ways. One such enhancement is to use the New Window command to pop up a small window listing any multiple matches.

      Edit notes:

      • Corrected set field step (thanks David!)
      • Reformatted for new forum