Count # of related records

[Aaron L.]

Hi there,

I've been searching for an answer to this question for about an hour with no luck so far. Maybe one of you guys can help me...

I am trying to create a calculation field which simply displays the number of records which are related in a related table.

Here is the exact scenario:

The 'student' table lists all of the people who are using our service. Each student has a studentID number

The 'tutor' table lists tutorID/studentID relationships. If tutor 100 and student 200 are working together, the relationship is shown as relationshipID 2, tutorID 100, studentID 200. Each student can work with multiple tutors.

All I need to do is add a field in my 'student' table which calculates how many times their studentID appears in the 'tutor' table.

Any help you can give is much appreciated. Thank you!

Aaron

[Søren Dyhr]

http://www.filemaker.com/help/Functions%20Ref3.html

--sd

[Aaron L.]

Thanks for your reply. I've tried the count() function before, but it is showing the total number of records up to that record, not the # of related records.

Example: there are 20 student/tutor relationsihps, I've entered count(tutortable::studentID) and instead of showing the # of relationships for that specific user (say relationshipID 120 out of 300), it shows the total number up to and before that (120).

[Aaron L.]

oh, by the way... my tables are related as follows:

tutor::studentID = studentID

[Søren Dyhr]

The 'tutor' table lists tutorID/studentID relationships. If tutor 100 and student 200 are working together, the relationship is shown as relationshipID 2, tutorID 100, studentID 200. Each student can work with multiple tutors.

....And you solve this without a join table??

--sd

[comment]

IIUC, the 'tutor' table IS a join table (one student tutoring another). Perhaps a better name for the table would be 'Tutorships'. What I am missing is the relationship:

Students::StudentID = Tutorships::TutorID

which is required to count the number of students tutored by a specific student-tutor. Aaron, perhaps you should post a sample file.

[Aaron L.]

Hi guys, here's a link to the file: http://speekit.com/DB.fp7. The table names, etc. are not the real names but you should get the idea after looking at the file.

The real names of the fields I am trying to get to work are: No_Coaches (in StudentSync table) and No_students (in Coach table). It will make more sense if you look at the layouts.

It's probably something that's really easy, but I'm new to this and still trying to figure it out...

Aaron

[comment]

OK, I see my assumption was wrong - it's Coaches vs. Students.

First thing I would suggest is clean up your data: make sure each Student and each Coach HAS a unique ID (an auto-entered serial number is best for this), and that the StudentID and CoachID fields in the student_coaches table are Ids that actually have corresponding records in their respective tables.

After that, a calculation of:

Count ( Students::StudentID )

in the Coaches table should give you the number of students of a coach.

[Søren Dyhr]

Hi guys, here's a link to the file: http://speekit.com/DB.fp7.

Here's an example why it isn't particular informative for future readers of this thread - to upload on your own site, the link has dried out and nobody have a chance to guess what you template contained. It might in your case have solved your problem right away - but what if a user of the forum seeks input for a problem in the vicinity of yours - would they remain clueless!

The problem at hand is as such a classic problem, because a fair share of the students acts as tutors as well. Which means the table although we are dealing with a many2many structure is the table the same or a collapsing of the tables not being join.

Now with filemakers RG tools could it easily be solved by still having only two real tables, but by having several table occurrences dealing with each their own task. Next issue to deal with is focus and perspective for this counting, is the problem to know how busy an assigned tutor really is, when assigning him/her to a student? This is something to grasp two table occurrences away!

Similar issues occurs if we on the same layout needs to show the opposite perspective - say in a tabbed layout, since every layout is closely tied to one TO...

I've assembled a template to get my own thoughts straightened, with these aspects, and could have ignored something, which you might have better solutions for??

--sd

Tutor.zip