How do I exclude records in one table based on a second table? 1

[weigoldk]

I'm not sure how to phrase my question so I'll give an example. I'm using an ODBC connection so I can't change the structure of the tables at all.

I have a table to "customers." Each customer has multiple "orders" in a second table. I want to run a report that shows customers from Michigan, Ohio and Indiana (I have a field for state) who did NOT order a widget.

I'm using grouping and supressing the details so that all I see are the customer numbers and names.

I've run it and tested it. John Smith shows up. He ordered a widget and lives in Ohio. When I show the detail, all of orders show up EXCEPT the record for the widget order. I don't want John Smith to show up at all because he ordered a widget.

{order.itemprice} > $0.00 and (eliminates returns and credits)
{order.state} in ["MI", "OHIO", "IN"]and
{order.item} <> "WIDGET"

 

[CrystalizeCanada]

Hi,

There are several orders for widgets, wotsits and thingamegigs.

If John Smith ordered a widget amongst other things he shouldn't show up in the report.

If I'm understanding correctly the following will work.

1. Remove {order.item} <> "WIDGET" from the record selection formula that you currently have
2. Create a formula
If {order.item} = "WIDGET" then 1 else 0
3. Sum this formula for each group
4. Click on this Sum and then click the Select Expert
5. Filter so this sum = 0

Let us know if this works

Tx

Gordon BOCP
Crystalize

 

[weigoldk]

Thanks Gordon! That's exactly what I needed. The widgetless report is exactly what we need.