Microsoft Dynamics GP Integration Manager module is pretty simple, especially when your incoming data is prepared and structured in CSV or tab delimited text file. The same result if data is ODBC compliant and you can easily map it in the destination.
However, very often you are in the situation when you can not control the incoming text file and have to massage it in the query. In order to achieve this goal, you need to deploy Advanced ODBC queries. Good example is when you are importing customer invoices and some of them might be negative, and in this case you need to treat them either as Sales Order Processing Returns or Receivable Management Credit Memos. Let’s review the steps and highlights:
1. Create ODBC DSN. Let’s assume that you got export in comma separated values (CSV) format. Open control panel, administrative tools, ODBC, select System tab, Microsoft Text driver, uncheck Use Current Directory, select the directory where text file is located, switch type to CSV and if you have column names in the first row, then save and close the DSN. Sometimes on this step you are getting error message, locking you from saving first row column names – if this happens to you – just work with generic enumerated columns
2. Create Advanced ODBC query in IM. Here you should appeal to SQL select, group, union and having clauses. Implementing the example from the header – in order to filter out all the credit memos, please place the conditions on negative document amount. Please, not that in the case of having invoice lines in the source – negative document amount might be calculated in group by statement – check the conditions on having sum(amount) less than zero
3. If SQL Query over the text file is too complex. In this case, consider creating SQL Server DTS package, which will move text file into SQL staging table and process it through one or several SQLL stored procedures to massage data to fit integration requirements. When you are ready with the data table in SQL Server, base your advanced ODBC query on SQL view
4. Beyond Integration Manager. If you need real time online integration, and if you are on Great Plains version 9.0, 10.0 or newer, consider programming integration routine in eConnect. eConnect allows you to create GP objects from your eCommerce application
Dexterity Customization for Dynamics GP Evaluation Level Paper
When you are developer it is always a good idea to read technical manuals. But if you was just assigned to the IT team to decide if Dexterity is the right tool to customize your ERP application then first you need something which is in style of ‘easy reading papers’ or FAQ
Planning Dynamics GP Customization in Large Corporation
If you are reading this page then chances are high that you were not able to find ISV add-on and need customization project. Let’s talk about planning, quality assurance and future event such as version updates.
Dynamics GP Invoice Logo Attributed to Specific Company or Crossing the Borders of Three SOP Forms
Initial Great Plains Dynamics architecture had three SOP Invoice forms: Long, Short and Blank. Modern GP is popular in scenarios where you have more than three companies under one business entity umbrella
