AnsweredAssumed Answered

Multiple fields performing lookup on a single field in another table?

Question asked by psmthe on Feb 1, 2018
Latest reply on Feb 1, 2018 by philmodjunk

I'm trying to convert an existing project (recording and analysing student results) to FIlemaker from a solution in Excel which used a lot of vlookup() functions.

 

In my database I have numerous tables, including a RESULTS table and a GRADE_LOOKUP table. When I enter a letter grade (A,B,C,..etc) into RESULTS::LetterGrade, I would like the field RESULTS::NumericalGrade to update with the corresponding numerical value from the GRADE_LOOKUP table, making it easier for me perform calculations on these values (e.g. weighted averages, etc.). An example of the GRADE_LOOKUP table is below.

 

GRADE_LOOKUP

LETTERNUMERICAL
A+10
A9
A-8
B+7
......

 

 

I have been able to make this work by creating a relationship between RESULTS::LetterGrade and GRADE_LOOKUP::LETTER and then creating an appropriate lookup.

 

Problem

I am obliged to grade students on multiple different criteria so they might, for example, receive an A for writing, and a B- for math and an A+ for art, etc. A single assignment or test can assess multiple criteria at once and this is reflected in the structure of the RESULTS table, with a separate field for each criterion (C1, C2, C3... etc). An abbreviated version is shown below.

 

RESULTS

RESULT_IDpkSTUDENT_IDfkTASK_IDfkC1_letterC2_letterC3_letterC1_numC2_numC3_num
10221421ABB
10222431CC+B-
10223422A-
10224432B+

 

When I enter letter grades into one of the *_letter fields I would like the corresponding *_num fields to update with the corresponding numerical grade from the GRADE_LOOKUP table. In my previous Excel solution, this was achieved using the vlookup() function. I can't work out how to use a lookup in Filemaker to achieve the same outcome and would really appreciate advice on how to make this work.

 

Note: I was able to create a case calculation to get the same outcome, but this seemed a bit clumsy and not easy for a use to make changes if required.

 

Many thanks

Outcomes