Microsoft Dynamics ERP: Column magic with SmartConnect

Posted By
BDO

Over the past few months we have encountered a number of instances of some rather unique source files produced for importing data into Microsoft Dynamics GP.  In general, when we see data related to general ledger transactions it is provided as a single account distribution per record. This is a nice fit with tools like SmartConnect and usually makes for an easy integration. This quickly changes however when data is presented with multiple account distributions per record. Since the developer’s mind tells us that there is always a solution, the wheels start turning…. Custom code to pre process the file? Create a new eConnect stored procedure? Actually the solution turned out to be quite simple.

Given the following file layout, the goal is to create two GL distributions per record:

 

When configuring the SmartConnect map source, ensure that the ODBC connection is used to read the text file.  This will afford greater control over how the data is extracted.

 

 

Now that we are able to write our custom SQL, we start with a query that extracts the data as a single record. The problem here is that it will be difficult to map the two GL distributions on one line to a single call to the “Add Distribution” node.

 

Now that we are able to write our custom SQL, we start with a query that extracts the data as a single record. The problem here is that it will be difficult to map the two GL distributions on one line to a single call to the “Add Distribution” node.

 

Now we are dealing with a typical GL integration. Problem solved! Incidentally, I did mention earlier that creating a new eConnect stored procedure was a possible solution. Let’s explore that in a future post.

 

For more great Microsoft Dynamics GP tips, tricks and software tutorials, visit our You Tube channel and review our playlists.

  • Microsoft Dynamics GP Administration Tutorials
  • Microsoft Dynamics GP System
  • Smartlist/Reporting Tools for Microsoft Dynamics GP
  • Receivables Management in Mcrosoft Dynamics GP
  • Payables Management in Microsoft Dynamics GP
  • Security and Microsoft Dynamics GP
  • Microsoft Dynamics GP Home Page Tips
  • Microsoft Dynamics GP General Ledger Tips & Tricks

For more information, please contact your BDO Client Manager and visit BDO Solutions.

 

 

BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing

Download