Incorrect data being reported

[lous]

I have a report that I'm trying to write with Crystal 7 that needs to look at a history file of shipments and compare them to a database of order numbers. If the shipments file entry does not match an order number, it should be listed on the report.

I can't get it to work and was hoping someone could help. Here's how it's currently set up.

The shipment numbers are in a FoxPro table and sometimes differ from the order numbers in that if there is more than one box, it will be followed by ".01, .02, etc." to indicate the box number. So I have trimmed those off by using {history.pkgid}[1 to 8] (so that the length matches a usual order number of 8 numbers).

The order numbers are in a Btrieve database.

The selection formula is {history.pkgid}[1 to 8]<>{orders.orderno}

The report lists nothing. There are many packages that don't have an actual order number, but this report just isn't finding them. What am I doing wrong?

Any help appreciated!!

 

[Chasidy]

The first thing I always check when my report comes up blank is the record selection. First I put the fields that I am using in the record selection in the details section of the report (temporarily) and then remove everything in the record selection. I run the report and make sure something &quot;shows up&quot;. Usually by doing that I see the fault in my logic in the record selection.
Hopefully that will work for you.

 

[lyanch]

The only way that I have gotten this type of report to work in crystal is to add a subquery to the WHERE statement.

Create your report showing all the fields etc from the history table. Then add to the Where statement in database -> Show SQL at then end of the statement


AND not substring(history.pkgid, 1, 8) in (select orders.orderno from orders where orders.orderno = leftsubstring(history.pkgid, 1, 8))

You may need to change the syntax to match your DB's syntax.

Lisa

 

[lyanch]

I forgot to add DON'T includ the orders table in the report (just the SQL you append to the SQL)

Lisa