Display Specific Related Record Data
I am trying to find a way to show the field data of a specific related record in a parent field.
Tables: Visit --> Category
Each Visit will have several Categories. Each Category record will have a 'SortID' field and a 'Grade' field. On the Visit layout I would like to display the 'Grade' for each of the Categories (there are typically only three).
I thought I might accomplish this by creating a calculation field in the Visit table like this...
Visit::CategoryGrade1 is defined as: Case ( Category::SortID = "1." ; Category::Grade )
Visit::CategoryGrade2 is defined as: Case ( Category::SortID = "2." ; Category::Grade )
I figured that each Category is related to a single Visit, so if I used 'Case' to define the SortID, it would show the Grade data from that record. Kind of like, "If the SortID of one of the related Category records = "1.", then show the 'Grade' data from that record."
Unfortunately, this didn't work past the first record. I cannot get the "2." or "3." records to show.
Any ideas to display this would be appreciated. I presume I could probably create three filtered portals, but I would like to see if there is a way to do it with some Visit record fields instead, if possible. Thanks for any help.