Mirroring Virtual Column in Spreadsheet Column

I am looking to mirror the data of a virtual column into a spreadsheet column. I have reached out to several members here who have been helpful but I still cannot get it to work. The app is a simple inventory app with 3 tables in the database (Products, Stock In, Stock Out). There is a virtual column (Current Stock) in the Products table that calculates current stock based on initial stock and 2 forms (Stock In, Stock Out) where the user inputs the barcode and a quantity. The Products table’s columns are Product, color, length, barcode, etc. The Current Stock calculated by the virtual column shows up in the app but I’d like to have a column on the Products table with the same data. Things I’ve tried:

  1. adding a column to the spreadsheet (called In stock now), regenerating structure, under Formula for that column adding = [Current Stock]

  2. creating an action

Action name: set current stock
For a record of this table: Products
Do this: data: set the values of some columns in this row
Set these columns: In stock now = [Current Stock]

the problem with this approach is the action can only be added to the Products view under Behavior/Event Actions/Row Selected. It works in this case, it fires off and copies the data when a row is selected but the intent is to have it fire off after the user hits “Save” when they Add or Remove stock through the Stock In or Stock Out views. However, the “Set Current Stock” action is not visible under Behavior in those views as the data is on a different table (the Products table). Any suggestions would be appreciated, thank you.

I would recommend REPLACING the Virtual Column with the spreadsheet column.


Does this mean that you are using the history of In/Out activity to re-calculate the Current Stock value each time?


On your Action issue, maybe you could have 2 actions?

  1. “Increase Current Stock” which is a “Stock In” action
  2. “Decrease Current Stock” which is a “Stock Out” action

Each of these could call “Set Current Stock” or any other actions needed.

1 Like

Hi Shane,
to do that, you can use a combination of:

  1. a workflow triggered whenever the Stock In table is updated (another workflow for the Stock out table) that calls an action (let’s call it A1)
  2. an action A1 that applies to a record of the table “Stock In” and that “executes an action on a set of rows” which will list the rows and the table (“Products”) to update
  3. an action A2 that applies to a record of the table “Products” and that “set the values of some columns in this row” with your formula

You’ll have to write a formula to properly get the “Referenced Rows” of table “Products” in A1. It will be a SELECT statement that returns a list of the IDs of the records to modify.

I tried this with the same formula the virtual column had and the column remains blank in the spreadsheet. It’s like the virtual column was auto calculated but the spreadsheet column is not.

Yes. Here is how it works. The virtual column (Current Stock) has the following formula:

SUM([Related StockIN][Quantity]) - SUM([Related StockOUT][Quantity]) + [Initial Stock]

There are 2 other virtual columns with formulas:

Related StockIN
REF_ROWS(“StockIN”, “Product Barcode”)

Related StockOUT
REF_ROWS(“StockOUT”, “Product Barcode”)

Yes, a “normal” column’s App Formula is only re-calced when the row is EDITED. A Virtual Column is re-calced on each Sync. I was kinda waiting on the response about how you are calculating.

I would move this calculation to the two Actions that were suggested and remove it from the App Formula. And as was suggested, you could call your single “Set Current Stock” from each of these other actions.

A few additional things to consider (if you haven’t):

  1. How are you handling inventory audits? Stock has a way of “mysteriously” disappearing and the only way to compensate is through a physical count of the actual stock.

  2. The calculation you have could get very slow over time (think years down the road) because the lists of In/Out records can get very long. To prevent this you would need to perform some maintenance to “trim” the number of records. Or you could switch to an update method where each In update increases the count and each Out record decreases the count.

  3. If you have the possibility that multiple people could be adding In/Out transactions, you need to be sure the design doesn’t allow the users to step on each other and muck up the actual count.

1 Like

I am creating a Virtual Column to generate a BarCode [QRCode] but what to copy this to my datasource (Smartsheet) [QR Code] (with space) so that it can be printed on a PO.

When I reference the Virtual column with the Smartsheet reference column =[QRCode] the field is blank.

Can yo advise how to retrieve this Virtual Column information

Many thanks
Dave

Virtual Columns are only available inside of your AppSheet app.

Additionally, because Virtual Columns are re-computed on Sync, the formula you use to generate the Barcode would be re-called on every Sync.

That’s probably not what you want!

I would recommend creating a normal sheet column for the Barcode. Barcodes are something you probably want to store in the data anyway.

1 Like

HI John
Many thanks for your response. The idea is that I would retrieve the Barcode from the URL but then I want to copy this image to another image field in the same ROW AND TABLE. The problem is I do not know how to do this.

The idea is that this record will be used to print a delivery note sent to a supplier, and then when the items are delivered they are scanned to record within in the same record that the goods have been received on a respective date.

I would have thought this was a common requirements but cannot find an example of this solution so would be grateful if you could advise

Regards
Dave