3 Replies Latest reply on Jun 17, 2017 12:20 PM by slabin

    Execute SQL i CVL does not work?

    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?

        • 1. Re: Execute SQL i CVL does not work?
          bigtom

          After Refresh?

          • 2. Re: Execute SQL i CVL does not work?
            martinsorich

            InStock is an unstored calculation. Is there a way you can update the value via a script tied to a script trigger? This way the field can be indexed and perhaps used for a value list.

             

            But InStock only measures quantity. What you need is a binary field indicating whether the stock of an item is 0 or > 0. If the stock is 0, then the binary field value is 0, if it is > 0, then it is 1. You can use a constant field ( = 1 ) from Table A to create a relationship to Table B to the new binary field. You can then create a value list to show all the items in stock.

            • 3. Re: Execute SQL i CVL does not work?
              slabin

              Itried a field with values 1 nad 0. It is calculated nad can't be indexed. Nad we are at begining.

              I used another field in TableA calculated with ExecuteSQL command. It can be indexed and serve as source of value list. But this list refreshes at only few steps (after opening DB and adding new record in TableA or TableB).