Exactly how did you relate your tables. Are the two exam (entry?) levels stored in separate tables? (I might put both in the same table.)
There are several ways to get what you want but the option that will work for you depends on the relationships and tables you've set up.
The exam entry data is currently in separate tables (I thought that was the "proper"way of doing it!); one table for each science (bio, chem and phys) at each tier - six tables in total
I have the exam scores linked to the marks in each table (MarkF <> Exam Mark <> MarkH, where F and H denote tier). I have since thought of putting them in the same table but the grade boundaries aren't straight forward, coming directly from the exam board. This means that each exam has a different set of grade boundaries; grade C could be different for each of the six exams. To get around this the exam board converts the marks to a "Unified" score out of 100 ... but there is some arcane rite involved in doing this which we have no access to!
Any ideas would be gratefully looked at, although I can see Excel coming out at some point :(
I can't follow from that post exactly how you have linked your tables in relationships. If you can list them like this:
TableName::FieldName = RelatedTableName::FieldName
It would help others understand your set up.
New users often organize data into separate fields when they could, instead enter data into a unified table and then use relationships, finds, sorts and portal filters to control which sub set of the total data is displayed and/or accessible for editing. The unified table is more flexible, overall as you can simply change what data is entered in your table if you find you need to add or remove subjects/levels where in your current approach, you'd have to modify the structure of your database by adding or removing tables--as well as making changes in the layouts that refer to them.
Granted, this is not an iron clad rule and I don't really know what you mean by "each exam has a different set of grade boundaries", though that sounds like something that can be handled fairly easily in a unified table.
Don't see why you can't boil a lot of this down into two tables, One for your Grade boundaries and one for your student exam scores. A third table would, of course, list all the students.
Your ExamScores table would then include the necessary date, subject and level information in additional fields so that you can properly link this record to the appropriate Exam Boundaries record.
I've been re-reading your original post and trying to guess the context of this statement: "my problem is the lack of "if this value is not found use the lower value" as seen in a normal lookup field."
With a field set to use a looked up value setting to copy values from a related table, you have the option to copy the next higher value or the next lower value as standard FileMaker features.
There are also ways to match related records with inequalities so that it can match against a range of values in the related table.
Sorry for the delay in responding; I hope you have had a good long weekend too :)
Looking at your ideas I can see how to get the boundaries in one table - a field for each exam and one for the scores. However, it appears that I would have to enter all the possible scores (from 0-45) to account for the way the grade boundaries work, with the grade for eah score next to it... I still miss some automatic "fill down" function at times! Time to play again.
With reference to "With a field set to use a looked up value setting to copy values from a related table, you have the option to copy the next higher value or the next lower value as standard FileMaker features." although this feature is standard in a lookup field I could not find it when using a function such as
condition1; Lookup(sourcefield1; etc
(yes - it needs work, but it's the idea I'm demonstrating)
Thanks again for the help and advice!
What I'm getting at, is that I don't see the need at all for your Case Function calculation if you structure your data and relationships correctly. I think you can set this up with inequlities to match a range of values to a given related record to avoid the need to enter in "all possible scores".
Say you have this table, using classic percentages for "Letter Grades" just to demo the concept:
Lower Boundary Upper Boundary Letter Grade
90 100 A
80 89 B
70 79 C
60 69 D
0 59 F
A relationship from a "scores" table could match to values in this "LetterGrades" table like this:
Scores::PCT < LetterGrades::UpperBoundary AND
Scores::PCT > LetterGrades::LowerBoundary
On a Scores Layout and other layout to tables correctly related to scores, you can simply place the Letter Grade field from LetterGrades on your layout and you'll see the correct Letter Grade for any possible percent score.
This concept can be applied to much more sophisticated situations such as yours by adding additional fields to the table and relationship so that you match to a given record on the basis of subject and level as well in addition to the upper and lower boundary matches.