AnsweredAssumed Answered

Problem with counting the number of unique values in a field

Question asked by sblake on Aug 15, 2017
Latest reply on Aug 15, 2017 by bigtom

 

Here’s an interesting challenge. Help would be appreciated with this very strange problem.

 

My FM database has companies, each with plants in various locations.

A company could have one or more plants.

The plants undergo a certification process.

We do reports showing how many plants are certified through a given date.

And how many unique companies these plants represent; i.e. number of companies will be less than number of plants.

 

Relevant fields:  CompanyID, CompanyName, PlantName, CertificationStatus

 

To find the number of certified plants I created a COUNT of the certification status.

Works perfectly.

 

To find the count of unique companies I am using the “Two summary fields, one calculation, and sort method” (found at http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-of-unique-values-in-a-field)

 

But the field showing total unique companies remained blank, until I happened upon a strange solution.

I sort by the CompanyID

Then delete and replace (with same number) the CompanyID in about four of the records.

And voila! The count of unique companies appears (and it is correct).

 

Am I missing something?  Any ideas?

 

Thanks so much.

Outcomes