Getting Table and Field Information in Microsoft Great Plains

Getting Table and Field Information in Microsoft Great Plains
=============================================================

To get information about tables and fields in Microsoft Business SolutionsùGreat Plains, you can use any of the following methods:

1. Open the Great Plains window which contains the data you are interested in, and then select Tools -> Integrate -> Table Import to see the tables associated with the Dexterity Form.

2. Open the Great Plains window which contains the data you are interested in, and then select Tools -> Customise -> Customise Current Window. Once youÆre 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 (these will be the same as shown in method 1).

Note: Using this method can create additional windows in the Modifier that have not actually been modified. You should check if the window already exists in the Modifier before using this method, so then you know whether the window can be deleted or not.

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

SQLLogSQLStmt=TRUE
SQLLogODBCMessages=TRUE
SQLLogAllODBCMessages=TRUE

Then delete the DEXSQL. LOG just before performing the actions you are interested in and look at the DEXSQL.LOG file immediately after.

4. Load the SDK (Software Developers Kit). The kit contains transaction flow documents that show which tables to use for specific transactions. It also contains PDF E-R (Entity-Relationship) diagrams that 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 such as SnapShot (available from the Development page of Winthrop Dexterity Consultants <link to http://www.winthropdc.com/ > ). This tool can display and export information about tables. However, its main function is to provide platform and account framework independent data transfer, backup, and migration.

7. Load Dexterity (from the Tools folder on the second Microsoft Great Plains CD), open the dictionary, and look at the form definition to get the attached tables. This will be the same list as provided in methods 1 or 2.

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

Note: Using this method can create additional reports in the Report Writer that have not actually been modified. You should check if the report already exists in the Report Writer before using this method, so then you know whether the window can be deleted or not.

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

ScriptDebugger=TRUE
ScriptDebuggerProduct=0

The zero represents the product ID for Microsoft Great Plains (as shown in the DYNAMICS.SET launch file). If you are interested in another product, you can use the product ID for that product.

Launch Microsoft Great Plains. You should now see a Debug menu on the right side of the menu bar. Get to where you want to start logging and profiling. Select Debug -> Profile Scripts, Debug -> Clear Profile, Debug -> Log Scripts and select a filename. Then perform the actions you want to log. Next 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 and hierarchy. The script profile shows you the scripts called, how many times they were 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: This step only logs Dexterity based table actions. If a stored procedure is called, Dexterity cannot see what is happening. Therefore, Dexterity will not log those table actions.

10. You could also turn on SQL activity tracking from Enterprise Manager to see what actions Microsoft SQL Server is doing.


David Musgrave [MSFT]
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions
http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.