Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday – Sum Document Line Amounts Using Global Variables


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.


tt-1

We map the calculated Credit and Debit amounts to the distribution Credit Amount and Debit Amount destination columns respectively.

tt-2

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.


tt-3

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.


tt-4

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.


tt-5

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.

Leave a Comment





RECENT POSTS


Happy Thanksgiving from eOne! - US Office Closed November 25 & 26
Popdock: What is your Locale?
Q4 2021 Partner All Hands Call - Recording Now Available
An Announcement regarding SmartList Builder and SmartView from eOne Solutions
Join us for the Q4 2021 Partner All Hands Call!

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.