Using SUM with [_THISROW_BEFORE]

[MauriceWhelan]

Hi

I am using the following expression in a workflow to return the SUM of [Total Cubic Metres]. The workflow itself is triggered if the [Total Cubic Metres] column is changed for any row.
<<SUM(SELECT(Create Works order[Total Cubic Metres],[Charge Number]=[_THISROW].[Charge Number]))>>
The [Charge Number] in one example is associated with 4 different rows in the table. What I want to achieve is to show the SUM total of [Total Cubic Metres] for that charge before the change and the SUM total for [Total Cubic Metres] after the change is made.

So something link SUM(SELECT(Create Works Order[_THISROW_BEFORE].[Total Cubic Metres],[Charge Number]=[_THISROW].[Charge Number])) & SUM(SELECT(Create Works Order[_THISROW_AFTER].[Total Cubic Metres],[Charge Number]=[_THISROW].[Charge Number]))

I know it can’t be done with the above expression but I am hoping the same result can be achieved some other way.

Any help would be great, thank you.

[Suvrutt_Gurjar · Accepted solution]

Please explore the following approach

Please create an additional real column called say [SUMCUBICMETERSBYCHARGE] with an expression something like

SUM(SELECT(Create Works Order [Total Cubic Metres], AND([ Charge Number]=[_THISROW].[ Charge Number], [Key Column]<>[_THISROW].[Key Column] )))+[Total Cubic Meters]

You may hide this column if you wish.

In the template, you could use the following two values to get the before and after values

Total Cubic Meters For The Charge Number Before : <<[_THISROW_BEFORE].[SUMCUBICMETERSBYCHARGE]>>
Total Cubic Meters For The Charge Number After : <<[_THISROW_AFTER].[SUMCUBICMETERSBYCHARGE]>>

Please note, since this expression is based on real column, it will show the “before sum” for existing rows in the table after the second change for that row, as initially the newly created [SUMCUBICMETERSBYCHARGE] column will be blank for existing rows before the row is opened for editing.

Also since the expression involves a SELECT() statement to make a SUM across rows, there could be impact on sync time depending on amount of data in table.

Please do test well for your requirements.

[MauriceWhelan]

Thanks a million @Suvrutt_Gurjar. I will test your suggestion and revert back.

[MauriceWhelan]

hI @Suvrutt_Gurjar. This recommendation appears to have done the trick. Many thanks again.

[Suvrutt_Gurjar]

You are welcome and thanks for the update Maurice. Good to know it works per your requirement.