3 Replies Latest reply on Jun 17, 2011 9:09 AM by philmodjunk

    Problem Creating a Conditional Value List



      Problem Creating a Conditional Value List


      Using FMP 11 Advanced, I am trying to follow the instructions at <http://help.filemaker.com/app/answers/detail/a_id/5833/~/creating-conditional-value-lists> to create a conditional value list in which the user selects a customer last name and then can select the appropriate first name from a list of all those first names for the given last name. The pop-up menu for last name works fine, but the First name pop-up menu says <no values defined>. I have double-checked the steps listed in the support article and not found any discrepancies, but still suspect that I have missed something. Any possible idea about what I missed or did wrong? Not sure which of the numerous dialogs that the issue may be in, but I have enclosed a screenshot of the Specify Fields dialog box. Let me know if there is another dialog box that you may need to see.

      As a further point, I would "like" to set it up so that the user can type (or use the pop-up menu if typing won't work) in the last name field, and if there is only one customer with the given last name, have it go ahead and fill in the appropriate first name without propmpting for the user to select via the pop-up menu. Can this be done, and if so, how?

      Thanks in advance,

      /Tim Allison


        • 1. Re: Problem Creating a Conditional Value List

          What relationship have you defined linking Orders to Customer_Names?

          (Selecting names in this fashion seems a bit chancy anyway as you can easily get two customers with the same first and last names. There are other methods for looking up customers by name that can handle such duplication while still working with unique ID numbers to tell one customer from another.)

          Your last request can be performed with a script triggered when you exit the last name field, but first you have to get that value list working.

          • 2. Re: Problem Creating a Conditional Value List

            Thanks for your response.

            My relationship is between the Orders:Last_Name field to the Customer_Names:Last_Name field. I do also have a Customer_Names:Customer_No field that I could link to (though I don't have it defined as a foreign key in the Orders table at this point.

            I do realize and agree with you that having a customer with the same first and last names can be an issue, however, I simply don't know how to set up the system for doing what you described (especially in the way I am trying to set up the date entry interface. If you could tell me what I need to do to account for that possibility, I would appreciate it.

            Thanks again.

            /Tim Allison

            • 3. Re: Problem Creating a Conditional Value List

              Here's the "simple" approach. There are more sophisticated methods that I've demo'ed in a file that I'll include the download link to at the end of this post, but getting the basic set up working first lays the ground work for more sophisticated approaches if you decide to try them. (The demo file illustrates approaches that make it easier to work with large value lists while still using ID numbers in place of names.

              First, add the Customer_No field to Orders as your foreign key field. This field will let you link customer records to orders and the links will work for you even when you have two customers named "John Smith" or when "Mary Jones" get's married and changes her name to "Mary Adams". The link also still works when you start a new customer record for John Smith and then discover it's really John Schmidt.

              Set up this relationship:

              Customer_Names::Customer_No = Orders::Customer_No

              Define a calculation field in Customer_Names called cFullName that has this expression: LastName & ", " & FirstName.

              Define a value list using the specify field option to list Customer_Names::Customer_No as Field1 and Customer_Names::cFullName as field 2.

              Put Orders::Customer_No on your orders layout and set it up as a pop-up menu or drop down list using this value list. Add Name fields from Customer_Names to your Orders layout and when you select a  customer in the Customer_No field, the customer's name will automatically appear in these name fields.

              Once you get that working, you can make your value list more sophisticated if you add more fields to your full name calculation so that you can tell the difference between two people of the same name. You can include a phone number or address field, to name two possibilities. That way, when you select "John Smith" you can ask your customer "are you the John Smith with phone number 555-5555?" to determine who's number to select.

              More enhanced methods can be set up where you can search by partial names or use an auto-complete enabled drop down list of names--but both require scripting to pull up the ID numbers and to handle duplicate names effectively. Here's a demo file that shows off several such approaches: