Calculating & updating Table 1 by picking data from multiple columns in Table 2

[Neeraj_Malik]

Looking for solution for the following scenario

1. Having SORD Table with the following structure
   
   
2. And Sale Table with the following structure
   
   
3. Values in SORD Column is unique and key in SORD Table
4. Key for Sale Table is the row number
5. Same SORD of SORD Table gets captured under different columns with every new entry in Sale Table
6. Want to calculate & update Balance Left in SORD Table by subtracting the multiple Qty values against the same SORD value in different columns Sale Table from Qty against the same SORD in SORD Table
7. Balance Left in SORD Table needs to be updated automatically with every new row added in Sale Table. This could be one option; Or

Other option could be
Since Balance Left is also being calculated in Sale Table in every row, but stored in corresponding different columns. May be there could be a way that the most recent entry of Balance Left in Sale Table can be updated in SORD Table with every new row added in Sale Table

Any guidance/help appreciated

[Rafael_ANEIC-PY · Accepted solution]

Hello Neeraj, you would need to run three separate SUM(SELECT()) combinations and adding them together in a virtual column in order to substract that in your SORD table, doing so in a virtual column makes it auto update on every new row (virtual columns re-calculate on every sync), so for example your formula would look like this:

In virtual column “Balance Left”

[Qty] - (SUM(SELECT(Sale[Qty_1],[SORD_1]=[_THISROW].[SORD]) + SUM(SELECT(Sale[Qty_2],[SORD_2]=[_THISROW].[SORD]) + SUM(SELECT(Sale[Qty_3],[SORD_3]=[_THISROW].[SORD]))

Bear in mind that the “select” function is an expensive formula, and if you have a lot of items in your SORD table it could slow down your app, if you were to use relational tables you could have all your sales quantities lined up on a single column, so you would need only one select()+sum() function to get the value you need.

[Neeraj_Malik]

Thanks @Rafael_ANEIC-PY
Is there any way that this info can be stored in the actual column in the table instead of a virtual column and gets updated with every addition of new row in SALE Table. B’s this column is linked to some other flow that needs filtering based on this value. So virtual column can’t be used.

[Rafael_ANEIC-PY]

In that case you could use the formula i mentioned before as an app formula, and since app formulas only recalculate when you change a value in their row, you need to set up a workflow that automatically invokes an action when a new row is added to the Sale table, let’s call that action “Refresh” , Refresh should invoke an action that changes at least a single value on all the rows that you wish to refresh, let’s call that second action “Change”, it’s common to add a new column to work as a “Trigger” for these kind of uses, so let’s say you add a new column called “aux”, now you configure the “Change” action to set the values of some columns on given rows, more specifically set the Trigger column a random value, for example uniqueid(), and you wish to do that for all your rows in the SORD table, so the condition for the action would be simply =TRUE.

So, to recap, these are the steps you need to take:

-Create an action that assigns a random value to a row in the SORD table in the Trigger column (i called it Change)
-Create an action that executes the “Change” action on a set of rows, this action should reference your SORD table and run for all rows (i called this action Refresh)
-Create a workflow that runs every time there’s an update or an add in your Sale table, and have it invoke the “Refresh” function

[Neeraj_Malik]

@Rafael_ANEIC-PY,
This is one is not that is for a non-techie like me so I have opted your 1st recommendation and changed my flows to accommodate that.
Thanks very much!!!