SQL Tables structures 5

[hamzajosh]

I need to know where great plains stores all the data, i mean in which tables. Let's say i am looking at a window "item maintenance options" and i want to know which table great plains stores information on this screen, how do i go about finding that out. Somebody told me to look for SDK on CD2. Couldn't find anything but DSDK which is just "what's new in V6.0 from 5.5". I am working with V6.0 currently. Any help would be appreciated.

 

[triciata]

Hi,

In Dynamics go to Tools->Resouce Descriptions ->Tables click on the elicpse button to select the module you would like to view.

Tricia

 

[hamzajosh]

i see it gives me a whole list of tables but still i can't find which window stores in which table. how do i find out specifically? Just a list is no good, i am a newbie to great plains as such though not to computers and databases.

 

[triciata]

You can sort it by Table Display Name to get your the information and use the table name in query analyzer to perform the select statement.

 

[hamzajosh]

Tricia, i don't think you got my question. I want to know which window stores in which table, I need the relation between a window name and a table name. thanks

 

[Dukester0122]

Here's a couple of tips that will help you. A window in GP means 1 or more windows in PSQL, Btrieve or SQL. Example, the Sales Transaction Entry window has 2 tables in SQL, SOP10100 and SOP10200. SOP10100 is the header while SOP10200 are the line amounts.

The best way to start learning the table structure of GP which I have recommended to many is to print the Table Structure which Tricia already told you how(try doing the Table Physical Name instead of Display Name) then go to GP and try doing a process entry from Order to Financial posting. Try to open the tables and see what effect does order entry or posting does to which tables. I assure you this is the fastest way to learn the table structure.

I've been implementing GP for more than 8 years now and I still have problems looking for the right table. Goodluck.

 

[hamzajosh]

Duke, thanks for the tips but frankly speaking that's grim news you have given me. I expected to find some documentation where everything would be available. Thanks again for the help.

 

[hooggie]

Hi If you are interested in finding the key to the table structure check out the posting dated 10/17/02 It's quite a long way back but has a pretty detailed explanation to the tables

 

[anifong]

With the window you're interested in open, try going to Tools, Integrate, and choose Table Import if it's available. That will give you a list of what tables are used by this window. The table import used to be available only if you owned Integration Manager, but I just tried this at a client without it and it was available.

 

[hamzajosh]

i don't have the options enabled in integration manager, although i have purchased integration manager. From where and how do i install integration manager and what do i need to do? Thanks

 

[PTina]

Open the window in question; make note of the window name in the Top Left Hand Corner of the Window (in this case Item Maintenance).

Then go to Tools --> Resource Descriptions --> Windows.

Change the Series to Inventory then scroll down until you locate Item Maintenance and Click on it. Look into the box labelled Tables: at the bottom right of the window. Here you will see a list of all the tables that are accessed by this window.

 

[rutvij]

You may also run this query to find out which tables this field gets updated into.

SELECT *
FROM YOURDBNAME.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NAME OF THE COLUMN'

THIS WILL GIVE YOU THE LIST OF ALL THE TABLES THIS COLUMN NAME IS IN. LET ME KNOW IF YOU HAVE ANY QUESTIONS.

EMAIL: RSHAH@ATLANTIC-INC.COM

 

[JonCross]

You can turn on a log of Dynamics SQL actions, do something in the area of interest in Dynamics then look at the log to see which tables have been accessed.

- Find file DEX.INI in you Dynamics directory
- Find the lines:
SQLLogSQLStmt=FALSE
SQLLogODBCMessages=FALSE
and edit them to TRUE
- restart Dynamics and navigate to your area of interest
- In your dynamics directory delete DEXSQL.LOG (it will be quite big by now !)
- Change a field in the form you are interested in, then save the change.
- Look at the DEXSQL.LOG file
- BINGO !!

Make sure you edite the DEX.INI file entries back to FALSE else you will end up with a giant log file.

 

[hamzajosh]

JonCross, Wow that's great but it's too much information, do I need to go and see every stored procedure which tables it accesses/changes? Just logging in creates like 100's of line in the log file. Opening a window does the same, how do i interpret all that info? Pls advise further

 

[winthropdc]

OK, here is a summary of your options.

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@microsoft.com

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

 

[hamzajosh]

Wow, David, that was awesome information. I will help me a lot. Just a few things,

1) My table import is grayed out even though I have purchased and installed integration manager.

2) I installed the SDK (a lot of people have advised me to use it) from CD2 of great plains. I do not see any of the things mentioned by you or others like the relationship diagrams and all. All i see is some useless stuff although i selected full install. Any advise on how to install or what i am doing wrong will be great.

 

[winthropdc]

Table Import may be disabled via security.

From the v7.50 SDK, Look at Great Plains >> Table Integration and at ERwin Models.

David