Current stock formula for current stock


I am building an app using the modified Inventory Management sample. There are 3 linked tables (Products, Stock In, and Stock Out). The app calculates the current stock of inventory items based on the initial stock and user input in the Stock Ins/Outs in a virtual column. I would like to have a current stock column on the google sheet it is linked to as well. I do not think there is a way to do it from AppSheet but I’m wondering if there is a formula I could add to the google sheet? Any help would be appreciated. Thank You

You are correct that a virtual column cannot be recorded in the spreadsheet. Consider instead a normal column and use actions to keep it updated.

Thanks for the response Steve, this is where I need help. The linked tables each have the product barcode in common, the “Products” table has the initial stock, the “StockIn” table has stock added, the “StockOut” has stock removed. If someone could help with the formula/actions to auto calculate the Current Stock it would be much appreciated.

I tried a normal column in AppSheet with the same formula as the virtual column but that will not record in the spreadsheet either. Should I do another table in the spreadsheet with a formula there to calculate?

Normal column values are only recomputed when the row is updated, typically through a form or by an action. To keep the value updated, your best bet is to create an action to update the value, then perform that action whenever the value needs an update.

I appreciate the direction Steve. Have you tried what you are suggesting because I cannot make it work. I created an action to copy over the “current stock” column already present in the sheet and another to computer the stock in/out but nothing. Thank you

What exactly did you try?

First Action:

Added new action “for a record of this table” = Products (this is the table I want the current stock listed)

Do this: “Data: set the value of some columns in this row”

Set these columns: Current Inventory (this is the column I created to be updated) = [Initial Stock] (this is a column already in the sheet)

Nothing shows up however

Nothing shows up when? How did you invoke the action?