3 Replies Latest reply on Nov 4, 2010 10:30 PM by wonderbike

    Trouble with sort order


      Trouble with sort order



      I'm relatively new to Filemaker. I'm using Filemaker Pro 11 on Windows Vista.

      I work for a university, and I am trying to create a database of our faculty members. Each faculty member may have multiple job titles. For example, I might have 

      Clark - Department Head, Professor

      Adams - Professor

      Graham - Associate Professor

      To keep consistency in the titles, I have a separate table with all the job titles and their appropriate ranking, and then link the faculty to those titles with an intermediate table.

      The problem comes when I try to sort the faculty in order of job ranking. If I sort by job rank and then by name, what I want to get is:




      in that order, since Clark outranks Adams as the department head. What I get is:




      I'm not totally sure what is happening, but it seems like Filemaker just ignores the department head title and sorts the professors alphabetically. It is only an issue with faculty members who have two or more job titles, but unfortunately we have quite a few of them (various department heads, deans, etc.), and some faculty have up to four titles. Is there a workaround for this? Am I going about it all wrong in the first place?

      Thank you in advance for any help you can give me.


        • 1. Re: Trouble with sort order

          It sounds as if your structure is correct. Three tables, Faculty, Titles, FacultyTitles (join table), with the Ranking field also entered into the join table. Links via IDs (hopefully). The problem is that you are sorting via a relationship to a join table which can have multiple entries per person. I'm not sure what it does in that case.

          What you need is a calculation field in the Faculty table. It would be either Max (FacultyTitles::JobRank) or Min (same), depending. The Max() or Min() function, when used thru a relationship, will return the highest or lowest value of the targeted field of the (possible) multiple records targeted.

          If there are a lot of people, and you want your Sorts to be fast, it would be best to keep and maintain this number in a fixed field in Faculty. This can be done by creating a field for it, then adding a step to set it in whatever script (in any) is involved in choosing the Title in the join table, and/or attaching a script to anywhere it can be changed manually. It must always run on a change to the relevant fields in the join table. You may then need a self-relationship in the join table, on FacultyID, if you wanted to evaluate within the join table itself. Or you could flip to a Faculty layout, evaluate it from there, then flip back to wherever you started from. If you reordered the Ranking in Titles, you'd need to redo everyone affected. So yeah, a lot more work, but much faster Sort.

          • 2. Re: Trouble with sort order

            You might also be able to define a sort order on your relationship so that the highest ranking title is the first related record. In that case, I believe your sort will work correctly for you.

            • 3. Re: Trouble with sort order

              Thank you both for your replies.

              The calculation field ended up working great. They are all in the right order now.

              Thank you again!