Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : Crystal with table joinsSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC Frequently Asked Questions

 New Topic 
 
 Post Reply 
[1]  
 Crystal with table joins 
 Author   Message 
  jdc 
  
 Group: Members 
 Posts: 4 
 Joined: 2009-02-06 
 Profile
 Posted : 2009-02-19 07:52:47

I am currently using QODBC 9.00.00.253 with Crystal Professional XI Release 2. I am very familiar with Crystal and table linking but am having a problem with QBE V9.

 

I am using the PO line table and need to get additional item information so I tried a left outer join from the PO line table to the Item table on the PurchaseOrderLine.PurchaseOrderLineItemRefListID = Item.ListID. I also tried an equal join (which I really fo not want).

 

I am not getting any matches. Can you please tell me what I am doing wrong? Thanks so much!

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2009-02-19 10:54:37
Make sure auto-linking is off, otherwise Crystal Reports will link any columns matches it can find and you will never get any data. 

  Top 
  jdc 
  
 Group: Members 
 Posts: 4 
 Joined: 2009-02-06 
 Profile
 Posted : 2009-02-19 11:09:03
Thanks Tom -  I cleared all links when I pulled in the tables and then created my own link and still no data. Any other suggestions? 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2009-02-19 17:08:02

Not really, you haven't even said what SQL Statement Crystal Reports generated. This works using VB Demo:

SELECT PurchaseOrderLine.PurchaseOrderLineCustomerRefFullName as Type,
PurchaseOrderLine.TxnDate as Date,  PurchaseOrderLine.DueDate as "Deliv Date",
PurchaseOrderLine.RefNumber as "Num", PurchaseOrderLine.VendorRefFullName as SourceName,
PurchaseOrderLine.PurchaseOrderLineItemRefListID as ItemCode,
PurchaseOrderLine.PurchaseOrderLineItemRefFullName as Item,
PurchaseOrderLine.PurchaseOrderLineQuantity as Qty,
PurchaseOrderLine.PurchaseOrderLineReceivedQuantity as Rcvd,
PurchaseOrderLine.PurchaseOrderLineRate as Cost,
PurchaseOrderLine.TotalAmount as Amount,
Item.SalesPrice as SalesPrice
FROM PurchaseOrderLine NOSYNC
LEFT OUTER JOIN Item NOSYNC ON PurchaseOrderLine.PurchaseOrderLineItemRefListID
=Item.ListID

 

  Top 
  jdc 
  
 Group: Members 
 Posts: 4 
 Joined: 2009-02-06 
 Profile
 Posted : 2009-02-20 00:58:28

Sorry Tom. Here is the code generated by Crystal:

 SELECT "PurchaseOrderLine"."VendorRefFullName", "PurchaseOrderLine"."TxnDate", "PurchaseOrderLine"."PurchaseOrderLineItemRefListID", "PurchaseOrderLine"."PurchaseOrderLineQuantity", "Item"."PurchaseCost", "PurchaseOrderLine"."DueDate", "PurchaseOrderLine"."PurchaseOrderLineReceivedQuantity", "PurchaseOrderLine"."PurchaseOrderLineAmount", "PurchaseOrderLine"."PurchaseOrderLineRate", "PurchaseOrderLine"."RefNumber"
 FROM   "PurchaseOrderLine" "PurchaseOrderLine" LEFT OUTER JOIN "Item" "Item" ON "PurchaseOrderLine"."PurchaseOrderLineItemRefListID"="Item"."ListID"
 ORDER BY "PurchaseOrderLine"."VendorRefFullName", "PurchaseOrderLine"."RefNumber"


BTW, I am getting data, but nothing from the Item table. I am also receiving no error message. Thanks Tom!

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2009-02-20 09:59:58

Run a report on just the ITEM table. If you get no results, rebuild the optimized ITEM table by doing:

SELECT * from ITEM verify

 

  Top 
  jdc 
  
 Group: Members 
 Posts: 4 
 Joined: 2009-02-06 
 Profile
 Posted : 2009-02-20 11:30:14

I ran the query:

 SELECT * from ITEM verify

and it did not change the results. I then played around with the Crystal report and if I remove all of the SORTS and GROUP BY information, the tables do in fact join successfully and I get the results I am looking for. If I just add a SORT on one field, the join does NOT work. Also, if I try to GROUP BY one field, the join foes NOT work.

I am really puzzled here because in the format it is in, it is not acceptable. Could this issue be caused by the fact that I am running Crystal XI Release 2?

Thanks Tom!

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2009-02-20 13:12:43

Sorry, I don't have any experience with Release 2. The other databases I work with don't work with Release 2, so I've had to stay away from it myself. But that doesn't matter. Just use the SQL using Add Command instead.

Creating a Report with SQL or an existing QuickBooks Report
For sp_reports (see QODBC sp_report) 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.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to