10 Replies Latest reply on Jan 24, 2017 12:04 PM by avallejo

Counting boolean results

I could swear this one would be easy...(for me)!

I have this 0/1 field in every record...I just want to know how many cases are positive (1).

Thought  -Count (field=1)-    would do it, but it does not...

Stuck again!!!

Researched but didn't find any answers... appreciate any hints...

Thanks

• 1. Re: Counting boolean results

Since the field is either 0 or 1, sum the field value should give you a count.

-Tom

• 2. Re: Counting boolean results

Sum( ) gives you the total

Count( ) gives you the number of non-blank records

beverly

• 3. Re: Counting boolean results

And there is more than one way to get that aggregate value (sum in this case):

A summary field might be used

the Sum ( ) function as previously posted here

Sum as part of an ExecuteSQL query

• 4. Re: Counting boolean results

Tom, my non-mathematical brain wouldn't have came up with that in a million years...rsrsrs

But now this is officially weird.

The field depicted below has five non-blank records with the values 1,3,0,1,1 (tried another one...)

Shouldn't    Sum (field) return 6 ???

• 5. Re: Counting boolean results

I tried Sum( weight ), and it's retuning only the patients weight in that particular record, not the sum of all weights in all records...

• 6. Re: Counting boolean results

your

Sum (field)

may not be the context you need

field - any related field, repeating field, or set of non-repeating fields; or an expression that returns a field, repeating field, or set of non-repeating fields.

And it appears that you have more than "boolean" values.

• 7. Re: Counting boolean results

It is your context, use Phil's advice to get a Summary field of a field in the same table. If it were related fields, that would be different and the Sum() calculation function would work.

beverly

1 of 1 people found this helpful
• 8. Re: Counting boolean results

I suggest that you look up a function in Help before you try to use it.

Sum ( field ) produces the value of field. You've basically asked Filemaker to compute Sum ( 1 ) and the sum of 1 is 1.

Sum can be used in different contexts.

Sum ( RelatedTable::Field )

will return the sum of Field over the set of related records. Bu that means defining your calculation field in a different table than "RelatedTable".

A "Total Of" summary field, on the other hand can return the total of the field it "totals" over the current found set if used in the right context.

• 9. Re: Counting boolean results

Oh, this one does, I was testing in some other fields just to be sure...

I'll look into the context tip...

• 10. Re: Counting boolean results

I did look up...but I obviously didn't understand it...

The summary field worked though...

Thanks all!

And thank heavens I don't make a living out of this...rsrsrs