Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Lookups with Case

Question asked by misterg65 on Dec 22, 2010
Latest reply on Dec 27, 2010 by philmodjunk

Like • Show 0 Likes0
Comment • 7

Title

Lookups with Case

Post

I've got two exams at different levels. The grades depend on which level the student took (A-D or C-G). Currently I have the ID, name, exam level, and mark. I have "lookup" tables for both entry levels. I'm trying for the following.

I can get the basic idea sorted but my problem is the lack of "if this value is not found use the lower value" as seen in a normal lookup field. Having two separate tables for the level is not really an option. I could use Excel, but ... :)

Thanks in advance

Zippy

No one else has this question

Outcomes

7 Replies

philmodjunk Dec 22, 2010 9:04 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
misterg65 Dec 22, 2010 10:35 AM
Mark Correct
Correct Answer
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 :(

EDIT: Link to relationship diagram here
Like • Show 0 Likes0
Actions
philmodjunk Dec 22, 2010 10:34 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
misterg65 Dec 22, 2010 10:43 AM
Mark Correct
Correct Answer
You must have posted this while I was editing my post :)

This is a link to my relationship diagram

I'm also adding a link to the grade boundaries. Admittedly I will not be using ALL these (only the BLY1H and F, CHY1H and F and PHY1H and F) but it might help in seeing what I am up against :(
Like • Show 0 Likes0
Actions
philmodjunk Dec 22, 2010 11:04 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
misterg65 Dec 27, 2010 2:52 AM
Mark Correct
Correct Answer
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
```
case(
```
```
condition1; Lookup(sourcefield1; etc
```
```
(yes - it needs work, but it's the idea I'm demonstrating)
```
Thanks again for the help and advice!
Like • Show 0 Likes0
Actions
philmodjunk Dec 27, 2010 8:34 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions

Lookups with Case

Title

Post

Outcomes

Related Content

Recommended Content