3 Replies Latest reply on Nov 15, 2014 6:03 PM by AjEGfmTech

    Display Specific Related Record Data



      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.

        • 1. Re: Display Specific Related Record Data

          I presume that no responses may mean that what I'm looking to accomplish is just not possible? I guess I will have to look into using filtered portals instead.

          • 2. Re: Display Specific Related Record Data

            It can be done with relationships, but not via your case calculations.

            When you refer to a field from a related table in a calculation, it only refers to data from the "first" related record. If there is more than one related record, data from the other records might as well not exist.

            To access data from a different record, you either need a relationship that matches to that record as the first or only related record, a SQL query in the ExecuteSQL function call or, as you have mentioned, by using a filtered portal.

            • 3. Re: Display Specific Related Record Data

              Thanks Phil. I had found a response you made in another post about using a relationship to show the desired records. That's what I ended up doing. I created a new calculation field where the result was "1." and then created a new TO that had 'SortID' = 'that calc field' as an additional relationship to the primary key = foreign key. I was then able to show the CategoryGrade field from that TO and it showed the field data of a specific related record in a parent field. It worked just fine. Thanks Phil.