Skip to content
+1-888-319-3663

COMMUNITY FORUM

Sum calculated field without grouping

Tanya Henderson asked 6 months ago
Is there a way to sum a column of amounts in a calculated field without using the grouping function?
Thanks,
Tanya
Answers
Patrick Roth Staff answered 6 months 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).
 
VENDORID, DATE, DEBITAMT, CREDITAMT, ACCOUNT, DISTTYPE
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
   GBL_DOCAMOUNT += _DEBITAMT
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:
GBL_DOCAMOUNT += _DEBITAMT
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.
 
CALC_DOCAMOUNT
return GBL_DOCAMOUNT
 
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.
GBL_DOCAMOUNT = 0
return true
 
And done

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