Calculation Help

[msix]

I have a field where there are two values allowed: "Active" & "Canceled". How do I create a calculation field to count the number of records where the field = "Active". In programming I'd do something like:

set record_count = 0

if ( $reg_type = "Active" ) then

@ record_count++

endif

How do you do it in FileMaker?

Be gentle... I'm learning.

M6

Edited January 31, 2008 by Guest

[Inky Phil]

One way would be to create 2 calc fields, both result number.

Lets call the first field actives. This field should have the calc

[color:blue]Case(relevantfield = 'active';1;0)

The other field should have the calculation

[color:blue]Sum(actives)

The first field will have a 1 in it if the relevant field is 'active' and a 0 in it if the field is anything else.

The second field will give you the total number of records that have a 1 in the field 'actives'

HTH

Phil

p.s. Both fields should have the 'do not store, calc as necessary' checked.

[Søren Dyhr]

You need to tell us what you need the counting to arrive in, and how many records we're likely to deal with?

It seems like it's php you writing, is it a php question?

--sd

[msix]

It's not PHP... I just have a PHP background. I was just trying to illustrate that I know how I'd do it in Perl or PHP but am learning FM on the job.

Basically, I have a field called STATUS that has 2 values: Active & Canceled. I also have another field called REGTYPE that has 2 values: PT & FT

I need to:

1. Set a field calculation to count all of the records where STATUS = Active.

2. Set a field calculation where STATUS = ACTIVE *and* REGTYPE = FT

3. Set a field calculation where STATUS = ACTIVE *and* REGTYPE = PT

Make sense?

M6

[msix]

I figured it out....

Field: ActiveYes is an unstored calculation...

Case (Status = "Active"; 1; 0)

Field: ActiveTotal is a Summary field ...

Total of ActiveYes

Field: FTYes is an unstored calculation ...

Case (Status = "Active" and RegType = "FT"; 1;0)

Field FullTimeTotal is a Summary field ...

Total of FTYes

Thanks for your help and for pointing me in the right direction!

M6

[Søren Dyhr]

Good catch since Phil made an error since Sum( only works over relations, or with repeating fields. But since Filemakes Summary fields works with void/null values as well could the last half of the case( statement be ignored.

But unfortunately are you not on the right track yet, you should just for the hell of it make yourself some 400000 records to see the disadvantage of the suggested technique, Summary field should not be part of a live layout but be reserved genuine sub-summary reports!

--sd

[Inky Phil]

Oops Sorry!

Phil

[LaRetta]

Ah, Phil, it's just good to see you around again. :wink2:

[comment]

Field: ActiveYes is an unstored calculation...

Case (Status = "Active"; 1; 0)

A lot of these fields can be eliminated by making Status a Number field and treating it as a Boolean. See:

http://www.fmforums.com/forum/showtopic.php?tid/181943/post/228583/#228583

[msix]

It's actually not a part of a live layout; it's on a summary layout. The system is programmed to allow no more that 5,000 records since that's 2,000 over the allowed bodies in the studio, according to fire code. The most they've ever had in the dance studio at one time is 800 students. In 22 years of business, the owner has never had more that 1,500 active members, with about 400 inactive members. The owner is happy with the size of the studio and has no plans to expand, thank heavens!

Thanks for the limit test suggestion. I'm always up for optimizations, improvements and learning of course.

Thanks to all. I appreciate your time and patience.

God Bless,

M6