Exporting Data from Dynamics GP Notes for Consultant
There might be situations, where you need to extract info from Microsoft Dynamics GP (formerly known as Great Plains Dynamics or Microsoft Great Plains) table. In this small publication we will describe you two scenarios. First, when you need one tame data export for following data conversion into future Corporate ERP accounting system (when you are walking away from Dynamics GP to something like SAP Business One, Oracle eBusiness Suite, etc.), and second - when you need ongoing data export for feeding data into something like industry compliance or reporting systems (including simple EDI):
1. Dynamics GP custom report in Report Writer. Here, we assume that you need one-time export. This method doesnt require from you to be programmer. Simply open ReportWriter (Microsoft Dynamics GP -> Tools -> Customize -> Report Writer), then click on Reports tab and New. Now, lets assume that you need to export all your customers: give report My Customers name, Series - Sales, pick main table RM Customer MSTR. Now, click on Layout to open Layout place desired fields Customer Master table on the layout: likely in the report body section (likely Customer ID and Customer Name to be required). Then, click Microsoft Dynamics menu and save report. Now, in Dynamics GP interface, Report -> Customized, select product Microsoft Dynamics GP, series Sales, and your report should be seen now available for selection. You can print report to the file and then import text file into Excel
2. Exporting data directly from MS SQL Server Dynamics GP tables. If you are comfortable to do direct SQL Select statements (and maybe linking several tables in one records pulling), please review GP tables structure: Microsoft Dynamics GP -> Tools -> Resource Description -> Tables. This method is useful in both scenarios (when you walk away from Great Plains Dynamics GP or when you need ongoing data export). Couple of comments here, as we know that programmers are very brave in their methods and may decide to alter Great Plains tables. Please, do now alter Microsoft Dynamics GP tables, as their metadata is also stored in Dynamics.dic and other dictionaries (to be available for Great Plains Dexterity programmed GP user workstation business logic)
3. EDI data export from Dynamics GP. For the purposes of Electronic Data Interchange we recommend you consider formatted SQL Select statement (where you imitate EDI typically fixed length header, body and trailer)
4. Data export from old versions of Great Plains. First of all - Customized Report method should work for all versions of Great Plains, Dynamics GP and Microsoft Great Plains, see #1. If you are on Great Plains Dynamics on Microsoft SQL Server: 7.5, 7.0, 6.0, 5.5, 5.0, 4.0 or Microsoft Dynamics GP 7.5, 8.0, 9.0, 10.0 or 11.0, you can export via SQL Server direct Select statements, see #2. If you are on Pervasive SQL 2000 or Ctree (7.5, 7.0, 6.0, 5.5, 5.0, 4.0, 3.2 and earlier), we recommend you to consider deploying connection via ODBC (Pervasive or Ctree drivers are usually available from CD #2 of your respected version of Great Plains Dynamics). Very good option is Microsoft Access, where you use ODBC connection in linked tables
5. Data Export from Great Plains Accounting for DOS, Windows or Macintosh. There are two methods: report export to the file (feel free to call us for GPA reports discovery and printing help), or via ODBC driver (this method is typically more complex, as you need to have original GPA diskettes to generate DDF file, required for ODBC driver). We saw customers, who are converting from Great Plains Accounting to SAP Business One recently
6. Data Export from all Great Plains (including GPA) service announcement. If you are walking away from Microsoft Dynamics GP, Great Plains Dynamics/eEnteprise or Great Plains Accounting, we are happy to extract all desirable data from GP to Excel, MS Access, coma or tab delimited text files. We are also experienced in EDI data export from Dynamics GP
7. Data Import to SAP Business One. If you are migrating from Great Plains to SB1, we are offering SAP B1 Data Transfer Workbench data preparation and integration setup service, including advanced ODBC migration (from text files, directly from Oracle, or MS SQL Server or cross platform ODBC queries)
8. Data Import to Microsoft Dynamics GP, Great Plains Dynamics or Microsoft Great Plains. We offer Great Plains Integration Manager setup,. user training, including Advanced ODBC data import service, where migrate in cross platform scenarios (Oracle, text file, MS SQL, MS Access, MS Excel, etc.), we are also offering ecommerce data import to Microsoft Dynamics GP in quasi real time (GP Integration Manager) or real time (eConnect ecommerce integration programming, Alba Spectrum Order Connector to Sales Order Processing Invoices or Sales Orders)
9. Custom Ongoing Dynamics GP Data Import/Export Programming in Great Plains Dexterity. If you need really flexible and advanced custom Great Plains data import or export add-on, feel free to call us to discuss Dynamics GP Dexterity Software Development options. Of company is Microsoft Dynamics GP Source Code Partner (we have access to Dexterity Source code, which allows us to see how original GP business logic is coded in Dynamics.dic Dexterity Sanscript codes)
Andrew Karasev, Alba Spectrum, 1-866-528-0577, help@albaspectrum.com http://www.albaspectrum.com We serve Microsoft Dynamics GP Great Plains community since 1994 in US and Internationally. Local service is available in Chicago, Los Angeles, San Diego, Houston. Remote support USA and Canada nationwide and internationally. We speak English, Spanish, Portuguese, Russian, Chinese. Please, visit our info portal http://www.pegasplanet.com We have customers in the following states and provinces: IL, CA, Ontario, FL, TX, MI, MO, NY, UT, NV, AZ, LA, GA, Quebec, Russia, Costa Rica, Puerto Rico, Brazil, Oceania, Germany, China. We also serve SAP Business One as good alternative for Corporate ERP for multinational corporation overseas subsidiaries