Share
ReportDoes anyone have experience pulling Great Plains data using SQL? Any helpful resources or documentation on how to get started would be much appreciated.
Categories:
You’re offline. This is a read only version of the page.
5
Does anyone have experience pulling Great Plains data using SQL? Any helpful resources or documentation on how to get started would be much appreciated.
Aside from the generic views already provided by Microsoft, you can use the online resources on AzureCurve to identify the GP tables by their physical name and try to understand what their purpose is (and get fields names too).
The site has also tons of other useful resources BTW..
I'll second Beat's recommendation of using Victoria Yudin's table guides on her blog. They are the BEST.
Something else you can do in SQL Server Management Studio is connect to your GP company database, expand Views, and then look for all the views that have a nice name to them:
Accounts
AccountTransactions
PayablesTransactions
PurchaseOrders
And on and on. These are the views created by Microsoft for SmartList, but you can also use them to do other reporting too. They have easy-to-read column names and easier-to-read data (example: instead of a number for a document type, the actual words will show instead) so you know what you're looking at. These are a good start too.
I hope this helps you out.
Joe
If you want to start somewhere (aside of the suggested SDK & GPPT), you should have a look at Victoria Yudin's blog site, which holds a wealth of SQL code and GP table references.
Then there is also the GPUG community (paid membership) which you can browse for free during a 30 days trial membership.. Tons of useful infos and SQL scripts too. http://www.gpug.com
As Richard mentioned, the SDK can help you understand the data model. It has E-R diagrams.
Also there is a resource descriptions window in GP which provides data about tables, etc.
Finally, I would look at GP Power Tools (http://WinthropDC.com/GPPT) which has a very powerful Resource Information and Resource Finder window which can identify exactly where data is stored in the databases from the window fields.
Regards
David
I would start by installing the GP SDK which is located in your GP install media. This will get you started.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (