GP 2010 - Sales Transactions -Reports

I am trying to build a report for an end user and do not know where to pull the fields from.
The physicians name is in the physician field in the "user defined" field.  The case name is in the "notes" field.  I need some help in determining where these fields are in SQL.

Please see attached screenshoot
GP2010-Case.pdf
pstreAsked:
Who is Participating?
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.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
For the physicians name, do you mean it's on the window that opens when you click User-Defined on the Sales Transaction Entry window? If so, those will all be in the SOP10106 table (for both posted and unposted transactions). Which field to use will depend on which one you have renamed to be "physician".

For the notes - these are stored in the SY03900 table. You can link to it from the SOP10100 table for unposted transactions (or SOP30200 table for posted transactions) on the NOTEINDX. So for example:

select s.SOPNUMBE, s.SOPTYPE, n.TXTFIELD 
from SOP10100 s
left outer join SY03900 n
on s.NOTEINDX = n.NOTEINDX

Open in new window

0
pstreAuthor Commented:
Great...thanks..  How would I link in the SOP10106 table in the above query?  Also, what SOp table would I use to run a report for billings - this means any invoice whether paid or not.

Thanks!
0
Victoria YudinOwner / Dynamics GP ConsultantCommented:
SOP10106 can be linked to other SOP tables by SOPTYPE and SOPNUMBE. So, for example:

select s.SOPTYPE, s.SOPNUMBE, u.*
from SOP10100 s
left outer join SOP10106 u
on s.SOPTYPE = u.SOPTYPE and s.SOPNUMBE = u.SOPNUMBE

Open in new window


When you say 'report for billings', that could mean so many different things, it's hard to suggest tables without more detail. What columns (or logic) are you looking for on this report?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

pstreAuthor Commented:
Thanks... Can a 3rd table (SY03900) be linked. this table has the noteindex that we need to pull data from.

Report for billings = All invoices (posted and unposted)
0
Victoria YudinOwner / Dynamics GP ConsultantCommented:
You can add as many tables as you want:

select s.SOPTYPE, s.SOPNUMBE,  n.TXTFIELD, u.*
from SOP10100 s
left outer join SOP10106 u
on s.SOPTYPE = u.SOPTYPE and s.SOPNUMBE = u.SOPNUMBE
left outer join SY03900 n
on s.NOTEINDX = n.NOTEINDX

Open in new window


For all invoices, take a look at my code for creating a view for all SOP line items:
http://victoriayudin.com/2009/05/17/sql-view-with-all-sop-line-items/ - that should give you a start.
0

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
pstreAuthor Commented:
Victoria is so knowledgable on GP and the tables AND SQL..  Much appreciated
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 Applications

From novice to tech pro — start learning today.