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
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?
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.
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
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?
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.