Today’s Tech Tuesday article comes to you from our Professional Service Director, Lorren Zemke:
Depending on the data source, there may be a time where you need to sum amounts from the line items into a document header. You may have a document that contains distribution details that need to be rounded or multiple data elements that need to be added together to store at the document header level. For this article we will be talking about summing amounts from an Excel file that has distribution amounts we need to sum together to create the voucher amount.
First, we will create a Global Variable that can be used to sum the values at a document line level and stored at a document header level. We start by creating a global variable which we will call GBL_PAYABLESTRXAMOUNT and assign a default value of 0. You can view a previous blog entry on how to create Global Variables and assign default values. http://eonesolutions.blogspot.com/2011/04/tech-tuesday-global-variables.html
We will start with our Distribution node to create our calculated amounts that need to be summed. Our document source contains debit and credit amounts that may be negative or positive so we will create a calculated column for each. Below is our DEBITCALC amount and we will do the opposite for the CREDITCALC.
We map the calculated Credit and Debit amounts to the distribution Credit Amount and Debit Amount destination columns respectively.
In the Restrictions script we sum the Debit and Credit calculated amounts to our Global Variable since the Restrictions script runs for each line in our data source while creating the Payables Distribution.
We are returning true in all cases for our Restriction because we are not restricting any lines just using the process for summing our values.
Now, we move to the Payables Header node where we take the summed amount and map it to the Payables amount. To get the global variable amount create a new calculated column to return the global variable since we cannot use the global variable as a source column.
Normally in our calculated column we would clear the global variable for this document so the system would not continue to add amounts for multiple documents together. But Payables also has another destination column where we want to use the same amount as our purchases amount we will create another calculated column and clear our global variable in that calculation.
Once the document amount calculations have been created, map them to the destination columns.
Have further questions or comments on what Lorren shared? Drop him a line at lorren.zemke@eonesolutions.com.