Updating balance

[naresh_chauhan1]

Hello everyone ! I am working on drug inventory management app. here is my tables
1 drugs list

2. store where balance is maintained

3. prescription where drugs quantity issued to pt

guys I can’t figure out how to update the balance in store table using values from the prescription tables for individual drugs. so that timely stocks can be updated.
pls help, also suggest if there is other way around !
thank you !

[Aurelien]

Hi @naresh_chauhan1
“Current Stock” should be calculated.
You may want to remove your “balance” column from your sheets, regenerate structure, then add a virtual column “balance_calculated”

[Initial_Quantity]
+SUM([Related Prescriptions][Quantity])

[naresh_chauhan1]

this worked for me exactly the way I wanted thank you so much
in store table I used Related Prescription =REF_ROWS(“prescription”, “Drug”)
and current stock = [quantity]-SUM([Related Prescriptions][Quantity]) both are virtual column
thanks a lot!

[Darmund]

Wouldn’t the [Quantity] have to be -5 in this case? I agree that Current Stock should be calculated. But I wonder what the best way would be to increase the Current Stock and decrease the Current Stock.