counting unique values using "min(serial)="

[uclacnsi]

Hi,

Here's the premise:

I want to generate a report of projects for a specific date range. I want to be able to count the number of unique professors who had projects during that date range. The total number of projects will be greater than the total number of unique professors, ie, there will be mutiple projects to one professor.

Currently, I import the data into an empty table and use the following calculation that I got from the FM site to tag records as having a unique professor or not. I created a clone of the table and self join relationship as instructed on the FM site http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-of-unique-values-in-a-field

min(serial)=min(ProjectsClone::serial)

this returns 1 for the first instance of a professor and 0 for subsequent instances. I then tally this field to get my total number of unique professors.

Here's the problem. When I do a query for the date range I want, my uniqueness tagging is not updated for the subset. I have to delete the omitted records in order to update the uniqueness tags. Is there a better way for all this? Mind you, I need the uniqueness tag to summarize other things, eg, if(uniqueProf=1 and dept="Chemistry",1,0)

I'd like not to delete the omitted records and somehow have the uniqueness field be dynamic and update with found sets. Frankly, I'd like not to have to do the import into new table thing but perform all this on the original table.

Thanks

[Vaughan]

Have you tried using a summary report for this?

Can you describe the data structure, because (unless I have missed something) this shouldn't be too difficult to do. Certainly not requiring imports into empty tables, etc.

[comment]

I created a clone of the table and self join relationship as instructed on the FM site http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-of-unique-values-in-a-field

As you have discovered, the self-join is not a good method because it disregards the found set. The second suggestion looks more promising, but I couldn't follow it. See if this helps:

http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/page__p__289204#entry289204