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

Hi,

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

I made same tables:

 

TableA (Beer)

pk_beer_ID

name

some other description fields

ListOfInStockIDs (to make conditional value list with ExecuteSQL)

 

TableB (Ingredients)

pk_Ingredients_ID

name

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

some other description fields

 

TableC (LineItems)

pk_LineItem_ID

fk_beer_ID

fk_ingrediets_ID

Qty_IN

Qty_OUT

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?

Outcomes