Sorting in multiple fields

[Dudley Dufort]

I'm looking for a way to preform a sort in multiple fields within a single record.

[AudioFreak]

Ok I'm in....How can you sort a single record? I think I'm missing something.

[Dudley Dufort]

Example. I want to find the best seven of nine monthly contest scores for each contestant. So I must "rank" the monthly scores from high to low. I'm looking for an easier way to do this than I have now. Currently I run a comparison calculation on each field that's about a mile long to ascertain which field/month is greater than the rest. I keep thinkin' "there must be an easier way to do this".

[Ender]

If your scores were entered into a portal of related Scores, you could have another portal or List View that's sorted by Score.

[AudioFreak]

The scores aren't entered in one record in a repeating field are they?

[Dudley Dufort]

No. They are "pulled in" from related files of the individual monthly contests, via a calculated field. We have awards for the monthly contests. Hence, the individual (monthly) files. Those monthly files are related to the "Year to Date" file. The year end calculations are preformed in the "YTD" scoring program. That's where the sort comes in. All the scores for all the contestants for the nine months are pulled into the YTD program. A complicated sort or ranking is preformed then the highest seven of the nine scores are averaged for the year end season awards. I'm trying to find if someone has found or developed a shorter way to do the sort other than a long series of comparitive statements. Here's how it runs; If A>B and A>C and A>C and A>D etc., etc., etc., then A is the greater number in the set. That evaluation is run for every contestant for every month. Like I said. there must be and easier way

[Ender]

No. They are "pulled in" from related files of the individual monthly contests, via a calculated field. We have awards for the monthly contests. Hence, the individual (monthly) files. Those monthly files are related to the "Year to Date" file.

You make it sound like you have a separate FileMaker file for each monthly contest. This would be a poor structure for a relational database. You should only have one table for each entity.

If you change your design to a normalized structure, this present problem should be much easier to solve.

[Dudley Dufort]

Thanks for your input. I guess I'd better just leave "well enough alone". Because we have a multitude of contest "foremats" one size doesn't fit all. The contest foremats vary from month to month. I have several "boiler plate" formats written and I'll tweek them to suit the objective or whims of the contest director for that particular month. I'd kinda like to know where you were going with your "all tables in one file" concept and how the sort would be preformed in that context.

[Ender]

I'd kinda like to know where you were going with your "all tables in one file" concept and how the sort would be preformed in that context.

You've mis-read me. I said one table per entity. That means one table for Contests, one table for Scores, one table for Questions, one table for Contestants, etc, etc. The number of actual files used to make up the solution is not relevant to the relational structure.

The differences in the contests from month to month is something that should be recorded as data within fields.