AnsweredAssumed Answered

Execute SQL i CVL does not work?

Question asked by slabin on Jun 17, 2017
Latest reply on Jun 17, 2017 by slabin


I try to make a solution for home brewery to track may recipes, ingredients, beer and so on.

I made same tables:


TableA (Beer)



some other description fields

ListOfInStockIDs (to make conditional value list with ExecuteSQL)


TableB (Ingredients)



InStock / calculation = Sum(TableC::Qty_IN)-Sum(TableC::Qty_OUT) /

some other description fields


TableC (LineItems)






some other description fields


and relationships „one-to-many” on both sides.


I have two layouts.

First for buying ingredients based on TableB with portal on related TableC. Works fine.

Second for track buys and consumption of ingredients based on TableA with portal on TableC. There is a drop-down field in portal to select Ingredients from value list based on TableB. Its OK, BUT….

I have hundreds of ingredients that are used completely (InStock=0).


How to remove them from value list? I tried methods from „1 CVLs”.

Made a field „ListOfInStockIDs” in TableA, second TO of TableB and related them. Relation is: TableA::ListOfInStockIDs = TableB2TO::pk_Ingrediens_ID. Made a popup field in portal with value list from TableB2TO beginning with TableA.

New field works as expected (refreshes after each operation and has correct list of IDs), but based on it VL does not. The VL refreshes on opening DB and after adding (not editing) new record in TableB (in another layout).

What can be wrong?