Display virtual coumn data in sheet

[mahh1998]

Hello,

Can anyone help me with a solution on how to display data from a virtual column in my google sheets.

The virtual column uses this app formula:
SUM(
SELECT(
Purchases[Antal],
[Produktnavn] = [_THISROW].[Produktnavn]
)) -
SUM(
SELECT(
Sales[Antal],
[Produktnavn] = [_THISROW].[Produktnavn]
)) +
[Initial Stock]

Is there a way to display the same thing in my google sheet while still updating the column. I am using it to update quantity in a inventory management app. I want to sync up this column to send an email if the quantity is below a certain threshold.

[QREW_Cam · Accepted solution]

This might make it easier.

- Make Lagerbeholdning a Number Data Type
- Make the App formula of Lagerbeholdning to: [Current Stock]
Every time this column updates this value you will update, which is good, but we need it to update anytime a Pur chase or Sale for this product gets added/updated/deleted

To accommodate for this need:

Add a column to the purchases table. [update], Regenerate the table and make the data type number (Number)

Add action to: Called update purchases: Table: Purchases // Do this: data: set values of some columns in this row,
> select the 'update' column. the formula to the right put:    [update]+1

> prominence: Do not display

Add a Bot:

Configure Event > Custom event: Table: Purchases // Data change type: All changes

Add a step > create a custom step > click step > Run a data action: run action on set of rows // Referenced Table: Products // Referenced Rows: LIST([Produktnavn]) // Referenced Action: 'update'

Repeat for Sales

[Markus_Malessa]

Are you wanting to send the email from Appsheet or from another program? If you are sending it from Appsheet, there is no need to have the virtual column in your GSheet. Otherwise, just add a column in your GSheet and in Appsheet set the column formula the same. If you are concerned about the formula updating when it is a 'set' column vs a virtual column then incorporate an action or bot that would update the relevant row.

[QREW_Cam]

What I suggest is having an App Formula Column in the Products Table with the same formula as above. This will be hidden in the App.

When a Purchase or Sale record gets Add/Edit /Deleted a bot gets triggered that updates the related Product Record; you could update a [Last_Update_DateTime] column or any other arbitrary column to force the App Formula to recalculate..

This will update that app formula each time that number will need to be refreshed, allowing you to have a sheet visable column but also with the update assurance of a Virtual Column.

[mahh1998]

Im not quite sure how im supposed to do that.

The marked one is the column that is supposed to be updates with the "Current Stock" virtual column.
When i tried to make the action i could only update cokumn

[QREW_Cam · Accepted solution]

This might make it easier.

- Make Lagerbeholdning a Number Data Type
- Make the App formula of Lagerbeholdning to: [Current Stock]
Every time this column updates this value you will update, which is good, but we need it to update anytime a Pur chase or Sale for this product gets added/updated/deleted

To accommodate for this need:

Add a column to the purchases table. [update], Regenerate the table and make the data type number (Number)

Add action to: Called update purchases: Table: Purchases // Do this: data: set values of some columns in this row,
> select the 'update' column. the formula to the right put:    [update]+1

> prominence: Do not display

Add a Bot:

Configure Event > Custom event: Table: Purchases // Data change type: All changes

Add a step > create a custom step > click step > Run a data action: run action on set of rows // Referenced Table: Products // Referenced Rows: LIST([Produktnavn]) // Referenced Action: 'update'

Repeat for Sales

[mahh1998]

Hello again,
When i try the following i have no available option on "Referenced Action"

[QREW_Cam · Accepted solution]

Sorry it looks like you were doing everything rhow i said. I saw 2 mistakes in my explanation,

1. the 'Update" action should be on the Products Table

2. the new column that we add, [Update], should be in the products table instead.

Corrections:

Add a column to the PRODUCTS table. [update], Regenerate the table and make the data type number (Number)

Add action to: Called 'update products': Table: PRODUCTS // Do this: data: set values of some columns in this row,
> select the 'update' column. the formula to the right put:    [update]+1

Once doing this the refrenced action will show up.

[mahh1998]

It seems to work perfect now! Thank you very much. Have a wonderful day! 🙂

[QREW_Cam]

Great to hear! Sorry for those two misleading parts. Hard to picture the whole editor UI in my head haha!