2 Replies Latest reply on Sep 15, 2016 7:03 PM by burghfan

    Help needed with "Drop-down list" and "Value Lists"

    burghfan

      In our "Customers" Table there is a field labelled "CustomerType" for selecting the the type of "Customer". This field is Drop-down list with the following selections; “Enduser”, “Contractor” and “Engineer”

       

      The Value Lists we created are;

       

      All Contractors - Use Values from Field - Both from the “Customer Table” left side = ID_Customer right side = Customer_Contractor

       

      All Engineers - Use Values from Field - Both from the “Customer Table” left side = ID_Customer right side = Customer_Engineer

       

      All End Users - Use Values from Field - Both from the “Customer Table” left side = ID_Customer right side = Customer_Enduser

       

      I use Calculated Fields as follows

       

      Customer_Contractor = Calculation - If ( CustomerType = "Contractor" ; Customer & " - " & CustomerType )

       

      Customer_Enduser = Calculation - If ( CustomerType = “Enduser" ; Customer & " - " & CustomerType )

       

      Customer_Engineer = Calculation - If ( CustomerType = “Engineer" ; Customer & " - " & CustomerType )

       

      The result is a Value List that separates our customer correctly but displays them as follows in those lists;

       

      Contractors List displays a company as “ABC Construction - Contractor”

       

      Engineer List displays a company as "XYZ Consultants - Engineer”

       

      Enduser List displays a company as  “123 Consumer - Enduser”

       

      The questions I have are.

       

      1. Is there a better way to generate a specific “Customer List” by CustomerType

       

      2. Or is there a way to remove the “ - Contractor” or “ - Engineer” or “ - Enduser” from my Value List?

       

      The result displays properly in the “Customer” field I just would like to remove the extra baggage in the value list if possible.

       

      Help would be appreciated.

        • 1. Re: Help needed with "Drop-down list" and "Value Lists"
          philmodjunk

          What you are dealing with here are called conditional value lists. The method that you are currently using is a form of what I call a "hardwired" conditional value list as each list automatically limits itself to those values meeting a specific condition. There are many other approaches to getting a conditional value list. Which is "better" depends on what you are trying to accomplish in your user interface design.

           

          2. If you don't want the customer type listed in the 2nd value of your value list, simply leave it out of your calculation field's expression and just have it return the customer name.

           

          If ( CustomerType = "Contractor" ; Customer  )

           

          For example, would do what you want for your "Contractor" conditional value list.

           

          The "standard" CVL uses a relationship instead of calculation fields.

           

          Let's say that you have a layout based on a table called "invoices" where you want to select the customer type and then get a value list of all customers of that type. If you set up this relationship:

           

          Invoices::CustomerType = Customers::CustomerType

           

          Then you can define a value list for CustomerType with three values: Contractor, Engineer, EndUser

          And then a single value list can be set with "use values from Field" to list fields from Customers with ID_Customer as field1 and Customer as Field2.

          You then select the "include only related values starting from Invoices" option to make this a conditional value list.

           

          The user selects a customer type from Invoices::CustomerType and then your value list lists only customers of that type.

           

          For more about conditional value lists, see:

           

          Adventures In FileMaking #1 - conditional value lists

           

          This file contains working examples of multiple different approaches with detailed information on how each is set up and how it works. It also has a link to Adventures In FileMaking #2 - Enhanced Value Selection which presents other approaches for selecting from a list of values that go beyond a simple value list.

          • 2. Re: Help needed with "Drop-down list" and "Value Lists"
            burghfan

            Thank you I'll give that a try.