Adding a new selection criteria loses all my data. 1

[NBVC]

I have a Crystal Report that was working fine until I added one select statement

Here is the Sql before:

 SELECT "PURC_ORDER_LINE"."PART_ID", "PURCHASE_ORDER"."BUYER", "PART"."PRODUCT_CODE", "PURC_ORDER_LINE"."MFG_NAME", "PURC_ORDER_LINE"."MFG_PART_ID", "PART"."DESCRIPTION", "VENDOR"."NAME", "PURC_ORDER_LINE"."LINE_NO", "PURC_ORDER_LINE"."TOTAL_RECEIVED_QTY", "PURC_ORDER_LINE"."DESIRED_RECV_DATE", "PURCHASE_ORDER"."VENDOR_ID", "PURC_ORDER_LINE"."PURC_ORDER_ID", "PURC_ORDER_LINE"."WAREHOUSE_ID", "PURC_ORDER_LINE"."ORDER_QTY", "PURC_ORDER_LINE"."USER_10", "PART"."PRIMARY_LOC_ID", "VENDOR"."CONTACT_FIRST_NAME", "VENDOR"."CONTACT_PHONE", "VENDOR"."CONTACT_FAX", "PURCHASE_ORDER"."STATUS", "PURCHASE_ORDER"."DESIRED_RECV_DATE"

 FROM   "SYSADM"."PURC_ORDER_LINE" "PURC_ORDER_LINE", "SYSADM"."PURCHASE_ORDER" "PURCHASE_ORDER", "SYSADM"."PART" "PART", "SYSADM"."VENDOR" "VENDOR"

 WHERE  ("PURC_ORDER_LINE"."PURC_ORDER_ID" (+)="PURCHASE_ORDER"."ID") AND ("PURC_ORDER_LINE"."PART_ID"="PART"."ID" (+)) AND ("PURCHASE_ORDER"."VENDOR_ID"="VENDOR"."ID") AND "PURCHASE_ORDER"."VENDOR_ID"='1017' AND  NOT ("PURCHASE_ORDER"."STATUS"='C' OR "PURCHASE_ORDER"."STATUS"='F' OR "PURCHASE_ORDER"."STATUS"='X')

and all I want to add is condition ..AND "PURC_ORDER_LINE"."USER_9" <> 'C' through the Select Expert...

I now have this resulting WHERE clause in my SQL:

 WHERE  ("PURC_ORDER_LINE"."PURC_ORDER_ID" (+)="PURCHASE_ORDER"."ID") AND ("PURC_ORDER_LINE"."PART_ID"="PART"."ID" (+)) AND ("PURCHASE_ORDER"."VENDOR_ID"="VENDOR"."ID") AND "PURCHASE_ORDER"."VENDOR_ID"='1017' AND  NOT ("PURCHASE_ORDER"."STATUS"='C' OR "PURCHASE_ORDER"."STATUS"='F' OR "PURCHASE_ORDER"."STATUS"='X') AND "PURC_ORDER_LINE"."USER_9"<>'C'

and it is now returning no results, where I know there should be plenty

 

[lbass]

I can't remember for sure how to interpret the (+), but I think you have left outer join the purc_order_line table to the other table. If you then add a selection criteria on the purc_order_line table, you are effectively undoing the left outer join. This also suggests that there are no records that meet your criterion. Try removing the selection and placing the UserID in the detail section and then observe whether any records have a value other than "C".

If the field can ONLY have a value "C" or be null, then by saying <> "C", you are ruling out all records, since you didn't check for nulls. However, if you want to see all records from the Purchase Order table, you should not add a null check or a check for C on this field. Instead, create a formula that you place in the body of the report:

if isnull({table.field}) or
{table.field} <> "C" then
{table.field}

-LB

 

[NBVC]

Thanks LB,

The field "may" have other info in it... I just want the lines that have a "C" on its own not to appear in the report...

so as long as the field is either null or has anything but a single "C" in it, it should appear.

Also, I am not sure what you mean by create a formula that you place in the body of the report. Do you mean create a Formula Field and then suppress the lines with "C" in USER_9?

 

[lbass]

You could lose Purchase Orders if you suppress rows with C in it--if that is the only value a Purchase Order has for that field. What I was suggesting is that you create the formula in the field explorer and use that instead of the field in the body of the report.

-LB

 

[NBVC]

I am still not sure how to implement that.

So I go to Field Explore and create a formula called "Closed" and enter formula:

if isnull({PURC_ORDER_LINE.USER_9}) or
{PURC_ORDER_LINE.USER_9} <> "C" then
{PURC_ORDER_LINE.USER_9}

What do I do next to finish?

 

[lbass]

I meant for you to place it on the report instead of the field itself, but I guess I'm not following your goal here.

Please explain--does "C" mean "Closed" and you don't want to see any closed records? Is it possible that the Purc_Order_line = "C", but you still want to see the Purchase Order?

Please show some sample data and explain what you are trying to do here.

-LB

 

[NBVC]

Yes "C" means closed.

It is our way of indicating that we want to close off a line without actually closing the entire purchase order.

I am updating an expediting report to show the vendors what items are still outstanding to be received.

If we want to stop receiving one of the lines (even if there is a backorder), we will put a "C" in that USER_9 field. Other lines will not have the "C" and so we still expect receipts on those.

So I will want to see purchase orders that have multiple lines, but exclude any with a "C" in the USER_9 fields.

 

[lbass]

If you don't care about losing some Purchase Orders (those whose lines ONLY contain 'C'), then add the following to your selection formula:

(
isnull({table.field}) or
{table.field} <> "C"
)

-LB

 

[NBVC]

Thanks LB,

That seems to do the trick.

It was just a bit confusing as you would think that null is not "C" so it should display....

Thanks again for your time.