12 Replies Latest reply on Mar 30, 2012 9:42 AM by Stephen Huston

    Portal through an intermediate table

    pashtech

      I have a feeling this is an instance where a self-join might be my best bet, but I'm hoping otherwise.

       

      This is a school report card database that needs to have transcript functionality.

       

      PARCS.jpg

       

      I realize this looks kind of crazy, but for right now I'm concered with Students, Enrollments, and RCData.

       

      Students is going to list every student at the school, both active and graduated (up to a point). Enrollments is a join table of a student to a given course in a specific school year. So every student should, with a full course load, have 9 enrollment records per year.

       

      RCData tracks grades per marking period, of which there are 3 per year. In other words, every year, a student will have 9 enrollments and 27 RCData records. Teachers are sent to a filtered list of blank RCData records for grade input.

       

      The issue is that for report card and transcript generation, I need to be looking at RCData records vis-a-vis Students. However, when I make a portal from Students to RCData, there is no direct relationship, and so the appropriate data do not display.

       

      My current thoughts are:

       

      • Do away with the the RCData table altogether, and combine it with the Enrollments table. However, I would prefer to keep them separate, so I don't have redundant fields in the Enrollments table.
      • Attempt a cascading portal setup, where RCData records display on a Students layout depending on the active portal row in a portal to Enrollments.
      • A self-join of RCData accessed by a GTRR operation from Students/Enrollments.

       

      Any help would be greatly appreciated.

        • 1. Re: Portal through an intermediate table
          comment

          pashtech wrote:

           

          I need to be looking at RCData records vis-a-vis Students. However, when I make a portal from Students to RCData, there is no direct relationship, and so the appropriate data do not display.

           

          Why not? I see that you do have a chain of relationships:

           

          Students -< Enrollments -< RCData

           

          so a portal to RCData placed on a layout of Students should display all of student's grades. If you want to narrow it down to a selected enrollment only, you will first need to select an enrollment somehow - either in a global field or in a variable. If you're using version 11, you can then employ portal filtering; with previous versions you'll need to establish a dedicated relationship filtered by a global field.

          • 2. Re: Portal through an intermediate table
            pashtech

            Here's what I'm observing:

             

            I make a portal from Students -> RCData. I include RCData::MarkingPeriod, RCData::LetterGrade, and Courses::Name (So I can identify what the grade is for.) And indeed, I see the correct number of grades. However, every grade shows the same course name. Changing the Courses::Name field to the Enrollments::__Course_FK confirms that it is looking at the same Course record for each RCData record in the portal.

             

            Looking at the RCData records when on an RCData layout, the records will display the correct related Course name and FK. That inability to relate to course is my primary issue.

            • 3. Re: Portal through an intermediate table
              comment

              pashtech wrote:

               

              I see the correct number of grades. However, every grade shows the same course name.

               

              There is a simple cure for this: define a calculation field in RCData =

               

              Courses::Course_Name

               

              and place it in the portal instead of the field from the Courses table.

              • 4. Re: Portal through an intermediate table
                comment

                BTW, for "report card and transcript generation" you should probably be working with the RCData records in their own table anyway.

                • 5. Re: Portal through an intermediate table
                  pashtech

                  That will be tricky for certain operations, but I will definitely do that whenever possible.

                   

                  The calculation solution does work, but I am still curious as to why the portal couldn't display the correct Course FK.

                  • 6. Re: Portal through an intermediate table
                    comment

                    Because the portal is to RCData - and data from any intermediate TO is taken from the first related record. Keep in mind that in a chain of

                     

                    A -- B -- C

                     

                    a record in C could be related to a record in A by more than one record in B, so ...

                     

                     

                    The situation is different with TOs placed behind the portal TO. For example, if you had:

                     

                    Students -< Enrollments -< Grades >- Enrollments 2 >- Courses 2

                     

                    a field from Courses 2 placed into a portal to Grades would be correctly associated with the grade in the same portal row.

                    • 7. Re: Portal through an intermediate table
                      pashtech

                      Ah right; of course. Thank you very much!

                      • 8. Re: Portal through an intermediate table
                        Stephen Huston

                        Another way to picture the problem of pulling Course name via the RCdata is that you are still reading starting from the student table, so the connection from Student to course name returns the first matching value in all records. Another way to pull the course name accurately (Michael's calc idea is fine) would be to create an additional TO for the course from which to pull the name:

                         

                        Student > Enrollment > RCdata > Course

                         

                        In that case the Course name is pulled via the RCdata record and would be accurate.

                         

                        Just another way to do the same thing with a relationship instead of a calc.

                        • 9. Re: Portal through an intermediate table
                          comment

                          Stephen Huston wrote:

                           

                           

                          Student > Enrollment > RCdata > Course

                           

                          Matching on ??

                          • 10. Re: Portal through an intermediate table
                            Stephen Huston

                            Always matching on Primary and Foreign Keys, of course.

                             

                            My point was that a 2nd TO for Course which was related through the RCdata table would also return the correct course name in the portal.

                            • 11. Re: Portal through an intermediate table
                              comment

                              Stephen Huston wrote:

                               

                              My point was that a 2nd TO for Course which was related through the RCdata table would also return the correct course name in the portal.

                               

                              I thought that was my point...

                               

                              Is there some game going on here that I am missing?

                              • 12. Re: Portal through an intermediate table
                                Stephen Huston

                                Of course this requires that a foreign_Key be looked up into RCdata for the Course record, which can then be used for establish this secondary relationship. It can be used to pull/view data from any field in the Course, which gives it more flexibility for other uses than just returning the course name via an unstored calc.

                                 

                                If one is sure all they will ever want from the Course is the name, one could lookup and store the course name in RCdata instead. The stored lookup would be more efficient for performance than an unstored calc.

                                 

                                Sorry if my response at the end of the day sounded glib. I hadn't thought thru the issue of having a foreign_Key field for course looked up and stored in RCdata. I routinely do that kind of fKey lookup for additional relationships, but realized after responding that there was nothing indicating this was already being done.

                                 

                                Mea Cupla