5 Replies Latest reply on Sep 23, 2013 12:00 PM by philmodjunk

    Drop Down List Problem



      Drop Down List Problem


           Hi Forum,

           I need your advice on how to show the drop-down list based on a condition.

           I have a rate card table, which contains a field category. The category is A customer, B customer and common. common means the rate card is used for both. 

           Now I have Order table which has "order type" field to tell the order comes from A customer or B customer. Whenever I create a new order, I ask the CSR to enter the order type: A customer or B customer.

           In the order line item, I have an e-code which is a drop-down list to show the e-code list for CSR to select. If the order is for A customer, I need the drop down list to show A customer rate cards' e-codes AND "common" rate cards' e-codes;

           if the order if for B customer, I need to see when I click on the e-code, I want to see the drop-down list to show B's rate cards' e-codes AND "common" e-codes.

           BEFORE I had "common" e-codes, I only had "A"customers and "B" customers' rate cards. I have a relationship "ratecards-order " based on rate cards::category = Order::order type. So I define the e-code in the order line item to show a drop-down list which  is from a field, show from "rate card" but only related values starting from Order.

           But after I introduced "common" e-codes, I don't see the "common" values listed when I click on the e-code in order line items.

           How do include those "common" rate cards listed, so the CSR is able to select for entry?


           Thank you very much,








        • 1. Re: Drop Down List Problem

               You can use a match field in your Rate Cards table that is a calculation field that produces a return separated list of values when the rate card is a member of the "common" category.

               Let's assume that your current relationship is based on the text values of "A" or "B" being present in the match fields of your defined relationship.

               Then you can update your relationship from: rate cards::category = Order::order type to become: rate cards::cCategories = Order::order type

               Define cCategories as:

               If ( Category = "Common" ; List ( "A" ; "B" ) ; Category )

               And now rate card data in the "common" category will always appear in your value list whether "A" or "B" is selected in the order type field.

          • 2. Re: Drop Down List Problem

                 Hi Phil,

                 Thank you so much. You just saved me a lot of time. It works great.

                 Although I still don't quite understand:

                 when I did debug and I am in the order layout for an "A" order, I tried to see if below expression will return 1, but it actually returns 0 !

                 List("A";"B") = Ord::Order Type 

                 then I think the "common" item is NOT related to the order, how come it is able to show in the drop-down list?


                 Please point me out.

                 Thank You,




            • 3. Re: Drop Down List Problem

                   How a relationship matches values and how a boolean expression such as List("A";"B") = Ord::Order Type evaluates is not the same.

                   A return separated list of values ( list of values separated by the return character) can be produced with the List function, a check box formatted field or just by typing the values in and entering the returns manually. When used in a relationship, all that need match a value in the related table is any one of the listed values. The "matching logic" is "or" rather than "and". Thus if you have "A" <Return>"B" in the match field (what my calculation produces for "common" rate cards), it will match to records with an operator type of "A" or "B".

              • 4. Re: Drop Down List Problem

                     That solved my puzzle. 

                     So FileMaker is using List function to implement "OR" logic when we need to build relationship based that. From the relationship GUI, I never found a way to do that.


                     Thank You,


                • 5. Re: Drop Down List Problem

                       It's one of two ways. It's quick and easy, but less flexible than the alternative. But it works well for this particular issue.

                       The other way to get "or" matching is with a third "join" table to which both of the other tables are linked in a many to many relationship.

                       In many ways, a return separated list of values is really a kind of "special join table" of just one field.