Hi,

I've got two tables, "Company" and "Person" with a one-to-many relationship
(one company can have many persons). "Person" table has a field with "Yes"
and "No" values.

I want to get a count in the Company layout of the number of Persons with
"Yes" values.

I defined a calculated field in Table Company and specified the calculation
as "Count ( Person::Select this person = "Yes")" but FileMaker reports an
error:
"In the function Count an expression was found where a field
alone is needed."

So, how can I get a conditional count of Persons with field "Select this
person" = value "Yes".

Thanks,
Steve

You set up this "filter" in the *relationship* not the calculation.

The Count() function would simply be Count ( SelectedPersons::Select )

Where SelectedPersons is a table occurrence of Person with the appropriate
relationship specified.

"Steve Drenker" <sdrenker (AT) pacbell (DOT) net> wrote

Thanks, Bill. I'm sorry, but I don't follow. I created another table
occurrence of Person and named it "SelectedPersons".

Table Name Company Persons SelectedPersons
Fields CompName PersName PersName
Select Select
CompSN CompSN CompSN


The relationship between Company and Persons is through CompSN (Company
Serial Number). Ditto for Company and SelectedPersons tables.

From here on, I don't understand.
1. How do I change the relationship between Company and SelectedPersons?
2. What field do I put the Count() function in?

Steve

The lines between table occurrences define the relationship between them.
The default is an equals sign. You change the relationship by
double-clicking the box that holds the "=" symbol.

At first blush, it would appear you should create a global field in Company
set to "yes" and a line between that field and the SelectedPersons::Select
field... you can also add more than just one set of criteria to the line,
for example if CompanySN must be considered in the relationship as well.

It's basically a lot like defining a "Find" -- where the TO is the label for
the "matching records" that would result.


"Steve Drenker" <sdrenker (AT) TAKEOUTpacbell (DOT) net> wrote

In article <C178CF7D.2BC7E%sdrenker (AT) pacbell (DOT) net>, Steve Drenker
<sdrenker (AT) pacbell (DOT) net> wrote:

Count is no use here. Firstly Count needs just a field as its
parameter, not a test.
ie. Count(SelectThisPerson) will work
Count(A = B) won't work

Added to this is a problem that the function simply counts the number
of fields that have data in them - which since your field is either
"Yes" or "No" will mean you get everyone included.

The easiest way to get what you want is to add another Calculation
field to the Person table that is set to 1 if that person is a "Yes".
eg.
PersonIsYes {Calculation, Number Result}
= If (Field = "Yes", 1, 0)

Then in the Company field you can have another Calculation field that
totals the above field using the Sum function.
ie.
TotalPersonIsYes {Calculation, Number Result}
= sum(Person::PersonIsYes)

This will give you a count of the related records that have "Yes" in
them.




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

Thanks, HH. I found the same approach described in a FileMaker Café post and
implemented it. Works fine.

My big problem now is I have 32 calculated fields that are unstored. Overall
database performance is in the toilet.

SD

In article <C17A2F72.2BE4B%sdrenker (AT) OMIT_THISpacbell (DOT) net>, Steve
Drenker <sdrenker (AT) OMIT_THISpacbell (DOT) net> wrote:

Unless you've got a HUGE number of records or an ancient slow computer,
you shouldn't even notice a tiny 32 calculation fields.

You can alter the method of counting the "Yes" records which MIGHT
prove faster. Instead of a Calculation field, use a normal field that
is automatically entered as 0 (assuming that "No" is the default entry
for the corresponding field). Then use a script that changes the "No"
to a "Yes" and the 0 to a 1. This way the field can be stored and the
slowdown will only be when summing via the relationship.

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