Using Case formula with table record list

Question

Here's what I am attempting to do in a very specific way:

We have kids in classes [class] that correspond to their academic grade [grade]. Rather than using a hard-coded CASE formula that would read:
case(
[grade],"6","Jr. High",
[grade],"9","Sr. High")

...we'd like for Case to look up that grade in another table and return "Jr. High"

The parallel "Classes" table is in our data app so that the people using it (Church Secretary) can easily add to this list without having to edit the field attributes.

What would be the best way to approach this type of lookup?

------------------------------
Ryan Buschmeyer
------------------------------

MarkShnierYou · Answer

What you want to have is a New Table of Grades and their Name. Set the Key field of the table to be the Grade #. and enter all the possible Grades and their Names. Make sure the Grade field Type (tecxt vs numeric) matches the field type of how you currently enter the Grades on the student record.

Then make a relationship where One Grade has many Students and for the field which connects the two fields, select your current Grade field. Do not let the system choose the first lookup field for you at this time. Then go back in to the Relationship and add a lookup field for the Grade Name.

Send note to Leonard initially offer the first look I feel for you is because that would become which would be called the reference proxy for the grade feel that in that case it would be difficult to see the grade number on the student form. Because the Grade number has information value as well as a Grade Name, you want them both to show on the form.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community