Case Function and All Related Records

[jill]

I've got a order database with a portal showing all the items included on an order. Part of the items list is a field indicating if goods were received complete, short or in excess. I've put a flag field on the order to indicate if all items were not received complete by using the Case function. However, it only evaluates the first record in the list of related items. How do I get it to evaluate all the related records instead of just the first one? Or do I need to use a different function or method to do this?

Here's my calcuation:

Case(Items for Order::Goods received ≠ "Rec'd Complete"; TextColor("Receipt Notes" ; RGB (255; 0; 0));

IsEmpty (Items for Order::Goods received); TextColor ("Receipt Notes" ; RGB (0; 0; 0));

TextColor ("Receipt Notes" ; RGB (0; 0; 0)))

I want it to turn "Receipt Notes" to red if the Goods Received field does not contain "Rec'd Complete".

[Fitch]

FileMaker 8.5 has the List function for this, e.g.:

ValueCount(Items for Order::Goods received ) =

PatternCount(List( Items for Order::Goods received );"Rec'd Complete")

In earlier versions (you have 8.0?), you'd have to define a value list based on the related field, and then use the ValueListItems function in place of List.

Another approach would be to use a calc field in the related table:

Goods received = "Rec'd Complete" // result will be 1 or blank

Then in your calc in the parent table you could compare the count of that field to the count of the items. Does that make sense?

[jill]

I do have 8.5. I haven't used the ValueCount or PatternCount functions before. Tell me if I'm understanding how to implement this:

The ValueCount will return the number of related items for the order.

The PatternCount(List) will return the number of times "Rec'd Complete" occurs within those items.

Do I set up a Case function which says if the number of times the PatternCount equals "0", then it makes "Receipt Notes" turn red, otherwise, it stays black?

If so, how should I be using the ValueCount function?

[jill]

I got it to work using the following:

Case(Count(Items for Order::Goods received)=PatternCount(List(Items for Order::Goods received); "Rec'd Complete");

TextColor("Receipt Notes"; RGB (0;0;0));

Count(Items for Order::Goods received) = 0;

TextColor("Receipt Notes"; RGB (0;0;0));

TextColor("Receipt Notes"; RGB (255;0;0)))

Using Count instead of ValueCount allowed it to work. When I used ValueCount, it would not return the total number of items for the order, instead it returned the number of times "Rec'd Complete" was listed for the items.