Former Great Plains has special naming convention for its tables and the names are not self-explanatory, as this application was designed in earlier 1990th, where multiple competing DB platforms often used alphanumeric conventions. We would like to walk you through user interface, where you can find the table name, description and fields.
Then we would like to discuss name convention and give you several examples. And finally we would like to review linking strategies in Reports and SQL queries. Before we go – we would like to mention that GP is one of Corporate ERP applications, where you can do direct data repair in SQL statements, so be careful if this is your assignment. Other modern ERP, such as SAP Business One or Dynamics AX Axapta do not allow you to update or inserts directly in SQL. We recommend you to back up the table, which you plan to repair via Select Into statement, as the whole company database backup might be too heavy procedure. Let’s begin:
1. Where to find. In new version, such as 2010, 10.0, 9.0 in menu Dynamics GP -> Tools -> Resource Description -> Tables, here keep product GP, switch required series (by default it picks Financial) then decide on sorting and find the table in the right pane. There is such concept as Table Group, good example would be Customer Master, where tables associated with customer master records are combined: customer master (RM00101), address master (RM00102), summary (RM00103), etc. Technically every module, which is coded in Dexterity and listed in Dynamics.set file could be reviewed on the matter of its table structure (in this case, simply pick the module on the upper left corner). Please note that structure is generated on demand and stored in Ctree table, usually located in Data subfolder, exactly in the same one, where such important file as Dex.ini is located
2. Naming Convention. It is alphanumeric. Alpha part is reasonably simple, as it is always in prefix and it resembles module name in its abbreviation, for example GL stays for General Ledger, SOP for Sales Order Processing, INV for Inventory module, etc. Numeric part is more complex, but it also has a pattern. First of all – tables are categorized as Master (those have 0 as the first digit next to alpha prefix), Work Transaction have 1 next to alpha, Posted Document have 2 respectively, then Historical Transactions have 3; Module Setup tables have 4. Let’s look at examples. SOP10100 table is Sales Order Processing Work Document Header and when document is posted it goes into SOP10200 SOP Document Header Historical file. GL00100 is Account Master, GL20000 is General Ledger Posted Transaction file, and when you close Financial Year all these records are moved to GL30000 GL Historical Entries
3. Linking in Reporting. We recommend you to look at tables groups. You begin linking from your Open, Historical or Work document header, then you link Lines document in the same aging category, and finally you link to the master record (typically customer RM00101, vendor PM00200, employee UPR00100, inventory item IV00101). If you are building challenging Crystal Report or the one in SSRS (SQL Server Reporting Services), we recommend you to base it on SQL Stored Procedure (the most powerful, as here you can create temporary tables to store staging queries and unionize temp tables, deploy SQL transactions, etc.), or SQL View (where you only use Select and Union). We often hear the stories, when report created in Crystal Report Wizard produces duplicate records and does other annoying things. When you separate data pulling from report design, initial quality assurance could be done directly in SQL query, prior to freezing it in the Report itself (where redesign incurs additional consulting hours and customer concerns about your experience)
4. SQL Querying and Data Repair. This is typically high pilotage and it you think about something like data repair in Corporate ERP application, first consider consultant to do the job. If you consider yourself as Great Plains consultant in learning curve, be sure to stick to safety rule. First one – always make table backup, where we recommend you to do it via Select Into Statement. Let’s assume that you would like to do customer master table fix. Issue the following statement in MS SQL Server Management Studio against company database: Select * Into RM00101_2 from RM00101. Then, if you screw up customer master table, have all the users log off this company, drop DEX_ROW_ID column from your RM00101_2 table, delete all the records from RM00101 and use the following statement to repopulate it: Insert into RM00101 select * from RM00101_2. Good practice also suggests you create production company backup, create test company in GP Utilities and reload its data from production company backup. Then try your data repair on the copy, not production database
5. If you are on old Great Plains version. In this case table structure info is available in user interface; simply follow Tools -> Resource Description -> Tables. If you need to create Crystal report for Pervasive SQL 2000 or Ctree database, you need to install respective ODBC driver (Pervasive SQL 2000/Btrieve requires DDF files, they could be generated – see your version user manuals). Old versions, no longer supported by MBS on Pervasive were 7.5, 7.0, 6.0, 5.5, 5.0, 4.0, 3.2. Natural tool for data repair on Btrieve/Pervasive or Ctree is Microsoft Access, where you are using ODBC DSN linked table constructions
6. Great Plains Accounting for DOS, Windows or Mac table structure. Please, note, that this is not the same platform as Great Plains Dynamics. GPA was its predecessor, but if fact it is Btrieve hosted application, where table names are self-explanatory. You will need DDF files to connect to GPA database, there are instructions to create them in ODBC related manuals for GPA. Supporting old GPA customers we found that it is difficult to create ODBC connection via phone conference (we serve USA nationwide), the better idea is to upload your GPS folder to our ftp and then we will extract or repair your tables
7. Please call us 1-866-304-3265, 1-269-605-4904. help@efaru.com We have local presence in Chicagoland, Southern California, West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian, Chinese
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
