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?
Thanks,
Tanya
Thanks,
Tanya
Answers
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).
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
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