Skip to content


Grouping Payables Transactions

Janice Woytek asked 9 years ago
I am importing from an Excel spreadsheet which has 2 lines for the same vendor. I want one payables transaction with the total for the document amount. I am grouping on the vendor ID and I am getting one payables transaction but the document amount is that of the first document, not the total.
Best Answer
Lorren answered 9 years ago

When you click the Group By option in the Node mapping there are two columns added to the source column list.  One of those is the function which defaults to first.  All you have to do is change that column to use Sum instead for the source column that would add up to be the document amount.  If I had a source column called DistributionAmount, I would change the function for that column to Sum and map the DistributionAmount to my document amount on the Destination and Smart Connect will do the calculation for you.

Janice Woytek replied 9 years ago

Thanks Lorren,

I have the function set to sum on the amount field and I am grouping on the vendor ID and my key field is vendor ID. I am still getting the first amount rather than the total.

Michelle replied 9 years ago

I have found that when trying to group documents properly I need to have more than just the vendor id (for ex.) as key.  Try adding the document number as a key field as well.  That has worked for me in the past.

Angel replied 8 years ago

Any updates on this? I’m having the same problem and can’t find a way to get the map to calculate the subtotal for all 4 lines instead of only the first row.
I’m grouping on two columns for the header: Vendor ID and Buyer and have a Sum for Subtotal, which is a calculated field (return _Quantity*_Unit_cost).

Angel replied 8 years ago

I added an extended cost column to my source file, so my PO subtotal is now the sum of the “extended cost” column.

The problem seems to be related to the sum of a calculated column on the PO header.

Any help for having smartconnect calculate the extended cost will be appreciated.

Angel replied 8 years ago

I modified the node to make the Subtotal non-required and voila!! The node would calculate the PO subtotal automatically. No need to have an extended price column or a calculated field for subtotal.

Chris replied 8 years ago

I believe we have it logged to change that subtotal field to be not required. The eConnect documentation that we follow when defining the requirements, fields, etc still has it marked as required although if you try run the stored procedure directly it is not required and will do the subtotal like you found. Glad that is working for you.


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