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.
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.
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.
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.