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.
Hello @Diogo_Ribeiro, you could use a virtual column for that.
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.
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.
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:
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):
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |