Sort to Separate List

[ChrisMax]

Hey Everyone!

I'm new here and am hoping that someone may be able to provide me with a solution.

I have a database that I'm developing for a touring dance convention. The main reason for the database is to quickly sort the competition (where studios bring routines they choose to compete) so that there is the maximum time between routines that studios compete so that the performers do not have quick changes.

Here's the Structure:

1) A Studio Registers all of their dancers and their teachers who are attending.

2) The dancers and teachers are all linked to the studio thru studioID

3) Multiple dancers are assigned to various routines (a single dancer can compete many routines)

4) A teacher (choreographer) is assigned to the routine.

Right now I've got a layout that properly displays the competing studio, the name of the routine, the age category (3-5 yo, 6-10yo, 11-14yo, 15+), the style (lyrical, ballet, tap, jazz), the routine type (solo, duo/trio, small group, large group etc), then two list portals... 1) list of all the dancers who are performing in the routine and 2) list of choreographers for the routine (usually one but could be more)

What I'm trying to do is sort the competition list in a special way:

1) Sort Ascending the Age Category so that the 3-5yo's are together, the 6-10's, 11-14s etc.

2) Then sort the order by studio so that studios 1,2,3,4 are not in 1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4 order but 1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4 giving the studio's more space in between each number

3) Finally sort the list again based on the dancers competing in the routine. For example... if there are 2 large dances that have 15 dancers in each of them.. they don't end up next to eachother... the sort would put a duo/trio in between the large groups so that there aren't many fast changes.

I have the database available if anyone is interested. Is this even possible with FileMaker or do i have to look to an AppleScript to accomplish this?

[comment]

This is not an easy problem - not just in Filemaker, but in general.

One way to do this is to assign each record a position in its group. Then sort the records by position first, group second. But if one group is significantly larger than others, no good solution exists.

I cannot find the previous thread that dealt with a very similar question, but here's a link to the demo file I have posted then.