5 Replies Latest reply on Mar 27, 2014 8:42 AM by philmodjunk

    Optional conditional value lists



      Optional conditional value lists


           I have a portal of line items in an Estimate layout.

           In the portal I have a few pop-up menus, that get their values from conditional value lists,

      •           Supplier
      •           Category
      •           Sub category
      •           and item

           I would like to introduce, another pop-up menu, Gauge, that isn't necessarily required;
           if left blank, the Item list will show all the items, regardless of gauge,
           but if filled with a number it only shows the items that equal that gauge.

           At the moment, if I put in a gauge, it works as expected and those items are shown,
           but, if I leave it blank, nothing comes up - I'd like for all the items to be displayed.

           Any ideas?


        • 1. Re: Optional conditional value lists

               Set up a calculation field to work as the match field to the table of values. Use ExecuteSQL() to generate the needed return separated list of IDs to match to the subset of records that you want for your value list. This calculation can include an If or case function if needed to use different calculations using different criteria (as determined by whether or not the gauge field is empty) to produce this list.

               Here's a link on using ExecuteSQL to control a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

          • 2. Re: Optional conditional value lists

                 Thanks for the information.
                 I've been trying to get my head around SQL as I've not had to use it much so far in my solution.

                 I've used SeedCode's SQL Explorer and it's been very helpful, however...

                 Say item 1 in the portal I have selected 1.2mm gauge, the drop down list works as expected, but, say I add a new line
                 item 2 to the portal and I select items that have 2.0 mm gauge, the drop down list now displays both 1.2 AND 2.0mm gauge items.

                 How can I limit the query to just look at the gauge only in its row?

            • 3. Re: Optional conditional value lists

                   Set up the conditional value list with the match field used and the "Starting from" table occurrence specified for the portal's table occurrence instead of the layout's. And link the related table of values to the portal's TO, not the Layout's.

              • 4. Re: Optional conditional value lists

                     The value list is already set up to look at the portals (LineItems) table.

                     This is what I have so far...



                     Esimates::_kp_EstID = LineItems::_kf_EstID
                     LineItems::c_SQL = ProductsSQL::_kp_ProdID


                SQL Query
                     SELECT a."_kp_ProdID"
                     FROM "Products" a
                     INNER JOIN "LineItems" b ON a."_kf_SupplierID" = b."_kf_Supplier" AND a."Category" = b."Cat" AND a."SubCategory" = b."SubCat" AND a."Gauge" = b."Gauge"
                     WHERE a."EstimateItem" = Yes
                     ORDER BY a."Description" ASC


                     Any other suggestions?

                • 5. Re: Optional conditional value lists

                       I must assume that you left out the \ characters for clarity.

                       You can omit the Order By clause as it has no effect on what values appear in the value list.

                       For your value list, did you select Estimates or LineItems as the "starting from" setting? It should be LineItems.

                       And are you listing values from ProductsSQL or a different occurrence of Products? It should be ProductsSQL.