Help with syncing a data value to sheets backend

[Humza_Khokhar]

I need help with an inventory app I am using from a sample found here

There is a “current stock” value that is calculated but kept as a virtual column, only shows the value on the app. I want to display that value on my google sheets backend and update the product list quantity of an item every time the stock changes.

There was a link in the comments to google plus forum where this question may have been asked, but that is now dead.

[LeventK]

@Humza_Khokhar
You can add a [Current Stock] column in your Google Sheet and use the same expression of that VC in the AppFormula property

[Humza_Khokhar]

I have done that, and when I test the expression in my colums in the editor, it returns the updated values, but it does not update the value on the actual sheet itself.

Update: It only syncs the current stock value to the product list when I modify the product list then sync. So now the question is, how can I update my sync setting so when I change a different page, that it syncs all pages.

[LeventK]

Humza_Khokhar:

I have done that, and when I test the expression in my colums in the editor, it returns the updated values, but it does not update the value on the actual sheet itself.

Have you added a physical column to your Google Sheet? I’m not talking about a Virtual Column.

Humza_Khokhar:

So now the question is, how can I update my sync setting so when I change a different page, that it syncs all pages.

Sorry but I couldn’t understand your question here. When you sync an app, generally all data is synced including the reference rows. Can you elaborate?

[Humza_Khokhar]

I am just repurposing a physical column for a different variable which wasn’t being used.

I thought the same for the syncing whch is why I am unsure as to why this is happening.

When I “Sell” a product in the app, then I sync, the data gets pushed to the “Sell” tab on my google sheets, but the “Current Stock” column does not get updated on my main “Products List” tab of my google sheet.

Now if I go to my product in the app and open the editor to change properties of the product, but I actually don’t change anything, I just open the ditor then close it, the app then believes there is a change in data and suggests to sync, then when I sync, the “Current Stock” gets updated too. I know this sounds very confusing, perhaps I can upload a screen recording?

[LeventK]

Provided the [Current Stock] column is holding an AppFormula, that formula is re-calculated when that particular row is edited actually. I may suggest using a webhook workflow which is triggered with ADDS_AND_UPDATES (or as per your requirements), calls a Preset AppSheet API: Edit Row any particular record or records in the Stocks table (just editing without changing any data) which will force all the AppFormulas to be re-calculated.

[Humza_Khokhar]

I setup the webhook workflow as follows:

Target Data: Sales -> Event: ADDS_AND_UPDATES
It will trigger when the sales table is modified.

Reaction: Webhook, Preset: AppSheet Edit Row

Table Name: Product List

Now through this setup, I have the same result where it does not sync the current stock to the sheet, unless I go into the product list, edit then sync.

I have also set it up with Target Data: Product List, & Table Name: Sales with other parameters the same, but get the same result.

[LeventK]

@Humza_Khokhar
I have thought that [Current Stock] is a virtual column, sorry. If it’s a physical column in the sheet, in your API Edit payload, just add the [Current Stock] column to the payload along with the key column, so that the value of that column will be recorded to the sheet.

[Humza_Khokhar]

Sorry but I do not understand the suggestion completely, I am very elementary with my skillset right now.

This new physical “Current Stock” Column in the “Product List” table is what I need to sync to the sheet as a change is made to the stock value in the “Sales” and/or “Restock” tables.

What is the suggestion with the API Edit Payload -> Is that the webhook you are saying to modify?

[LeventK]

Humza_Khokhar:

What is the suggestion with the API Edit Payload → Is that the webhook you are saying to modify?

Correct

[Humza_Khokhar]

How should I be modifying exactly?

just add the [Current Stock] column to the payload along with the key column, so that the value of that column will be recorded to the sheet.

Does add the column to payload mean I am changing my Preset API to Appsheet Add Row?

Currently, it is set up like this:

Target Data: Sales - > Event: ADDS_AND_UPDATES
It will trigger when the sales table is modified.

Reaction: Webhook, Preset: AppSheet Edit Row

Table Name: Product List

[LeventK]

help.appsheet.com

Updating Records in a Table

How to update records using the API.

[Humza_Khokhar]

Thanks for the help with that link, I am close to making progress.

Just to clarify “Current Stock” is a virtual column that exists only in the “Product List” Table, then there is a column “Lead Time” the name is arbitrary but the value of it I want it to be the same as “Current Stock” so I gave it the formula:

SUM([Related Restocks][Quantity]) - SUM([Related Sales][Quantity]) - SUM([Related Sample Withdrawals][Quantity]) + [Quantity]

But the trouble is with the syncing the live “Lead Time” column value such that it is updated to the sheets and is the same number as the “Current Stock” virtual column value.

I tried instruction you shared earlier for updating the records in a table, but I am not sure if those will work in my circumstance because it mentions the need to have the same column in both tables to sync them, but my other tables do not have the “current stock” rather the quantity taken or added.