Anyone, I'm creating a report in Crystal report.  Once similar to the Open POs by job report.  Does anyone know what tables are used, what fields are linking the tables, what fields are used for the report, and what criteria is used to know if a PO is still active or not.  Thanks, Marie 

See: How do I run a Open Purchase Orders by Job Report? for the SQL for the report and follow the instructions below:

Creating a Report with SQL or an existing QuickBooks Report
For sp_reports or manual SQL statements, Highlight “Add Command” and move the right panel.

The following window will appear. Type or paste your SQL statement or sp_report command as shown, then click “OK”

Your SQL statement is now entered; (to edit, just right click on “Command”). Click “OK”

The fields as specified in your SQL statement, are now available for selection in your report.

I have three levels of jobs, for ex:

Company

     Job number

            ship

Third level down doesn't show up under the type field.  Plus I need to group on each level and having all of them showing up in one field doesn't work.  Then the purchase orders are not linked at all to the 'type' field so when I do my report....the po's don't show up.

Please, is there any way I can get the tables and how they are linked and so forth?  The QODBC website documentation is not helping me :(

Marie

We can also extract the same information from the PurchaseOrderLine table directly like this:

SELECT PurchaseOrderLineCustomerRefFullName as Type, TxnDate as Date,  DueDate as "Deliv Date",
RefNumber as "Num", VendorRefFullName as SourceName,PurchaseOrderLineItemRefFullName as Item,
PurchaseOrderLineQuantity as Qty, PurchaseOrderLineReceivedQuantity as Rcvd,
PurchaseOrderLineRate as Cost, TotalAmount as Amount FROM PurchaseOrderLine where IsFullyReceived = 0
and PurchaseOrderLineCustomerRefFullName is not Null order by PurchaseOrderLineCustomerRefFullName

When I tried what you suggested below.  I let it hang for about 15 minutes and then canceled it.  So instead I opened table 'PurchaseOrderLine' and selected 'IsFullyReceived' = false, and 'CustomerRefFullName' is not "".

My results are as followed compared to the Quick Books "Open POs by Job" report:

1) Not all open POs are showing up in my report. 

2) Doesn't show down to third level (ex/ Ship, Instrumentation, Other). 

3) In Quick Books the total for one job is about 6 in all.  My results are coming out in pages worth of POs. 

Do you think maybe we should link to another table like, Entity?

PLEASE HELP!

Marie

No, I suggest you play around with the SQL you are using first in VB Demo before building your report in Crystal Reports. If you're happy with the report you see in QuickBooks, use sp_report to reproduce and a sub-report in Crystal Reports to expand it (drill down on a line).

See: How do I run a SP_REPORT Command? for more on all the options.