Identify Great Plains Tables

Need to be able to identify the tables in Great Plains 8.0 so that the name, address (street, city, state, zip code) and email address information can be extracted by Federal Express. We no longer have a GP expert on call (as we plan to move away from GP in the next 16 to 24 months) and the FedEx guy says he can see all the tables but he doesn't know which ones to use so you can write the required code to populate their shipping fields. Can anyone tell me how I can identify which tables are used for what purposes--thank you.
LVL 26
Lionel MMSmall Business IT ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve EndowMicrosoft MVP - Dynamics GPCommented:
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
Lionel MMSmall Business IT ConsultantAuthor Commented:
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!
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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'

Open in new window



Let me know if that helps.

Steve
Lionel MMSmall Business IT ConsultantAuthor Commented:
using the last query I get this error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SY04910'.
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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'

Open in new window

Lionel MMSmall Business IT ConsultantAuthor Commented:
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
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Looks like GP 8.0 didn't have the additional EmailToAddress field either.

Give this one a try.


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(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'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
Lionel MMSmall Business IT ConsultantAuthor Commented:
Great solution for Great Plains 8.0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.