Compound criteria

FM 8.5

I have this calculation.

Due =

Case (
Status_Order = "1 Ordered" ; "" ;
Status_Order ="Shipped" and Terms ≠ "Cons"; TotalAmount -
OrderDeposit;
Status_Order ="2 Invoiced" and Terms ≠ "Cons"; TotalAmount -
OrderDeposit;
Status_Order ="2 Invoiced" and Terms ≠ "Cons" and Status_Payment
= "Paid";" ";
Status_Payment ="Paid" and Terms ≠ "Cons" ; " "; Status_Order
="Cancelled" and Terms ≠ "Cons" ; " ")

On my report I have two fields. One for reporting the Order_Status and
one for reporting the Order_Payment_Status

In the line
Status_Order ="2 Invoiced" and Terms ≠ "Cons" and Status_Payment
= "Paid";" ";

I want the result in the Due field to be blank when the Status_Payment
equals Paid. But this does not happen. Can anyone tell me why.


Thanks
Matthew

Re: Compound criteria

In article
,
Buckbuck wrote:

> FM 8.5
>
> I have this calculation.
>
> Due =
>
> Case (
> Status_Order = "1 Ordered" ; "" ;
> Status_Order ="Shipped" and Terms ≠ "Cons"; TotalAmount -
> OrderDeposit;
> Status_Order ="2 Invoiced" and Terms ≠ "Cons"; TotalAmount -
> OrderDeposit;
> Status_Order ="2 Invoiced" and Terms ≠ "Cons" and Status_Payment
> = "Paid";" ";
> Status_Payment ="Paid" and Terms ≠ "Cons" ; " "; Status_Order
> ="Cancelled" and Terms ≠ "Cons" ; " ")
>
> On my report I have two fields. One for reporting the Order_Status and
> one for reporting the Order_Payment_Status
>
> In the line
> Status_Order ="2 Invoiced" and Terms ≠ "Cons" and Status_Payment
> = "Paid";" ";
>
> I want the result in the Due field to be blank when the Status_Payment
> equals Paid. But this does not happen. Can anyone tell me why.

There's some strange symbol there that doesn't translate to Usenet, but
it's not relevant to your problem. (I'll assume it's the "not equals"
symbol and replace it with <> just for the sake of readability.)

The problem here is that you have too parts of the Case statement that
use very similar tests.
ie.

Status_Order ="2 Invoiced" and Terms <> "Cons";
TotalAmount - OrderDeposit

and Status_Order ="2 Invoiced" and Terms <> "Cons"
and Status_Payment = "Paid"; " "

The order of your tests within the Case statment is very important.
When a Case statement is evaulated, FileMaker will return the result
from the FIRST test that matches - FileMaker doesn't bother continuing
to look for a "better" match further down. In this Calculation that
means when Status_Order is "2 Invoiced" and Terms is not "Cons", it is
the first test above which is matched, and it's irrelevant what data is
in Status_Payment and the second test that also looks at the
Status_Payment field will never be reached.

To fix this problem you would simply swap the order of those two tests.
ie.

Status_Order ="2 Invoiced" and Terms <> "Cons" and
Status_Payment = "Paid"; " ";
Status_Order ="2 Invoiced" and Terms <> "Cons";
TotalAmount - OrderDeposit

so that the test that checks the Status_Payment field is evaluted
first. If the test succeeds you'll get the value " ". If Status_Payment
is not "Paid" then that test fails and FileMaker tries the next one.


BUT,
in this particular example you've also got another test that is similar:

Status_Order ="2 Invoiced" and Terms <> "Cons"
and Status_Payment = "Paid";" ";

and Status_Payment ="Paid" and Terms <> "Cons" ; " "

This second test ignores the data in Status_Order so that any record
that has Status_Payment = "Paid" is given a value of " ". Putting this
test above the others would make the first one of them not needed at
all.

You should also get into the habit of putting extra brackets /
parentheses around sub-parts of multi-part tests to ensure they are
evaluated as you want them to be.

Putting all this together means your Case calculation becomes:

Case (
Status_Order = "1 Ordered"; "";
(Status_Payment = "Paid") and (Terms <> "Cons"); " ";
(Status_Order = "Shipped") and (Terms <> "Cons");
TotalAmount - OrderDeposit;
(Status_Order = "2 Invoiced") and (Terms <> "Cons");
TotalAmount - OrderDeposit;
(Status_Order = "Cancelled") and (Terms <> "Cons"); " "
)

This could be simplified even further, but that would probably make it
more difficult to understand.





Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)