One way to do it is with a global calculated field in the sales person table that is equal to "closed" -- let's call it globalStatusClosed.
Now make a relationship with two predicates:
Then make a calculated field in sales, using that relationship and a field that always has a value such as an auto-entered ID:
Thanks, that helped me understand things a little better. It does leave me with another question though...
What if I want to have another column in my Sales table which counts the total # leads (open or closed) each sales person has? Using relationships like this seems like it will limit me pretty quickly...
You can do everything with only one relationship:
Total number of leads =
Count ( Leads::LeadID )
Closed leads =
ValueCount ( FilterValues ( List ( Leads::Status ) ; "Closed" ) )
P.S. Don't use names for relationships.
Michael's solution is better than mine. And I completely agree, using names (i.e. any modifiable data) for relationships is not a good idea. I only used that in my example to stay in line with the question as asked, but never mind that -- use unique IDs for relationships, not names!
I was going to suggest yet another way to do it: in the leads file, make separate fields for each status. Make each status a number field that you set up as a checkbox (1 vs 0 or empty). You could make trigger scripts so that when one status is set, the others are cleared. You could then use simple sum or count functions from the salesperson.
However, if you anticipate many different status options, this might not be the best way. It all depends on what kinds of entry and reporting you'll be doing.