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

LETTER | NUMERICAL |
---|---|

A+ | 10 |

A | 9 |

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_IDpk | STUDENT_IDfk | TASK_IDfk | C1_letter | C2_letter | C3_letter | C1_num | C2_num | C3_num |
---|---|---|---|---|---|---|---|---|

10221 | 42 | 1 | A | B | B | |||

10222 | 43 | 1 | C | C+ | B- | |||

10223 | 42 | 2 | A- | |||||

10224 | 43 | 2 | B+ |

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

You'll need to remind yourself that FileMaker is not a spreadsheet and thus your options for doing these things differ.

In your place, I'd consider not having each of these criteria in a different column. If you used separate records where you have multiple columns/fields, your look up becomes very simple.

With your current design, you might use multiple occurrences of your look up table that each match on a different field in order to facilitate the needed look ups. You might use ExecuteSQL queries to auto-enter the needed value or even a script that finds the matching value and sets the field.