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

markshnier__you · 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
------------------------------

ryanbuschmeyer · Answer

Thanks, Mark. Yeah I'll give that a shot.------------------------------Ryan Buschmeyer------------------------------

ryanbuschmeyer · Answer

Well, we had to do a different thing (slightly). Since we have multiple academic grades assigned to a single class, reporting would have been messed-up. What we did instead was build a table for the Classes (using the Record ID as the key), then built a pipeline to do the automated relationships for us based on user-input of the academic grade level.

This gives us the flexibility to do proper reporting on the other end (who is in this class?).

Thanks for the suggestion though, it ended up being the key to getting the eventual correct solution.

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

Forum Discussion

Using Case formula with table record list

3 Replies