Skip to content
+1-888-319-3663

COMMUNITY FORUM

Grouping a Calculated Column

Dominique asked 3 years ago
I’m trying to group the account and debit in my distribution so that it is one line item.
I’ve used calculated columns for the debit and credit amounts as my source file has negative and positive amounts.
 
My data file has the accounts/amounts as follows:
0001-500000-022  288.34
0001-500000-024  47.10
0001-600060-022  -5.03
0001-500000-022  74.84
The vendor, invoice number, etc. are the same for all line items.
 
When I run the map, the distribution looks exactly as my data file looks.
I would like the first and last lines to be one line in the distribution.
I’ve tried grouping several different ways but it continues to return an error. It will only process one distribution line and tell me that the invoice number already exists for the 3 remaining. Is this impossible to do/group with calculated columns?
Answers
Ethan Sorenson Staff answered 3 years ago
Dominique,
 
Yes this can be done. You only want each account to be brought into GP once. So we want to group the Line mapping on Account Number. Then, on the amounts column it will be set to ‘first’ change this to ‘sum’. This will tell SmartConnect to return the sum of all Amounts with the same Account Number. 
 
You can use the field as you normal would in a calculated column and it will use the sum instead of the first available number.

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