Hola!
Tengo una app de inventario donde utilizo una columna virtual [CANTIDAD], que mediante una formula hace el calculo entre las salidas y entradas de productos. Cantidad = Stock Disponible
Lo que necesito es mostrar estos datos en mi hoja de Google Sheets como una columna normal.
Intente crear una columna normal llamada [STOCK] y en el campo Initial Value escribir [CANTIDAD] pero no funciona.
Hay alguna manera de hacerlo?
Gracias!
if the data are already in that table then initial value won’t help as it applies only on the new rows being moved or copied,…
i’d add it as formula , but for previous values that are already calculated in the VC, you might need an action and temporary column to do an update and get the previously added value .
Could you explain to me how to do that?
Thanks @Hussein_Osseily
From the appsheet builder:
-go to Data / Columns
Find the column STOCK , and in the Formula column paste this [QUANTITY]
now press “Test” and check if the expression is giving you the desired value (whihc it should, unless the QUANTITY column is empty
if the test is all okay proceed with below:
creat a temporary column in your sheets where the STOCK column is , lets say “test”
from the app builder , behavior, Action / Creat new Action
Action name: Action Refresh STOCK
For a record of this table: the table name where the column STOCK is
Do this: Data: Set the values of some columns in this row
Set the Columns: STOCK = [QUANTITY]
Appearance / Prominence = Do not Display
Now creat another Action:
Action name: Refresh STOCK
For a record of this table: the table name where the column STOCK is
Do this: Data: Execute an action on a set of rows
Referenced Table: the table name where the column STOCK is
Referenced Rows: FILTER(“Table Name”, ISNOTBLANK(QUANTITY))
Referenced Action: Choose the first action you have created in the previous step.
Appearance / Prominence = Display as primary
Now save the changes, refresh your app, go to your application, go to that view, select a row and press on the action you have created, and wait for the sync, after the sync is done, you shall have the values of all the records in your real column in sheets, and for the newly added rows, it shall be there automatically registered. then you can either delete these two actions or keep it for other uses.
Another workaround of above, is to create an action on that table of type: APP: export this view
-Export the view
-VLOOKUP the values into your real column STOCK
in both ways, for newly added rows , it shall automatically pull the QUANTITY in the VC into the real column in sheets as you have added that into the formula column in the app builder
Why not just use the Expression in CQNTIDAD in the real column STOCK ?
I dont understand,
Do you mean [CANTIDAD] formula in the column [STOCK]?
I already put the [CANTIDAD] formula in [STOCK] and didn’t work.
The reason why its not working is because real columns wouldn’t update the values already synced before creating the formula unlike virtual columns, this is why i suggested you to creat a temporary action to update the previous records
thanks @Hussein_Osseily
But i dont understand step 1
-go to Data / Columns> > Find the column STOCK , and in the Formula column paste this [QUANTITY]> now press “Test” and check if the expression is giving you the desired value (whihc it should, unless the QUANTITY column is empty> if the test is all okay proceed with below:
Expression is not giving me desired value (Cantidad)
Then actions, but first I need to resolve step 1
Thanks!
What i mean with “Test” is as below, for you to know if the collumn formula is giving you the desired result , you can check as follow:
firs do as what you did before, STOCK = Cantidad
then press on test as shown below:
a new web page will open , where you can see the expression result:
in the red square in your case you shall see the values from the column CANTIDAD as you have requested
Thanks @Hussein_Osseily
I created the 2 actions, sync, when finished [STOCK] column loads the quantity perfectly but… when I add a new product or the quantity of a certain product is updated, the [STOCK] column is not updated automatically, only updated manually with the action.
Am I missing a step?
@marinocity hola.
si, lo que pasa es que debes configurar que dicha accion se ejecute automaticamente con cada cambio en l atabla donde estan las columnas CANTIADD Y STOCK.
Tienes dos opcioenes, o fijas la accion a la zona BEHAVIOR de la vista form.
O creas un bot que se accione con cada modificacion+creación de data en tu tabla.
Gracias @Luis_Rodriguez_1
Agregue la accion en la tabla INVENTARIO, behavior vista form, donde estan las columnas [CANTIDAD] (Virtual) y [STOCK] (normal) pero no actualiza las modificaciones de stock o alta de nuevos productos.
Tampoco lo hace con el bot 
As you already updated the old records, now add it to the expression column and the formula. So every time a new record is created that column is updated. Try it
“now add it to the expression column and the formula”
I dont understand how to do that
ah entiendo. Pasa lo siguiente:
SEgun puedo deducir, tienes al menos 2 tablas, una de INVENTARIO y otra de MOVIMIENTOS. Ahora debes tener en cuenta que cuando creas un nuevo item en INVENTARIO, sus columnas STOCK y CANTIDAD van a mostrar el resultado de la suma y resta de los registros de movimientos que se tiene en ese momento.
Si despues haces uns salilda o entrada o modificas uno de esos registros(modificacion a la tabla de MOVIMIENTOS), entonces la accion que has creado no te va a servir dado que esta vinculada a la tabla INVENTARIO. Para poder “llamar” a dicha acción, debes crear otra accción del tipo “ejecutar una ccion en otra tabla cuando se registren cambios en esta tabla”.
en esta iamgen por ejemplo, configure la accion ‘cantidad’ para que al ser ejecutada en la tabla ‘kardex temp’ a su vez ejecute la acdcion ‘ACT CANTIDAD’ en la tabla INVENTARIO para lasfilas que tuvieses el mismo SKU (ID).
add it to the expression into the initial value, then once you add a new row to your table the real column will be updated automatically
en la parte “FOR A RECORD IN THIS TABLE” deberias colocar la tabla donde se registran tus movimientos de ingreso y salida.
El problema es que son 2 tablas, cada una con su respectiva tabla hija.
VENTAS > DETALLE VENTAS
COMPRAS > DETALLE COMPRAS
tienes idea con otro metodo de mostrar los datos de una columna virtual, en este caso la cantidad disponible [CANTIDAD] en una columna real?
El metodo arriba que amablemente me explico @Hussein_Osseily no me funcionó
