Count Field Occurance

[biggles1212]

Have a passenger list and next to their name it has 'adult', 'child' or 'infant' in a drop down list.

At the bottom I wish to summarise the booking schedule so the person reading the report can see how many times 'adult' was selected for example.

 

The following is in a portal:

 

Age Bracket, First Name, Last Name, etc etc

(Adult, Child, Infant), (First Name), (Last Name) etc etc

 

At the bottom of the screen I wish to have:

 

Adults: x

Children: x

Infants: x

 

Thank you in advance. I love using this forum to learn!

[Raybaudi]

Try:

Let([
L = List ( YourTable::Age Bracket ) ;
A = ValueCount ( FilterValues ( L ; "Adult" ) ) ;
C = ValueCount ( FilterValues ( L ; "Child" ) ) ;
I = ValueCount ( FilterValues ( L ; "Infant" ) )
];
List (
If ( A ; "Adults: " & A ) ;
If ( C ; "Children: " & C ) ;
If ( I ; "Infants: " & I )
)
)

[Helpful Harry]

If that doesn't work, then the old-fashioned way will.

Create three new Calculation fields in the portal's Table:

IsAdult = If (Age Bracket = "Adult"; 1; 0)

IsChild = If (Age Bracket = "Child"; 1; 0)

IsInfant = If (Age Bracket = "Infant"; 1; 0)

Then in the main Table you can create Calculation / Summary fields to total those three fields:

SumAdult = Sum (Relationship::IsAdult)

SumChild = Sum (Relationship::IsChild)

SumInfant = Sum (Relationship::IsInfant)

[Wim Decorte]

Ugh... that's 6 new fields to count values of one field...

 

That's the kind of stuff that makes a database slow and difficult to maintain.  Raybaudi's approach is much better.  And there is also ExecuteSQL, a looping script,... many different ways that will not burden the schema with unstored calculations and summary fields.

[Helpful Harry]

I never said it was elegant nor even the "best" solution. It's simply one possible solution that works in any version of FileMaker and is easy to see how it works.

[comment]

I never said it was elegant nor even the "best" solution.

 

Then what is the problem? The assessment of pros and cons of any proposed solution is a legitimate part of the discussion.