Issue in calculation of virtual columns

AppSheet AppSheet Q&A
, , , , , , , , , , , , , ,
1

I have two tables, Table 1 is named BOQ for Site and Table 2 name is Indent Used. The Columns in the BOQ for Site are PO Number Ref, Description, QTY and LastEditUser and the columns in the Indent Used are User Email, PO Number, Item, Quantity Present, Item Name and Quantity Using.

Firstly, I will create a virtual column in the Indent Used Table, and I will name it as Quantity at Present, what it does is it will fetch the QTY from BOQ for Site Table, and after that someone will make a entry in the Indent Used Table and will enter a value in Quantity Using Column. So, what the value does is it need to dedut the value from the Quantity at Present for that PO Number and Item, now next time when I make another entry it should be show me the remaining value after deduction for that PO Number and Item, it should show me the QTY of BOQ for Site Table. My only concern is BOQ for Site Table QTY Should not be affected. I tried from my side, but it didn’t work, it is showing me the same value of BOQ for Site – QTY Column value even after few entries.

So, do I need to create any other virtual columns, and what actions need to be included?

Is there any other possibility to create Physical column, but the BOQ for Site Table QTY Should not be affected

2

Can you try [Initial BOQ Quantity] - SELECT(Indent Used[Quantity], [PO Number]=[_THISROW].[PO Number])

The intention is to Subtract the Quantity used for all entries with this PO Number from the initial BOQ Quantity for this PO Number

1 Like
3

So, I need to paste the above formula in Quantity at Present of Indent Using Table. If that is the situation the formula will be as BOQ for Site[QTY] - SELECT(Indent Used[Quantity Using], [PO Number]=[_THISROW].[PO Number]). If I am wrong please help me to correct the formula

4

Yes. Or if you make the column [PO Number] in Indent using Table as Type Ref, then you can use dereference to get BOQ Initial Qty by using [PO Number].[QTY]