Count Only Records that Meet a Criteria

[svalenti]

I'm looking for a way to count records that meet a certain criteria.

I have a field (rating) that uses numbers 7 - 10 to identify the motivation of clients. I need a summary type field that will display a found count of all the client records that have a rating = 7, another field that counts rating = 8, etc..

I'd prefer to do this without creating a relationship for each summary field.

[Vaughan]

A summary report will do what you need, as long as you don;t mind having to preview or print the results. It'll only need one summary field to be created too.

[svalenti]

Thank for your quick response.

I though of that, but I needed it more "Live".

I actually solved my problem. Sometimes just posting the question helps me think!

I used 2 fields

1 calculation field:

rating_calc_10 Unstored, = If ( clients_consultant_assigned::rating = "10" ; "1" ; "" )

Then a summary field:

count_rating_10 = Total of rating_calc_10

I just use the summary fields in the related portal and they dynamically update if I adjust the person the lead is assigned to or the date range. Works great!

[Genx]

Just a general note, with If statements, you don't have to specify a false condition so If ( clients_consultant_assign ed::rating = "10" ; "1" ) is perfectly acceptable...

[Genx]

Infact, you could probably just get away with clients_consultant_assign ed::rating = "10" which will return 1 for true and nothing or 0 for false.

[comment]

Indeed - but then you'd better move to Sum instead of Count.

[Genx]

Oh Right, easy to forget that 0 'counts' ... Me and my bad puns lately.

[David Jondreau]

You can do this with just one extra field per value (rather than two) with a custom function. I modified another CF I made and haven't tested this out, but it should meet your needs.

//TypeCount: This function will count records whose TypeField = Type

//Start should =0 or blank

//By changing the End variable's definition to = Count(TypeField), this function will work on a related set.

Let(

[

End = Get(FoundCount);

counter = Start +1;

total = If(GetNthRecord(TypeField;counter)=Type; 1)

];

Case(

counter

total

)

)

[Søren Dyhr]

While this might work on very small sets:

Let(

[

End = Get(FoundCount);

counter = Start +1;

total = If(GetNthRecord(TypeField ;counter)=Type; 1)

];

Case(

counter

total

)

)

Have I however taken this statement to heart:

Anyhow, I asked about this statement and he says he always steers people towards traditional reporting methods (subsummary reports) for speed reasons and flexibility. Unless there is some really great reason to use relationships to simulate a report, don't do it. FileMaker was not designed to aggregate massive amounts of information through relationships. Anyhow, I just wanted to make sure readers were clear on the best approach to reporting.

This is a question made on my behalf by JMO to Andy LeCates, whos the one top most in charge of development at FMInc.

Snipped from:

http://www.fmforums.com/forum/showpost.php?post/206543/

This means that a task like this in a proper way could be done using this technique:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

...this have in this case let me to produce the following template...

--sd

SplitSummary.zip

[Genx]

Let(

[

End = Get(FoundCount);

counter = Start +1;

total = If(GetNthRecord(TypeField ;counter)=Type; 1)

];

Case(

counter

total

)

)

... Try navigation through 100,000 records with that, and then compare that to a sum() on a stored calclation field.

[David Jondreau]

The most beguiling kind of ignorance is the ignorance you don't even know you have.

I've always thought that summary fields could only be used on sub summary parts (and therefore not available in Browse mode). Your post has made me actually look at summary fields and what they're good for.

Man I feel stupid.

[svalenti]

Sumary fields in portals are the only way I know to display info from multiple relationships on the same page. I guess I should have mentioned that the original problem needed to include multiple relationships.

Is there a way to create 1 summary report when I need to display results from 4 completely separate relationships? None of the relationships share the same parent table.

[Genx]

Could you explain the purpose of your let statement Vs. just: Extend( Motivation ) - 6 = Get(CalculationRepetitionNumber )

Last time I checked 1 + 1 + 0 + 0 equals 2, just as 1 + 1 equals 2. Your not saving any evaluations by not actually displaying the 0.

[Genx]

Is there a way to create 1 summary report when I need to display results from 4 completely separate relationships? None of the relationships share the same parent table.

Probably using the calculation method as was originally suggested, but care to construct a sample for us to take a look at?

[Søren Dyhr]

just: Extend( Motivation ) - 6 = Get(CalculationRepetition Number )

No I can't - you're absolutely right!

--sd

[Søren Dyhr]

Sumary fields in portals are the only way I know to display info from multiple relationships on the same page

This is news to me, have never seen it before - only aggregate functions? On the other side have I on several occations seen related data on a subsummary report!

None of the relationships share the same parent table.

Again are the semantics playing tricks... at least on me! Summary reports are best of being made in join table layouts? Are you aware of the relations now are bidirectional, unless you deliberately Anchor Bouy the graph.

--sd

[Søren Dyhr]

Your post has made me actually look at summary fields and what they're good for.

At least you need to consider measures to circumvent the iteration max, tail-recursion will get you from 10K to 50K - but you could also borrow the idea from this:

http://www.clevelandconsulting.com/support/viewtopic.php?t=1264

The good question is then, if you like Alex break the found set up in chunks and make them tail recurse instead, will other things get in the way for dealing with an arbitrary occationally whopping set??

I've always thought that summary fields could only be used on sub summary parts (and therefore not available in Browse mode)

I kind of agree with you, when ever I recieve a filemaker newbe first attempts into a file, slow as molasses is it usually the freshing of cascades of shown summary fields hointed all over the place, nowhere near Harum-Alvarez'ish ideals of proper distinction between need to know vs nice to know.

We even had to justify why every object was present on a layout.

And were they in the best possible location?

snipped from: http://www.sumware.net/robfm2/?p=24

What I did in my template, obviously isn't!!!

--sd

Edited June 2, 2007 by Guest