There are several tools to do one time data conversion or ongoing integration into this mid-market Corporate ERP. There are more than what you see in the header of this publication. For example, you can use obsolete tool: Table Import, which was very useful for non-MS SQL Server versions.
If you are on the current version, you can import data in Query designer – insert statement. There is a possibility to deploy generic data managing tool, such as Microsoft Access, Excel, where you can connect to the company database via ODBS DSN. However, all the methods, mentioned in the last two sentences do not validate business logic and due to that fact, they might lead to the data integrity compromising – so we do not recommend them. At least, if by whatever reason you have to use custom SQL stored procedure for data import, try first to test it multiple time in the copy of your production company, which you could reload from backup as many time as you need to support your learning curve. Let’s begin with eConnect:
1. Let’s assume that you are MS Visual Studio programmer and ready to add to your project eConnect libraries. This tool also has SDK with variety of the code samples in C#, VB and C++. Modern programmer probably codes mostly by code samples and eConnect is not an exception. This tool allows you to create, update or delete documents as well as master records. Good example of document is Sales Order Processing Invoice and good example of master record is customer. Just to give you some background on eConnect – this tool is coded as set of encrypted stored procedures and with version 10.0 and current 2010/11.0 it is installed automatically on the server (SQL Stored Procedures in the company and Dynamics databases – please note, that not all of the stored procedures are coming from the eConnect, some are traditional and deployed for Dexterity logic performance improvement – those are not encrypted)
2. Web Services. This term might sound like something very cool and techy, however it is just additional level of abstraction, where through SOAP, HTTP you are calling eConnect methods. Of course, good thing here about web services – they could help you resolve computer platform incompatibility, for example, you may call Web Service to create, let’s say SOP Invoice from you eCommerce application, residing in Linux/PHP/MySQL. Obviously this paper doesn’t pretend to be technical, but the good news is – you can find Web Services documentation on the open side of the Microsoft and MSDN websites
3. Integration Manager – Simple Integration. This tool, in our opinion is the best from the standpoint of its simplicity and at the same time its reasonable programmability and flexibility. Technically it doesn’t require programming at all, if you plan to integrate reasonably straight forward text files. If this is your case, please review sample integrations, typically the most difficult here to understand the concept of creating the document with the header and lines from one source text file – here he need to produce two queries: header (where you do grouping and multiple lines are merged into one record for the document header) and lines (where you include typically all the lines from the text file)
4. Integration Manager – Advanced ODBC integration. Here you are breaking through the restrictions of the text driver and creating the query based on the ODBC DSN, where in turn you can call SQL View, for example and in this view you are doing all the filtering logic. SQL View could be cross platform with OPENROWSET, allowing you to join MS SQL Server and other databases: Oracle, MySQL, Pervasive SQL to give you several examples
5. Integration Manager – VBA scripting and Translations. If you need to alter integration logic on the fly, good example could be to change document date based on the day of the week and month – you can do it in Before Document VBA script. There is also the possibility to incorporate Microsoft ADO (old fashion one, not ADO.Net) call to the database from these VBA scripts, but we would rather recommend you to switch to Advanced ODBC query and do whatever is needs in the cross-platform SQL View, see paragraph above
6. Integration Manager in eCommerce shopping cart integration scenario. Of course, if you are ecommerce web programmer, the first thing you may think about is eConnect - this tool was specially created for you back in 2003. eConnect allows you to propagate paid shopping cart to Great Plains Sales Order Processing Invoice in the real time. Integration Manager, however can do the same job. Imagine, you are exporting paid shopping carts every ten minutes into text files, then IM is scheduled to fire integration every ten minutes and move the integrated files into the Integrated, Exceptions folders (VBA scripting). Described scenario is quasi real time, but it is very elegant and cheap to implement and your customers would not see the difference, as nobody expect the order to be shipped the same minute as it is placed
7. IM technology layers. In fact, with version 2010 and 10.0 Integration Manager has eConnect connector. And traditionally it was using and this is still an option – OLE Server, where user workstation should be running in the background as OLE Server
8. More unusual tools, such as Microsoft Access. Here what you do you create the table based on the External data, select ‘More’ and pick ODBC, then select Link to the data source by creating linked table. Might be good option, especially when you consider yourself as MS Access developer and plan to create the reports directly in Access. Also, in fact if you are on the old version of Great Plains (on Dynamics on Pervasive SQL 2000, Btrieve or Ctree, as well as Great Plains Accounting for DOS, Windows or Mac – Access might be a good choice, however for archaic GPA we would rather recommend you to export data directly in Pervasive SQL Control Center and then work with the copied tables in MS Access)
9. Table Import. This tool is still available, but we would rather discourage you to use it, as it doesn’t validate data logic. Earlier versions of GP had special technical articles on how to import data via TI and then do various validations via CheckLinks – those we rather addressed to technical consultant, and not end user. Table Import is similar in what it is doing to SQL Insert statement, if you are on SQL Server DB platform. Rather consider Integration Manager, where data is hundred percent validated before it goes into the ERP tables
10. Dexterity. Sometimes data import is done in Dexterity, as here you can push transactions directly to historical tables, for example (this is impossible neither in IM nor in eConnect). Dex was especially popular in earlier 2000th, and now we see more eConnect and Web Services oriented projects. If you really need automatic posting to history from eConnect project, you can request information on Alba Spectrum Posting Server. This application is coded and Dexterity and allows you to place the batch into the custom table, triggered for being posted the same way, as it would be posted via user interface
11. For further information, please call us 1-866-304-3265 or email help@efaru.com
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
