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.

1 Like

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”)

1 Like

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

Hello @Arthur_Rallu

Can you give us example of the formula on how to properly get the “Referenced Rows” of the table “Products” in A1?

Thank you!

Hello @WillowMobileSystems

Can you give a formula example on how we can achieve this kind of update method?

Thank you!

Sure! The model you are referencing to manage inventory has 3 tables - an Inventory table where the Quantity on Hand is maintained; an In table where each transaction indicates a quantity to be added to the inventory count; and an Out table where each transaction indicates a quantity being removed from the inventory.

The idea based on the comment you copied is to track a running total. The best way, in my opinion is to add an Action to the Form Saved behavior’s - one for the In table and one for the out table.

Now the tricky part. Because we currently do not have an action to “update values in a row in another table with values from this row”, we have to build a mechanism to simulate this. We need:

  1. A Processed? flag column on each In row and each Out row to indicate it has been added/removed from inventory.
  2. An action to bridge from the IN/Out row to proper product row in the Inventory.
  3. In the Inventory action, An expression to collect the unprocessed In/Out rows, sum them and add/subtract the Sum to/from the Quantity on Hand.
  4. An Action to mark all of the in/Out rows as processed

The expression in the Inventory Action to update to the inventory count would be something like:

[Quantity On Hand] + SUM(SELECT(InTable[Quantity], AND([Product]=[_THISROW].[Product ID], [Processed?] = "FALSE)"))

I being general here since I don’t know your specific structures.

I hope this helps!

2 Likes

Thank you for this @WillowMobileSystems!

So by adding flag column (which is shown in your example as [Processed?]) for both IN and OUT tables, it would make a more convenient expression for the AppSheet server to calculate and update the INVENTORY table because it will only include the “unprocessed” IN/OUT rows in its calculation, rather than using the expression [Quantity On Hand] + SUM([Related IN PRODUCT][Count]) - SUM([Related OUT PRODUCT][Count]) which includes all the rows in the related IN and OUT tables to recalculate and update the INVENTORY table every time a new row is added to the IN and OUT tables.

That is correct! As the table grows so does the time it takes compute the two SUM’s. However, with that formula, if there is an error, the next computation will auto-correct the Quantity on Hand value.

With the method I’ve described, if some increment or decrement fails for any reason, the Quantity on Hand amount will be out of sync from that point forward and requires some manual intervention to correct it. Usually this is just an Adjustment feature.

But I would argue that typically an Adjustment feature is needed anyway. Things happen to inventory all the time outside of the normal sell and purchase. That is why a routine inventory check is required to verify counts are correct and if not adjust them.

1 Like