Update value from Table 2 to "that value" plus a value from Table 1

[Diogo_Ribeiro]

Hello everyone,

I am currently building an App to control Stock levels.
I would like to know how can I update a value from Table 2 with a value from Table 1 whenever I add a new row to Table 1. The update would be of type: table2.valueBeforeUpdate + table1.valueFromNewRow.

Here is some additional info on my app. Hope this additional info may be of any help.
My app has 4 tables: “Product DB”, “Warehouse DB”, “Movements DB” and “Stock DB”.
“Stock DB” is a relational table which relates “Product DB” with “Warehouse DB”, letting me know how many products I have per warehouse ([currentStock]). I register all stock movements in “Movements DB”.

Whenever I add a new row to “Movements DB” I want to update the referenced “Stock DB” [currentStock] value to: [currentStock] + movementsDB[qtt].

I have tried to make this work via “Data: execute an action on a set of rows”, the problem that I am having is that I don’t know how to reference a value from another Table to the Table where I want to make the change happen.

Looking forward to your replies. Thanks in advance.

[Rafael_ANEIC-PY]

Hello @Diogo_Ribeiro, you could use a virtual column for that.

help.appsheet.com

Virtual Columns

Check out how you can use virtual columns to add more information to your app.

Turning your [currentStock] column In your “Stock DB” table into a virtual column would apply your stock formula again each time a sync is done within the app.

[Diogo_Ribeiro]

I came up with that solution previously. Yet, the more movements recorded I have, the longer it will take to synchronize the app. I am looking for a solution that does not imply recalculating every row each time I sync.

The other solution that I came up with was having a computed formula on [currentStock]'s Initial Value.
I make it reset to the initial value each time I update that row.
And I update that row’s [lastChangeTimestamp] each time I had a new Movement.

Still, I think it would be better and faster if I could directly add or subtract the value that I want directly on the table, instead of having to calculate all the related moves each time I make an update.

[Rafael_ANEIC-PY]

I understand, i suggest you take a look at this post, the original poster wanted to update a value on a table when he saved a form, which in your case would mean to refresh your stock column when you insert a new row, take a look:

Expression to recalculate all rows in a table (child/parent) Questions

Hi, I try to set an action and a workflow to update correctly all rows of a table containing sums of related table rows. I need to update the sum colum of the parent table as soon as there is an add/edit/delete in the child table. I think I understood the concept of worflows to watch changes in child table but I don’t understand what kind of expression is needed in the action to be linked with the workflow. Any suggestion ? Thanks

[MultiTech]

You might find something helpful in one of the In-App Guides I’ve made; a couple are specific to parent/child updates and how to implement a quick-update system in your app (and tons of others):

Introducing A New Resource: In-App Guides Tips & Tricks 💡

The following guides were published and made public on 4/1/2020: Avatar Builder This guide shows how to quickly implement the avatar builder first introduced to the community by Tsuji Koichi How To Import Gravatars Automatically This guide shows how to pull the Gravatar for an email RoboHash Images This guide shows how you can use the RoboHash service to pull in temporary images as stand-ins (including monsters, robots, kittens, etc.) Add GeoFencing This guide shows how to add Ge…