Current stock formula for current stock

Shane_Price1
Participant II

Hello

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

0 8 981
  • UX
8 REPLIES 8

Steve
Participant V

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?

Top Labels in this Space