Identify Great Plains Tables

Hi Lionel,

To get started, I recommend reading Leslie Vail's excellent tutorial on the Dynamics GP table naming convention.  You'll be a near-expert on GP table names in just a few minutes:

https://dynamicsconfessions.blogspot.com/2010/07/dynamics-gp-table-names.html

If you want more info on a given table, you can google "GP table _______", i.e. "GP table SOP10200".  There are a few sites that have more info on specific tables.


Leslie's tutorial will help with deciphering the table names, but a bit more discussion is required to figure out which tables you'll want to query for the contact data you describe.

Do you want address data associated with customer records? (master records)  That will be in the RM00101 (Customer Master) and RM00102 (Customer Address) tables.  If you only need one main address per customer, RM00101 should work.  If you have customers with multiple Ship To addressses, you may need RM00102 data.

Or do you want the ship to address information from orders?  If so, I suspect you'll need to get the order address data from the SOP10200 table.

Let me know if that helps and if you have any additional questions.

Thanks,

Steve Endow
Microsoft MVP - Dynamics GP

Hi Steve--been awhile--hope you are well. Link definitely did help lift the fog. Makes so much more sense now--no longer Greek to me, well not as much as before. However is there a simple query I could run on a table to for instance show the contents on the table, maybe just one or two records from the table so that I know I have the right one? Thanks!

Hi Lionel,

Here are two simple ones to start with:

SELECT TOP 100 * FROM RM00101

SELECT TOP 100 * FROM SOP10200


If you have more information on what exactly you need to send to FedEx, I can potentially narrow it down for you.

Steve

The Sales Customer Detail Entry Form (see attached) has the name, address, and shipping method information on it that we need to pull into FedEx--what we are struggling to figure out is where each field is stored, what table. When I use the search function on that form it shows a bunch of tables that it is linked. basically they need Name, Company Name, address, city state zip shipping method phone number and email address for each order--since each order can have a different shipping address we need the one that is unique to each order.

Hi Lionel,

Here is a simple version of a query that pulls the Ship To Address information from the order header.  There are several possible caveats for this query though.

1. It assumes that the orders have "shipping address overrides", meaning that the actual customer shipping address / contact info is correct at the order level. (which sounds like it is the case, based on your last response)  You can test it and verify that it is querying the correct address data.

2. There is technically a way to associate an email address with a specific order, but the window is a bit obscure, so you'll have to let me know if that is where the customer is storing emails for their orders.  The window is called Sales Email Detail Entry, and is the small envelope icon next to the Customer ID field on the order window.  For reference, I've also added a join to the main customer email address (which you probably do not want), as well as the SOP User Defined fields, which are another place you could potentially store an order email address value.

The query has a filter for a sample 'WEB1000' order number--just change that value to a real order number in your environment.

SELECT sh.SOPTYPE, sh.SOPNUMBE, sh.DOCID, sh.DOCDATE, sh.ReqShipDate, sh.CUSTNMBR, sh.CUSTNAME, sh.PRSTADCD, sh.ShipToName, 
sh.CNTCPRSN, sh.ADDRESS1, sh.ADDRESS2, sh.ADDRESS3, sh.CITY, sh.STATE, sh.ZIPCODE, sh.COUNTRY, sh.PHNUMBR1, sh.PHNUMBR2, sh.SHIPMTHD, 
ISNULL(emd.EmailToAddress, '') AS OrderEmailAddress, 
ISNULL(em.INET1, '') AS CustEmail1, ISNULL(em.EmailToAddress, '') AS CustEmailToAddress, ISNULL(udf.USERDEF1, '') AS OrderUserDef1
FROM SOP10100 sh --SOP Order / Invoice Header table
LEFT OUTER JOIN SY04910 emd ON emd.DOCTYPE = sh.SOPTYPE AND emd.DOCNUMBR = sh.SOPNUMBE AND emd.Master_ID = sh.CUSTNMBR --Email Details table, order specific email
LEFT OUTER JOIN SY01200 em ON em.Master_Type = 'CUS' AND em.Master_ID = sh.CUSTNMBR AND em.ADRSCODE = sh.PRSTADCD --INET INFO table where customer Internet info is stored
LEFT OUTER JOIN SOP10106 udf ON udf.SOPTYPE = sh.SOPTYPE AND udf.SOPNUMBE = sh.SOPNUMBE --SOP user defined fields
WHERE sh.SOPNUMBE = 'WEB1000'

Select allOpen in new window

Let me know if that helps.

Steve

using the last query I get this error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SY04910'.

Ahh, sorry, I forgot to double check the GP version.  I believe that table was added in GP 2010, so it won't be available in GP 8.0.

That means that an email address would need to be stored somewhere else on the order.  If you know which window+field it is stored in, let me know and I'll add it to the query.


Removing that table:

SELECT sh.SOPTYPE, sh.SOPNUMBE, sh.DOCID, sh.DOCDATE, sh.ReqShipDate, sh.CUSTNMBR, sh.CUSTNAME, sh.PRSTADCD, sh.ShipToName, 
sh.CNTCPRSN, sh.ADDRESS1, sh.ADDRESS2, sh.ADDRESS3, sh.CITY, sh.STATE, sh.ZIPCODE, sh.COUNTRY, sh.PHNUMBR1, sh.PHNUMBR2, sh.SHIPMTHD, 
ISNULL(em.INET1, '') AS CustEmail1, ISNULL(em.EmailToAddress, '') AS CustEmailToAddress, ISNULL(udf.USERDEF1, '') AS OrderUserDef1
FROM SOP10100 sh --SOP Order / Invoice Header table
LEFT OUTER JOIN SY01200 em ON em.Master_Type = 'CUS' AND em.Master_ID = sh.CUSTNMBR AND em.ADRSCODE = sh.PRSTADCD --INET INFO table where customer Internet info is stored
LEFT OUTER JOIN SOP10106 udf ON udf.SOPTYPE = sh.SOPTYPE AND udf.SOPNUMBE = sh.SOPNUMBE --SOP user defined fields
WHERE sh.SOPNUMBE = 'WEB1000'

Select allOpen in new window

Does this screenshot help you? Hope so. Get this error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'EmailToAddress'.
email-form.jpg

Works--thanks Steve, so if I understand all this you are getting all this information from table SOP10100, right? Thanks so much.
2      FLC62943                   3                    2018-02-01 00:00:00.000      2018-04-17 00:00:00.000      27262ILD101          NANCY                                                         MAIN                 NANCY                                                          NANCY                        1010  CLUB DR                                                                                           HIGH POINT                           NC                                 27262-2808       US                                      UPS                  Nancy@NORTHSTATE.NET; NANCY@GMAIL.COM

The main address information is from the SOP Order Header table, SOP10100.  So that is where the order info and customer info is coming from.

But the email is coming from Internet Information table, SY01200, which only has customer-specific email--not an order-specific email address.

In case you need it, I included a reference to the SOP User Defined Fields table, SOP10106.

Please let me know if you have any questions or need any additional information.

Steve

no that's it thanks Steve--will be in touch soon about the SQL import we spoke of several months ago--just way too busy for that now but hope to address that soon--appreciate your time.

Great solution for Great Plains 8.0