if/case functions with related files

[CWH]

I'm trying to sum a field in one database (CTA) in a related db (Reviewers). I only want to total the Primary Reviewers from a particular group: those that are marked "Review (send)." I tried the following, but it only adds up the Primary Reviewers if ALL of the records are marked that way, otherwise, it's blank.

Case(CTAone::AppStatus = "Review (send)", Count(CTAone::Primary Reviewer))

Maybe it should be an If function?

thanks for suggestions

[Fitch]

CWH, we meet again! To answer your hypothesis, If and Case are essentially the same. But forget that. Here's the deal: you have shot yourself in the foot by naming your field "Review (send)." Fields with names that include parentheses can't be used in calculations, and we need to Count() this field. So I'm renaming it reviewSend. Now then...

Count(CTAone::reviewSend)

[CWH]

Fitch, thanks for your efforts on my behalf once again. Unfortunately, I wasn't clear -- the field is AppStaus and Review(send) is text from a value list. Even when I change the text to ReviewYes, it's still not counting the primary reviewer field for at least one record.

[Fitch]

Ah, I wasn't looking closely enough. You described it just fine the first time. I see now. This is a common situation. When you want to count a text field based on its contents, you have to create a calculation field, in this case we'll call it reviewSend:

AppStatus = "Review (send)"

That's a field in the CTA file. The calc will return a 1 if it's true. Then you can count or perhaps total it in the Reviewers file as in my last post:

Total(CTAone::reviewSend)

[CWH]

Unfortunately, I don't think that will give me what I need, which is to tabulate each type of review that each reviewer is assigned, but only if App status=Reviewsend. So I think there has to be an If function involved, but the one I constructed has the problem I indicated above. If any of the applications assigned to a particular reviewer do not = Reviewsend, then it doesn't count any of them.

[Fitch]

You're going to need 3 calculated fields in the CTA file:

PrimarySend...

Case(AppStatus = "Review (send)", Primary Reviewer)

SecondarySend...

Case(AppStatus = "Review (send)", Secondary Reviewer)

TertiarySend...

Case(AppStatus = "Review (send)", Tertiary Reviewer)

Now, in the Reviewers file, instead of this:

Count(CTAone::Primary Reviewer)

...do this:

Count(CTAone::PrimarySend)