Need to count results

[maryb]

Hi,

 

I am new to all of this and need some help please. we have a database that has a calculated field that determines the overall health of a project based on 3 other healths - this calculation field is working fine. So, every project record has an overall health of either:

 

"Green" or

"yellow" or

"red"

 

I now need a means to export counts of each of the health colors at a summary level to excel where some charts are being created. I would like the results of a query to be as followed

 

Criteria #1 (such as planned)

 

Number of Green Health

Number of Yellow Health

Number of Red Health

 

Criteria #2 (such as unplanned)

 

Number of Green Health

Number of Yellow Health

Number of Red Health

 

Total number for Criteria 1 and Criteria 2 results by health

 

Total Number of Green Health

Total Number of Yellow Health

Total Number of Red Health

 

Can fields be created to count and then total all for the found set? can you help with some details around this? Can we then have clean results to export to excel to be charted?

 

Thank you!

[CobaltSky]

Hi Mary,

There are various ways to approach the problem you're facing, but the most common method involves the use of summary fields and sub-summary layout parts.

 

Summary fields (see page 83 of the FM9 user guide) are a field type that can be defined to summarize records in the found set, and one of the options is to count the values in a specific field. If you define a summary field to count the values in a "color" field, then search for records with green in that field, the summary field will tell you how many were returned.

 

A sub-summary part (see page 122 of the FM9 user guide) allows you to sort records by a designated field (in this case color) and view a separate set of summary values for each if the data groups (in this case for each color) among the sorted set. The group summary data are visible when the summary field/s are included in the sub-summary part and the sorted records are previewed or printed, or when the data are grouped during export.

 

While exporting to Excel is a possibility, there are of course, ways that charts can be created and displayed within FileMaker itself (generally using either a web viewer or a third-party plug-in and a container field) however these techniques may prove rather challenging for you if you're just starting out. :cool: :eek: :cool: smiley-wink