Joining With Left Outer, Selection On Second Table

[angel00079]

Version : Crystal 11

Data source : JD Edwards

I am trying to create a report that shows a certain type of item and if has any orders that have not been shipped. I have used an left inner join on the part number. There are some items that have orders against them but these are shipped so I would not want to show the orders. If I exclude orders by status I could lose the part on the report. I have tried to create a formula against order number. If there is no order number because they is no order in the sales the else part of the formula does not set to “N”. If there is an order number it sets it to “Y”.

Is there a way to set fields that do not appear in the second table to a value. My thinking was that if I grouped by part number I could show open orders in the detailed section, suppressing group footer and for items will no sales suppress the detail but show the footer.

Below is an example that I hope will explain

Part

Part No Stocking Type
Nut Purchased
Bolt Stocked
Tube Made
Screw Stocked
Washer Stocked

Order

Order Number Line Number Part No Status
1 1 Nut Open
2 1 Bolt Complete
3 1 Bolt Complete
4 1 Tube Open
5 1 Tube Open
6 1 Washer Open
7 1 Washer Closed


After joining (left inner)

Part Stocking Type Order Number Line Status
*********************************************************
Nut Purchased 1 1 Open
Bolt Stocked 2 1 Complete
Bolt Stocked 3 1 Complete
Tube Made 4 1 Open
Tube Made 5 1 Open
Washer Stocked 6 1 Open
Washer Stocked 7 1 Open
Screw Stocked

This is what I want on the report

Part Stocking Type Order Number Line Status
*********************************************************
Nut Purchased 1 1 Open
Bolt* Stocked
Tube Made 4 1 Open
Tube Made 5 1 Open
Washer Stocked 6 1 Open
Washer Stocked 7 1 Open
Screw Stocked

*Bolt still shows but not the completed orders.

Is there a way to do this without using a subreport? If I selected on Status I would lose the 'screw' that is not in the orders table.

 

[hilfy]

I can think of a couple of ways to do this. The easiest may be to make the join an outer join and then do the following in the Selection Criteria (you'll have to edit it rather than using the wizard...):

(IsNull({order.status}) or {order.status) <> 'Complete'

This will get all of your parts records, even if they have completed order records.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

You could just use field suppression on the order number, line, and status fields using:

{table.status} = "Completed"

Then create a formula {@concat} that concatenates all five fields and use that to suppress the detail section:

{@concat} = previous({@concat})

-LB