Grouping Payables Transactions
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.
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.
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.
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).
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.
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.
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.