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:
adding a column to the spreadsheet (called In stock now), regenerating structure, under Formula for that column adding = [Current Stock]
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?
Each of these could call โSet Current Stockโ or any other actions needed.
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):
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.
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.
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.
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:
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!
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.
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.
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.
Thanks for your patience and best regards,
Galo
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.
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.
Thank you very much. I understood and managed to create the bridge and another action.
I really appreciate your help and the time.
Galo
Hi Shane,
to do that, you can use a combination of:
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.
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!
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.
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
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |