Mirroring Virtual Column in Spreadsheet Column

[Shane_Price1]

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.

[WillowMobileSys]

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

---

Shane_Price:

calculates current stock based on initial stock and 2 forms (Stock In, Stock Out)

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.

[Shane_Price1]

WillowMobileSystems:

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

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.

WillowMobileSystems:

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

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

[WillowMobileSys]

Shane_Price:

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, 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.

[daw7]

WillowMobileSystems:

update method where each In update increases the count and each Out record decreases the count.

Hello @WillowMobileSystems

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

Thank you!

[WillowMobileSys]

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!

[daw7]

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.

[WillowMobileSys]

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.

[Galo_Barriga]

WillowMobileSystems:

• A Processed? flag column on each In row and each Out row to indicate it has been added/removed from inventory.
• An action to bridge from the IN/Out row to proper product row in the Inventory.
• 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.
• An Action to mark all of the in/Out rows as processed

Hello there,
I am completely new to this tool so if my question is too obvious, please receive my apologies in advance. I have two issues and hope I can explain them clearly.

1. I was following your advice but do not understood the meaning of creating an action to bridge from the IN/Out row to proper product row in the Inventory.

Since my ‘Current inventory’ column is in the “third table” [PRODUCT], based on my basic understanding any edition/calculation to that Inventory has to be done on that table.

So I ended up with only one action linked to that table [PRODUCT] instead of two actions (1 from IN table and another from OUT Table) as you suggested.

2. BUT I have to manually run the Action after I save transactions either they are IN or OUT of inventory. The ‘Current inventory’ is being calculated properly which means the formulas are OK.
   I have read that, by using a workflow it is possible to run actions automatically. However when I was creating the workflow since, as I already said, the Current Inventory is placed in a different table [PRODUCT] I could not make the workflow to run. Why? Because when I enter transactions IN/OUT there is no change in the PRODUCT table so the workflow does not trigger. I can not create the workflow linked to IN Table because the Action is attached to the [PRODUCT] table…
   How do I set the action to run automatically taking into consideration that the action generates the changes that trigger the workflow ? If the action does not run, the table does not change and the workflow does not trigger…

Thanks for your patience and best regards,
Galo

[WillowMobileSys]

Galo_Barriga:

do not understood the meaning of creating an action to bridge from the IN/Out row to proper product row in the Inventory .

I am referring to an AppSheet action. The term “bridge” is meant to describe what the action is doing - bridging functionality between tables. AppSheet does have an action for this identified as “Data: execute an action on a set of rows”. Let me explain further.

First, whether you are managing the total inventory count in the Product table or a dedicated Inventory table, the same problem exists. Where I mention Inventory table below, in your case replace with Product table.

In this sample app described above, changes are being made to tables recording the increase in Inventory (the “In” table) and decrease to Inventory (the “Out” table). But then these “movements” of Inventory (i.e. added rows) need to be applied to the main Inventory table.

Galo_Barriga:

How do I set the action to run automatically taking into consideration that the action generates the changes that trigger the workflow ?

As you have figured out, the problem is that we don’t currently have an easy way to handle the following - “when a new row is added to the In/Out table apply that value to the Inventory table”.

For example, if we create a workflow that triggers when a new row is added to the In table, the AppSheet actions the workflow calls MUST also be for the “In” table. We cannot call an action that operates on the Inventory table.

In order to apply the value from the added “In” row to the Inventory table, we must “bridge” from the “In” table to the Inventory table. And we need to use the action type of “Data: execute an action on a set of rows” to perform this bridge. This type of action allows us to start with the “In” row and then transition to actions that update the Inventory row identified by the Product column.

The bigger problem is that once you have transitioned to an action to update the Inventory table, where do you get the value to update with? The action to update Inventory is “pointing” to the proper Inventory row by Product. The reference to the “In” row no longer exists.

Instead, in our update Inventory action, we need to use a SELECT() expression to pull the value from the proper “In” row. How do we identify the proper “In” row? Some use MAXROW() on a Timestamp to get the value from the most recent row. When inserting Inventory “movement” rows one-by-one, this probably works just fine.

In my personal app, I was updating Inventory based on Orders having multiple Order Detail rows and Purchase Orders again having several Purchase Order Detail rows. I needed to identify several rows requiring Inventory updates. This is where the idea of the “Processed?” column comes from. I would look for rows not yet processed, apply their values to Inventory and then mark them as processed.

[Galo_Barriga]

Thank you very much. I understood and managed to create the bridge and another action.
I really appreciate your help and the time.

Galo

[Arthur_Rallu]

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.

[daw7]

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!

[David_Gordon]

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

[WillowMobileSys]

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.

[David_Gordon]

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