Counting Unique Values

[wonderbike]

I know there are dozens of threads on this topic, but I have not been able to implement the solutions in this case.

I am using Filemaker 10 on Windows 7.

I work for a university's international department and am making a database on the students we send abroad and receive from abroad.

The basic structure of the database has a student table, university table, semester table, and a line-item-style table connecting semesters to students.

Say for example, we sent 3 students (one for 1 semester, and 2 for 2 semesters) to X university in Fall 2009, 2 in Spring 2010 for a semester each.

I have a layout where I would like to show both the number of students in X university in any given semester, but also the number of students we have sent to X university overall. The layout is based on the line items table and looks like this:

(sub summary sorted by university) University

(sub summary sorted by semester) Semester

(body) Line item

What I want the layout to look like is:

University X (5 students)

Fall 2009 (3 students)

Student A NEW

Student B NEW

Student C NEW

Spring 2010 (4 students)

Student A CONTINUING

Student B CONTINUING

Student D NEW

Student E NEW

But what I get is

University X (7 students)

Fall 2009 (3 students)

Student A NEW

Student B NEW

Student C NEW

Spring 2010 (4 students)

Student A CONTINUING

Student B CONTINUING

Student D NEW

Student E NEW

Every student has a unique key, so I *think* I should be able to get the right number if I can count the number of unique student ID numbers in each part

I have tried the popular 1/getsummary solution and can't seem to get it to work in this situation. For some reason it returns the same value as a regular count.

I also set up a calc field in my line items table which is set as equal to the student serial number. When I make a self-reference to try and count that way, I end up getting an <index missing> error.

I would really appreciate any help with this. It is my last hurdle before we can actually start using the database in day-to-day work.

Thanks,

Mike

[comment]

I have tried the popular 1/getsummary solution

That won't work here, because records are not grouped by student (the method counts sorted groups). It would probably be easier to solve this through relationships - but this raises the question whether all students are being reported (relationships ignore found sets).

How many universities are you reporting on at one time?

[wonderbike]

There are over 100.

Ideally, I want to use this layout to reflect a found set. In our office, we have to submit a lot of numbers to the government, like "how many male students went to Korea between 2009 and 2010?" or "how many students in XX major have gone to XX university since student exchange started?"

In order to be able to pull out those numbers quickly, I have a different layout that is used as a search page where the user can select university, country, gender, and a few other things, then click "go" and be brought to the layout mentioned above. The search works perfectly - if I want a list of all the male students who went to Korea in a certain time frame, I get that list no problem. It just seems silly that if I can get that far, I can't also have it spit out an accurate count of them as well. As it is currently, any student who was in Korea for 2 semesters in that time frame gets counted twice.

Is there a way to solve this with relationships?

[comment]

Is there a way to solve this with relationships?

No, not if you want to reflect the found set. For example, student A may have attended University X in Spring 2009 (which is not included in your report). A relationship would include that and consider his attendance in Fall 2009 as "continuing".

There are over 100.

In one report? What I am asking is do you need the unique student count for the entire report, or for individual groups (universities) within the report?

[wonderbike]

In one report? What I am asking is do you need the unique student count for the entire report, or for individual groups (universities) within the report?

If we were asked "How many students were sent to the UK between 2009 and 2010?" then the report shows students from the 7 or 8 universities in the UK we send students to. The report as I have it set up shows the correct student list, but not the correct count.

Ideally, if I produced the report above, it would show the total number of students sent to the UK, and then a subtotal for each university within that. Would those require different solutions?

[comment]

So, worst case scenario:

University X

	Fall 2009

		Student A

		Student B

	Spring 2010

		Student A

		Student C

		

University Y

	Fall 2009

		Student D

		Student E

	Spring 2010

		Student D

		Student E

		Student B (transferred from X)

should be reported as:

University X: 3

University Y: 3

Total: 5

Is that correct? Note that the totals do not match.

[wonderbike]

So, worst case scenario:

University X

	Fall 2009

		Student A

		Student B

	Spring 2010

		Student A

		Student C

		

University Y

	Fall 2009

		Student D

		Student E

	Spring 2010

		Student D

		Student E

		Student B (transferred from X)

should be reported as:

University X: 3

University Y: 3

Total: 5

Is that correct? Note that the totals do not match.

Yes, those are the numbers I would like to see. What I would get reported with my current setup is:

University X: 4

University Y: 5

Total: 9

[comment]

Well, I hope by now you understand that this is not going to be simple. What I would do here is precount the totals by script, i.e sort the found set by university and by student and do the counts. Then sort by student only and count again to get the grand total.

The counting can be done using the "Fast Summaries' method. However, there is the question of where to put the results and how to display them. I would store the subcounts in a global variable, and use an unstored calculation field in the Universities table to pull from there. The grand total can go into a global field (in any table).

[wonderbike]

I was afraid that this was going to have to involve scripting.

The there is one other thing I was considering, but wasn't able to think all the way through.

What if the report I mentioned was based on the students table, rather than the line item table? I think this would eliminate the problem of counting students twice, but then students A B D and E would only show up once in the report, right?

[comment]

What if the report I mentioned was based on the students table, rather than the line item table? I think this would eliminate the problem of counting students twice, but then students A B D and E would only show up once in the report, right?

Right. You would have 5 records in your found set.

I was afraid that this was going to have to involve scripting.

The only other option I see is to work entirely with filtered relationships. But this is only viable if you can predict all possible criteria, i.e. country, university, semester, gender, etc.

[wonderbike]

Thank you for all your help. I will either try scripting, or limiting the number of criteria to the point where filtered relationships are viable.

Thanks again

[xochi]

Some discussion of custom functions to count unique values in a List:

http://fmforums.com/forum/topic/72555-uniquelistfast-and-uniquelistcountfast/page__p__343018#entry343018