Great plains data structure help...

[Ahssan]

hi all,

does any body give me the actual names of the tables in GP for following:

customers table
invoices table
products table

i would appreciate alot in this regard.

thanks

 

[PTina]

Go to Tools --> Resource Descriptions --> Tables

Click on the icon with 3 dots.
Change the Series to Sales - here you can see all tables relating to the Sales Module (e.g. Customer Master - RM Customer MSTR, RM00101).

Change the Series to Inventory to get the Inventory tables (e.g. Item Master - IV00101)

 

[Ahssan]

thanks ptina,

i will do that and check . since u are in to GP i assume from a long time i would like to put one more query. how is the invoice table related to products or inventory table i mean u have any idea what key is unique between both table to link them.

 

[celeron895]

I would suggest downloading the Software Development Kit (SDK). It contains the information you are looking for and is in a better format then the Resource Descriptions within Great Plains.

 

[Luvsql]

If you're looking at the SOP invoices, the SOP10200 to the IV00101 on the key ITEMNMBR and the SOP30300 (History) to the IV00101 on the key ITEMNMBR. The invoicing tables are the same, but IVC10101 and IVC30102.

 

[Dukester0122]

You only need to use 1 table - IV30300 which already shows the Invoice No and Item No.

 

[Ahssan]

thanks duke

well i can see the table though not every thing is clear, well im not a GP developer or a user i just need to integrate the GP with some other application. through GP i have seen that when some order has to be invoiced it has to be first selected from the sales order transaction form the invoice details are saved there . i want add an entry as a record in to it from some other application so that i will alert the finance dept that there is a order to be invoiced and they can open the pending orders to be invoice window and select the order to be invoiced. where does that entry has to be stored i cant get it. what table is and in a invoice there are lot of products how they are related to an invoice and saved in which table. Im totally lostttttttttttt man really. SOME HELP WILL BE GREAT>

 

[winthropdc]

The SDK (Software Developers Kit) Transaction Flows documentation will answer almost all of your questions. It is available from CD 2.

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.

 

[barbola]

If you want invoices that have yet to be posted, you can use the WORK tables which in this case would be IV10300 instead of IV30300.

Thanks!
Barb E.

 

[winthropdc]

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.

 

[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.