Skip to content


Sum calculated field without grouping

Tanya Henderson asked 2 years ago
Is there a way to sum a column of amounts in a calculated field without using the grouping function?
Patrick Roth Staff answered 2 years ago
Not anything in SmartConnect that would do this.  Either we group the data and we can set the column to SUM or else we don’t and you don’t get any kind of “SUM” functionality.
But depending on your source data, if it was SQL you could run a Query in a calculation to get that value by making a SQL connection and then running your statement.
But assuming not, the 100% solution is to add it up yourself.
Tech Tuesday: Line Sequence Numbering | eOne Solutions
In the above, they use a Restriction script to keep track of Sequence Numbers.  This would be the same kind of idea.
So say I was creating a PM Trx map and had the distributions in it.  So i have the Debit & Credit dists but i don’t have a “total” for the document amount (seen this more than a few times).
ACETRAVE001, 04/12/2027, 100, 0, 000-1200-00, 6
ACETRAVE001, 04/12/2027, 200, 0, 000-1200-01, 6
ACETRAVE001, 04/12/2027, 0, 300, 000-1400-00, 2
So in the above case, I have a $300 PM Invoice and my dists balance – but i don’t have a “doc total” which i will need on the ‘create transaction’ node.
What I could do is make a global variable – say GBL_DOCAMOUNT.
Then in the Restriction script on my ‘add distribution’ node, I would keep track of the totals in my variable:
Restriction script:
if _DISTTYPE = 6 then
end if
return true
So in this case, I’m counting up my debits and adding them into the global variable.  I could have done it for the for DISTTYPE= 3 and CREDITAMT or just checked for _DEBITAMT > 0 since for the PAY dist it would be $0
Restriction script:
return true
The code is simpler and now at the end of my distribution node, GBL_DOCAMOUNT will be 300.00.
Then finally, we need that value in a calculation.
and we map it where we need it on the header.
Lastly, we need to clear it back out so that if you have 2+ documents, we don’t start at 300 and count from there.

So in the “Before Document” task, we clear it out.
return true
And done

If you would like to submit an answer or comment, please sign in to the eOne portal.