Documentation on what is in the tables 12

[JZajda]

I am attempting to develop some external reporting needs for our finance department. They need to be able to group accounts into different Goals and Objectives and report on them, (Budget, Dollars spent, Dollars generated, etc...).

I have set up in Access XP the tables to group the accounts and the ODBC Connection to SQL 7.0, my problem is that I have no idea which tables contain the account definition, budget, expense or revenue information. I have never seen such a poorly named mess of tables.

If anyone could point me in the right direction it would be greatly appreciated.

 

[cmcgarel]

I second this post. Where is the technical documentation for Great Plains? My integration project has been very painful to plug-in to GP and full of trial and error and inferrence.
Some human-readable descriptions of each table and field in a PDF would make all the difference.

 

[hooggie]

If you install the software developer kit which is on the great plains CD there is some table documentation in there.

As for the table naming it is a pretty simple code that makes it easy to find what you are looking for in almost all instances instead of scolling through thousands of names looking for a table that might be called a number of different things

Here is how it works. A great plains table has a structure of AANNNNN 2 or 3 Alpha characters followed by 5 numeric. There are some exceptions to this especially in the case of 3rd party products and newer tables designed for use in SQL.

The Alpha part will be GL or IV or RM or PM or CM or SOP or SY ... You get the idea

These tell you what series you are looking at:-
GL = General Ledger
RM = Receivables Management (Accounts Receivable)
PM = Payables Management (Accounts Payable)
SOP = Sales Order Processing
SY = System (Non ledger specific items e.g. payment terms and tax data)

the first numeric digit tells you the following:-
0 = Cards
1 = Work
2 = Open
3 = History
4+ setup and other tables infrequently needed

The 2nd and 3rd digit break down the areas in the series and status that you are looking at.

Eg. If you were looking at Work on the Payables series (PM1) 01 might be the transaction header 02 might be the tax 03 might be the distributions (I am not looking at the system I am making these examples up)

the last 2 numerics are just an index if there were two tables needed in the payables series work tranaction header (PM101) the first would be 00 the second would be 01

The last piece of advice is a list of all the tables sorted by series can be found by going to tool >> resource description >> tables and looking in there. I select the series I am looking for and look for and then sort the tables by physical name.

If you are looking for account definition that would be in the General Ledger under cards so you want a table starting GL0 (and as a freebie I will tell you this one is GL00100)
Budget is also cards so you will be looking for something starting GL0
Transaction information is going to be open in the general ledger if you are looking at data for the current year so you will be looking for something starting GL2

Once you have the first 3 digits you will find that there is rarely more than 5 or so tables to look at and reading the names is no big chore.

As a final help though I will tell you that when working with the accounts file that you will only find the account number in that table. In 99% of other tables you will only find an index number. ACTINDX or DISTINDX which links back to the ACTINDX in the GL00100

 

[markajem]

Thanks for the assistance here Hooggie. I am also creating some reports using Crystal and find it most difficult to find the data I need. I was brought in on this project knowing how to use Crystal but not knowing the structure of the database and never before using Great Plains. WOW what a learning curve on this one. I am used to using the supplied table and field definitions so I know exactly what I am looking at without guessing at everything and so much trial and mainly error. I appreciate the heads up on the naming conventions for the tables but another problem I am finding is knowing what the fields refere to. Some are basically self explanatory but some I have no clue because of the abbreviations they use. My biggest problem that I find is that if I find myself needing a fied who's value is a number it is difficult for me to assertain as to what the numbers refer to. IE - in the inventory master file. The itemtype field. Values are numbers. If I do a browse on the field I find that the numbers 1,3 and 5 have been used. But how do I know what other numbers are available to be used and what the values refernce. I would assume if 1, 3 and 5 are used I know 0, 1, 2, 3, 4 and 5 are avaiable. how do I know what other values are available and what they referfence. Value 3 could be discontinued while 1 could be selling inventory. This is only an example, don't have the table info in front of me so I don't know if Ihave the proper values, but just wanted to use this as an example on the post.

Thanks for any help you can give me. This project is a monster without knowing the data structure and the software. I have been locked out of all the cards and any data entry areas so it makes this difficult for me. Because if I find a field with values I have no clue what they reference.

Also would it be safe to assume that if a field has a value and the field in the screen has a drop down box with 4 selections that the values of that field is based on the order of the values showing in the drop down box.

Thanks
Mark

Mark
Email: markanas333@hotmail.com

 

[DougP]

Fantastic !!!!
Thanks a million DougP, MCP

 

[JunglesMcBeef]

hooggie, you're a bloody champion.

 

[winthropdc]

Here is my summary on how to get info about tables and fields.

1) Open the window, then select Tools >> Integrate >> Table Import to see the tables associated with the Dexterity Form.

2) Open the window, then select Tools >> Customise >> Customise Current Window. Once in Modifier Layout mode, look at the window object properties. Usually the most important table for a Form is linked as the AutoLink table. Then close the layout window and look at the Tables tab of the Form definition window to see the attached tables (same as the info in 1).

3) Try using SQL logging by adding the following lines into the DEX.INI:

SQLLogSQLStmt=TRUE
SQLLogODBCMessages=TRUE
SQLLogAllODBCMessages=TRUE

and look at the DEXSQL.LOG file after performing the actions you are interested in.

4) Load the SDK (Software Developers Kit), there are transaction flow documents which show what tables to use for specific transactions. Also there are PDF E-R (Entity - Relationship) diagrams which show the tables and their relationships.

5) Select Tools >> Resources >> Tables and use the Table Resource window to look up tables.

6) Use a tool like SnapShot (available from Development page of

http://www.winthropdc.com).

It can display and export information about tables. However, its main function to to provide platform and account framework data transfer/backup/migration.

7) Load Dexterity (on 2nd CD) and open the dictionary and look at the form definition to get the attached tables... same as the info in 1 and 2.

8) Open the window and print the associated report to the screen, select Tools >> Customise >> Modify Current Report. The look at the tables attached the report from the Report Definition.

9) This is probably the most powerful of all the methods listed and is normally only available to the Dexterity Developer. Add the following lines into the DEX.INI to turn Debug mode on (Please do not use for live systems):

ScriptDebugger=TRUE
ScriptDebuggerProduct=0

The zero is the Product ID for Great Plains (as shown in the DYNAMICS.SET launch file.

Launch Great Plains and you should now have a Debug menu on the right hand side of the menu bar.

Get to where you want to start logging/profiling. Select Debug >> Profile Scripts, Debug >> Clear Profile, Debug >> Log Scripts and select a filename. Then perform the actions you want to log. Then select Debug >> Log Scripts to stop the logging, Debug >> Save Profile to save the profile and Debug >> Profile Scripts to turn off profiling.

Now look at the script log and the script profile files. The Script log shows all the Dexterity calls with their parameters & hierarchy and the Script Profile will show you the scripts called with how many times they are called and how much time was spent inside the call.

Here is the trick.... The bottom half of the Script Profile shows all the tables that were touched and what actions took place.

NOTE: that this only logs Dexterity based table actions, if a stored proc is called Dexterity cannot see what is happening and so will not log those table actions.

10) I suppose you could also turn on SQL activity tracking from Enterprise Manager to see what actions SQL Server is doing.

Hope this helps.

David Musgrave
Senior Development Consultant
Asia Pacific Professional Services

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

mailto:dmusgrav@nospam-microsoft.com

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.