Stuck on Sorting Issue

[Kent Searight]

Hi all,

I'm trying to figure out a way of sorting a value list in as simple a manner as possible. Here's the scenario:

I have 5 tables - Teachers, Assignments, Classes, Enrollment and Students. The Assignments table joins Teachers to Classes via their respective IDs to show which teacher is assigned to which class. The Enrollment table joins students to classes.

In a report that lists the students that are enrolled in a given class, I'm showing the teachers' names in the footer by parsing the results of a conditional value list into a "comma+space" separated list. An example would be:

Value list =

Jane Evans

Tom Smith

Displays as...

Jane Evans, Tom Smith

That worked well enough until I was asked to show the teachers' names by rank in the class. So if Tom Smith is the main teacher in the example above, the teachers' names would appear as:

Tom Smith, Jane Evans

I've created a number field in Assignments for ranking the teachers and I know that this is the key to the sorting issue, but I can't figure out how to implement it, short of setting a new field's value to Rank & TeacherName by script, and then using that field as the source of my list values. Of course, I'd have to strip out the numerical values of the list items, but that's no big deal. I'm just stuck on coming up with a simple way of creating a value list that sorts the way I described above.

Hopefully my explanation was clear enough. Thanks in advance for any help offered!

Edited August 22, 2006 by Guest

[Ender]

Could you use a CF with GetNthRecord() instead? I think that will respect the relationship's sort order.

[Mikhail Edoshin]

Try to make a value list showing two values — the teacher name and the rank. The rank must be the second field. Make this value list sorted by the second field. Now, as far as I can remember, ValueListItems( filename, this list ) will return a list of teachers sorted by their rank.

[Kent Searight]

Sorry it took so long to get back to you guys...

Could you use a CF with GetNthRecord() instead? I think that will respect the relationship's sort order.

Hi Mike,

I'm not following you. Could you please elaborate?

Try to make a value list showing two values — the teacher name and the rank. The rank must be the second field. Make this value list sorted by the second field. Now, as far as I can remember, ValueListItems( filename, this list ) will return a list of teachers sorted by their rank.

Hi Mikhail,

I get errroneous results when I sort by the ranking field because it's only 1 TO away from Classes. IOW, if a teacher is assigned to more than 1 class, the teacher's name appears in the value list multiple times. Thanks for trying!

[Mikhail Edoshin]

Did you make the value list conditional starting in the Class TO? This way it will be showing only teachers of this class.

Edited August 25, 2006 by Guest
Fixed a typo

[Kent Searight]

Yes, I did.

[comment]

I am not sure mixing two fields from different tables in the same value list is very meaningful (to Filemaker). I am also puzzled why do you need to do the ranking on a class-to-class basis; shouldn't this be automatic by the teacher's status, stored in the Teachers table?

The way you have it now, I think your best bet is to go with Ender's suggestion and use a custom function. You will need an unstored calculation in the Assignments table, pulling the teacher's name from Teachers. Sort the Assignments (as viewed from Classes) by Rank. The custom function can be:

GetNValues ( fieldName ; startingValue ; endingValue )

GetNthRecord ( fieldName ; startingValue ) &

Case ( startingValue < endingValue ; ", " & GetNValues ( fieldName ; startingValue + 1 ; endingValue ) )

and you would call it as:

GetNValues ( Assignments::cName ; 1 ; Count ( Assignments::TeacherID ) )

That is unless Mikhail is holding something up his sleeve...

[Kent Searight]

...why do you need to do the ranking on a class-to-class basis; shouldn't this be automatic by the teacher's status, stored in the Teachers table?

It won't work that way because a teacher that is the main teacher in class A, may be the assistant teacher in class B. But, you did give me an idea on how to handle it with Mikhail's approach. I put an auto-enter calc field in Assignments to create the teacher's full name in that TO. Then I based the value list on that field and the ranking as the second field by which it sorts. It works, as does your method.

Thanks to all....now I have 2 ways to solve the problem :/

[comment]

a teacher that is the main teacher in class A, may be the assistant teacher in class B.

I realize that, but the question is can teacher X be the main guy in one class, with teacher Y as assistant, while in another class teacher Y comes before teacher X? Isn't everything relative?

Storing the name in Assignments means it won't update.

[Kent Searight]

I realize that, but the question is can teacher X be the main guy in one class, with teacher Y as assistant, while in another class teacher Y comes before teacher X? Isn't everything relative?

Yes, that can be and is the case in several classes. That's why the ranking can't be in the Teachers table and must be in the Assignments table. Hmmm. Now you've got me wondering...am I overlooking something? :qwery:

Storing the name in Assignments means it won't update.

You're right. Thanks!