2 Replies Latest reply on Dec 14, 2011 3:35 PM by philmodjunk

    Querying for a drop down list

    ShadowRider

      Title

      Querying for a drop down list

      Post

       Hi gang,

      I have managed to get my first issue resolved, please reffer to my previous thread. I would like to thank everyone for all of the helpful solutions however; I'm faced with another issue. The form that I have created for this customer is strictly for entering tank data from the oil field. When I enter a customer's name into the name field; I need the tank numbers to appear in my drop down list for that customer. I have a feeling I am going to have to right a script for this one. Please reffer to the image I have attached to this post. Here is what I need this form to do spacifically:

      1. I have typed in "Lena Simpson" in the customer name field/

      2. I know that Lena has two tanks to collect data from, both tanks are litterally named "01" and "02"

      3. In my drop down list for tank number I need "01" and "02" to appear in the drop down list.

      Here is what I need a script for I think. When I enter a customer's name, I need the tank numbers for that customer to automatically appear in my drop down list for the tank number. Here is another example:

      1. I enter "Joe R. Payne" in the customer name field.

      2. Joe has three tank numbers "38862" "38863" and "43295"

      3. I need the tank number drop down list to appear with these three tank numbers when I enter this customer's name; not "01" "02" from Lena Simpson, as well as the numbers for Joe's tank numbers.

      Thanks for any suggestions

      image.JPG

        • 1. Re: Querying for a drop down list
          ShadowRider

          Sorry about that. Here is the image 

          • 2. Re: Querying for a drop down list
            philmodjunk

            This is a conditional value list and I'll include some tutorial and demo links on the concept at the bottom of this post. But first, you need to analzye some other issues so that you can set up a relationship that works for you.

            Item 1.

            You are selecting a name and then the value list filters to show just the tank numbers associated with that customer. But what if you get two customers named Lena Simpson? What if Lena Simpson changes her name due to marriage or divorce? It's best to link your customer records by a serial ID number instead of their name. You can still use the name to select a customer, but you should set it up so that this selects a unique ID number that is then used in your relationships. Your customer name field can be a two field drop down where the customer ID is in field 1 and the customer's name (or a calculation field that combines a name with additional data to distinguish between names) in field 2. You select the customer by name, but the value list enters their ID number. This type of value list is the simplest to set up. More sophisticated systems that use scripts or a search portal can also be used and which provide better user experiences when you get lots of customers in your customer table.

            Item 2.

            Is it always a one to many relationship from Customer to Tanks or could more than one customer be associated with the same tank? If more than one customer can be associated with the same tank, you have a many to many relationship here and additional work needs to be done with your tables and relationships before you tackle the conditional value list.

            Once you can figure out a solid relationship between a customer record and the tank records with which they are associated, you can set up your conditional value list.

            Forum Tutorial: Custom Value List?

            Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

            Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html