What am I doing wrong?

AND(
{Order Complete?} =FALSE(),
{Doc Type} ="O",
{OELine Qty Backordered} >0,
{Cust #} ="TD5775",
OR(
{OEHead Salesperson} ="KINGSN"
))

I don't really know how to use AND and OR together, but what I'm trying to accomplish is:

Show me all incomplete orders with items on back order for customer TD5775 and all other customers if their salesperson is KINGSN.

Can anyone help?

Susan:

Try:
AND(
{Order Complete?} =FALSE(),
{Doc Type} ="O",
{OELine Qty Backordered} >0,
OR(
{Cust #} ="TD5775",
{OEHead Salesperson} ="KINGSN"
)
)

The first 3 lines have to be true and only line 4 or line 5 need to be true.

Bruce -- I beg to differ. I think the terminal ")" should be moved up to replace the "," before "OR". This requires the first 4 lines to all be true OR the fifth true.

Susan -- The problem with English is that it's not logically exact. When you say AND all other ..., you really mean OR all other.

Consider the English phrase "all companies with a head office in Toronto AND Montreal". This excludes every company since they can only have one head office so it can't be both in Toronto AND in Montreal.
In English, it is understood the phrase to mean "all companies with a head office in Toronto as well as all companies with a head office in Montreal". The "as well as" is a logical OR.

Can you try it this way?

AND(
{Order Complete?} =FALSE(),
{Doc Type} ="O",
{OELine Qty Backordered} >0,
{Cust #} ="TD5775"
)
OR(
{OEHead Salesperson} ="KINGSN"
)

YESS!!!! Thanks Bruce. Now I get it.

Hi Wally,

I tried yours too.. but it wouldn't evaluate. Thanks anyway though!

Susan -- I've been wrong before!

Are you sure Bruce's gives you the correct items? I'm confused about how you want to select on the customer.

Yes, Bruce's gives me the results I was hoping for.

The AND section shows results for all incomplete orders with items on back order.

The OR section narrows the above results to customer TD5775 and all customers with KINGSN salesperson.

Hi Wally:
I agree that English isn't logically exact and your example demonstrates that. I'm not even certain my one line explanation earlier was precise enough.

It doesn't help that the GridView syntax is a little cryptic, but unlike English, it at least has a defined set of rules.

I picture a large barrel and a smaller bucket. The barrel contains all of the orders that have been entered. Susan wants only the items that are i) Orders, ii) incomplete and iii) have a backorder quantity. The first 3 lines separate out those items and move them to the smaller bucket.

Now that they are in the bucket, she wants to filter them further. She wants only the ones belonging to Customer TD5775 OR belonging to any customer associated with Salesman KINGSN. The last 2 lines do that because they are in brackets after the "OR" operator.

In your example you would have transferred all Incomplete orders with a Backordered quantity belonging to TD5775 to the bucket. That's good. But the final part would have transferred all orders belonging to any customer associated with Salesman KINGSN regardless of whether they are Complete or have Backorders. It would also have included Invoice and Credit Note transactions. That would be a lot of records.

Susan's description of her goal could have been interpreted to mean that result. However it would have resulted in a meaningless report so the other interpretation was more likely.

Wally is in the marketing department, not the R&D department.

But it was clearly a slow day in Marketing! And by "slow", I'm referring to the pace of the day, not the capabilities of the Marketing staff.