Tuesday, February 9, 2010

IM - How to group Integration Manager transactions based on transaction date

Just recently, I fielded a question where the user wanted to group a number of customer invoice transactions in a file based on the invoice date. The file happened to contain invoices downloaded from the billing system in a date range, for example 02/01/2010 - 02/05/2010. They wanted the resulting transaction batches in GP to be something like,

AR02012010
AR02022010
AR02032010
AR02042010
AR02052010

This would facilitate analysis by day and to make sure control totals matched those created on a daily basis.






The solution involves adding a field script to the batch ID field which in turn reads the transaction date field from the source query and forms the new batch ID with that information:




Very simple, but useful script. Keep in mind that you must enable the option to Add Missing batches for the integration as shown below:



When the integration is executed, it will create as many AR batches in Microsoft Dynamics GP as unique transaction dates there are in the file.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

1 comment:

pfersh75 said...

Hi Mariano - It's been a number of years ago, but I'm sure I was able to SUM records being imported using Integration Manager. I have a need over the short term to summarize records in an AP import - I have GL Distributions but need to calculate the document total. Can you point me in the right direction? The version in use is Integration Mgr 10 (I know this is almost 2015). Thanks for any help you can provide! Jeff