Calculating Aggregates in Dynamics GP Integration Manager

We recently came across a bit of a kink when developing an accounts payable integration using Integration Manager for a client.  The client was providing us with a single CSV file of expense report line items.  Each line item represented a distribution for an expense report that would contain one or more distributions.

No problems so far.  We created two sources from the same file, header and distributions.  The header source was grouped by Expense Report ID.  With that we were all set except that it turned out the client was unable to supply us with the expense report total in the source file.  Since Integration Manager doesn’t have any facility to aggregate values when grouping we were stuck.  We had no way to set the Purchases amount in the AP document header.

We briefly considered preprocessing the source file to add the required column but decided to explore some of the scripting capabilities we hadn’t used in the past.  20 minutes and 5 lines of VBScript later we’d solved our problem.

The solution is to keep a running total of the distribution amounts as you’re integrating them and then assign that to the AP Purchases amount before saving each document.  To start with we need a global variable to store our running total and need to initialize it to $0.00.  To do this we add the following VBScript to the Before Integration event of the integration:

SetVariable "PurchaseAmount", CCur(0.00)

The SetVariable statement saves a value that can be accessed from any other script in the integration.  Note that we’ve explicitly converted it to a currency amount using CCur(0.00).  Global variables are Variants and will take on the type of their initial value.

Now that we’ve initialized our variable we need to set the running total by capturing the distribution amounts as we process them.  We do this by setting the Debit Amount of the distribution to Use Script and setting the following script”

SetVariable "PurchaseAmount", _
    GetVariable("PurchaseAmount") + _
    SourceFields("Distributions.ExpenseAmount")

CurrentField = SourceFields("Distributions.ExpenseAmount")

The first line is adding the expense amount to the running total.  The GetVariable function is returning the value of the global variable we set using SetVariable.  The second line is setting the Debit Amount to the current expense amount.  Obviously this is a simple example where we’re assuming that the expense amounts are always positive.

Once we’ve got our total for the document we want to assign it to the document Purchases amount.  To do this we go back to our integration level events and assign the following script to the Before Document Commit event that takes place right before saving each AP document:

DestinationFields("Purchases") = _
    GetVariable("PurchaseAmount")
SetVariable "PurchaseAmount", CCur(0.00)

The first line sets the current documents Purchases amount to the running total.  Since we’re not in working within the destination mappings, we set the Purchases field using the DestinationFields object that gives us access to all of the destination fields.  The second line reinitializes the running total to $0.00 for the next document.

So there you have it.  The ability to calculate aggregates and running totals within Integration Manager.  Beyond the obvious AP and AR integrations, I’m sure there are numerous useful and probably less the useful purpose for this.

Aggregate away!