Find largest value for field

[Peter Knowles]

I have a need to identify the largest numerical value in a single field among a set of records related to a single person, and don't quite know the best way to go about it. If you can help, it would be much appreciated.

Here's what I have:

A database "students" with all the students at our school (only one record per student)

A related database "scores" with test scores for those students over a number of years. Each record in this db represents a single testing session, with scores in three different areas (reading, writing, math)

Students who pass all three areas the first time through are done; they have only one record in "scores" related to their record in "students". (They're the easy ones!) Students who fail to pass all three areas will retake only the tests they didn't pass, up to 4 more times, so, theoretically, they could end up with 5 total related records, some with 3 different score fields filled in, some with 2, and some with only 1.

The master db "students" has a layout that pulls values from "scores" and, currently shows the FIRST value for each of reading, writing, and math. I want it to show the GREATEST value for each, which could, conceivably, come from two or three different records in "scores". The layout also shows which testing session (Spring 2008, Fall 2007, etc.) the score came from.

So...

I've thought about setting up a portal in "students" but that tends to throw off the layout's printable structures.

I've thought about creating a separate, join file, containing the layout I want, that could draw fields from both "students" and "scores", which would, I think, allow me to set up a global field that would pull the largest value from my test score fields, but then I have trouble controlling the number of records that the global field pulls from (only comparing scores from a single student at a time).

I'm sure there's a pretty simple way to do this, but I haven't found it yet.

Note: I've only recently begun using FMP 8, after doing lots of this stuff on FMP 6, and haven't quite figured out all the changes in relational options with the new software.

Thanks in advance for any ideas.

[comment]

You can see the maximum scores by placing 3 one-row portals on a layout of Students and sorting each by the respective score, descending. Alternatively, you could calculate the scores by using the Max() function over the relationship - but the portal method can also display other details from the same score record.

[Peter Knowles]

Thanks for the Portal suggestion. It's definitely the way to go, given my needs.

I've successfully set up a sortable portal and have all the related fields showing the way I want....

EXCEPT a few of the student scores are not numbers, and they're throwing off the sorting capabilities for those records. For example, a student who missed the first testing session due to an absence has "AB" in his/her score field. Next time through, if he/she passes, I want that number to show up. Instead, it seems to sort the alphabetic entries ahead of the numeric. The field being sorted is set as a number, but I also tried it set as a text field, and the result is the same. Any text in the field sorts above any numeric value, which is not what I want.

I suppose I could set up a new calculation field to place a zero in place of any non-numeric score, and then it would likely sort correctly, but that seems like a cumbersome solution.

The instances where all the values are numeric work great! Fantastic solution. Almost there. Any more ideas are welcome.

Thanks in advance

[comment]

Well, the thing is that "AB" is not a number. And it's not a score either. It's the reason why there is no score. So the right thing, IMHO, would be to set this up according to the "one fact per field" rule to begin with, rather than try to patch it up with a calculation.

[Peter Knowles]

Of course. This is even simpler, and it works great. Thanks.