GP 2010 - Sales Transactions -Reports

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

Select allOpen in new window

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!

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

Select allOpen 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?

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)

Victoria is so knowledgable on GP and the tables AND SQL..  Much appreciated