Dynamics,Data,Import,eConnect, computer Dynamics GP Data Import: eConnect, Web Services, Integration

----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc

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 websites3. 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 examples5. 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 above6. 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 placed7. 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 Server8. 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 tables10. 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 interface11. For further information, please call us 1-866-304-3265 or email [email protected]