Great Plains Querying 1

[WelshyWizard]

Hi all,

We have recently purchased Great Plains and I am looking to Query the database with Crystal Reports to get a list of Invoices on a certain contract (using AA for a contract number). Is this possible? If so, is there anywhere I can get a table schema from?

Cheers

Today is the tomorrow you worried about yesterday - and all is well.....

 

[Alcar]

There really isn't a table schema... consider over 1300 tables to map out
There is a tool out there that you can point to a SQL DB and it will create the schema for you.
I pointed it to my Company Database and it chugged for good 40 minutes.. I had to stop it.

The Best bet is to determine exactly what you need to report, look for similar fields in the table descriptions and try it out on the test company.

In the test company you could create an obvious Invoice and then search for all of its fields.

Off the top of my head, you will be working with most of the IVCxxxx tables and a couple of RMxxxx ones (for the customer information)

Last way to figure out where to find the fields is a little hacking way of doing it:
If you have a transaction log analizer you could use a particular user to enter one invoice and then look at the transaction log to review where all the inserts happened.


Hope this helps

Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[jymm]

ACTUALLY - check out the bottom of this thread.

http://www.tek-tips.com/viewthread.cfm?qid=1318127

There is a REALLY nice lay out for which table is which and such - and GP Dynamics does have the way to see it from within GP. I just use that info to point Crystal to the tables I need since there are so many that are not applicable for invoices (for example) in the SOP module.

 

[Alcar]

Nice find Jymm. I Like what I have found there. You better get a star just for that one

Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.