5 Replies Latest reply on Jan 11, 2011 9:45 AM by philmodjunk

    DataSeparated portals sporadic in showing data

    MatthewMiller

      Title

      DataSeparated portals sporadic in showing data

      Post

      I have a database that contains student information.  One of the items I attempted to create is a schedule.  This was a bit tricky for me, as it involved creating a fairly deep cascade of buoy TOs in order to show all the data desired.  Originally, it worked fine to create the schedules.  However, now that I'm going back and trying to print schedules on a one-off basis as changes are made, it sometimes works and sometimes doesn't!  When it doesn't, I get a page full of blank portals, as if the global student ID field were left blank (though it isn't).  
      Here's the structure:


      In the data file I have tables w/ the following fields (non-relevant fields excluded for brevity, let me know if you think they're necessary):

      STUDENT - StudentID

      COURSE - CourseID, Title, Room, SchoolYear, TermMajor

      COURSEENROLLMENT - CourseEnrollmentID, CourseID, StudentID, SchoolYear, Term_Major (auto-lookup from Course)

      BLOCK - BlockID, Title, gConstMon, gConstTue, gConstWed, gConstThu, gConstFri

      COURSE2BLOCK - CourseID, BlockID

      BLOCKTIMESLOTS - TimeslotID, DayOfWeek, StartTime, EndTime


      Then in the presentation file I have a globals table with a field g_Selection_StudentID.  Based on this, the anchor-buoy TOG for the report in question is:

      1   g1_  - this table is the global table in the Presentation file, containing g_Selection_StudentID

      2   g1_STU (TO of Student) - link g1_::g_Selection_StudentID = g1_STU::StudentID

      3   g1_stu_ENR (TO of CourseEnrollment) - link g1_STU::StudentID = g1_stu_ENR::StudentID, g1_STU::g_CurrentSchoolYear = g1_stu_ENR::SchoolYear, g1_STU::g_Current_TermMajor = g1_stu_ENR::Term_Major

      4   g1_stu_enr_CRS (TO of Course) - link g1_stu_ENR::CourseID = g1_stu_enr_CRS::CourseID

      5   g1_stu_enr_crs_c2b - link g1_stu_enr_CRS::CourseID = g1_stu_enr_crs_C2B::CourseID AND 

      6   g1_stu_enr_crs_c2b_BLK (TO of Block) - link  g1_stu_enr_crs_C2B::BlockID = g1_stu_enr_crs_c2b_BLOCK::BlockID

      7   g1_stu_enr_crs_c2b_blk_BLKtsMON (TO of BlockTimeslots) - link g1_stu_enr_crs_c2b_blk::BlockID = g1_stu_enr_crs_c2b_blk_BLKtsMON::BlockID AND g1_stu_enr_crs_c2b_blk::gConstMon = g1_stu_enr_crs_c2b_blk_BLKtsMON::DayOfWeek

      8   g1_stu_enr_crs_c2b_blk_blktsmon_BLK (TO of Block) - link g1_stu_enr_crs_c2b_blk_blktsmon::BlockID = g1_stu_enr_crs_c2b_blk_blktsmon_BLK::BlockID

      9   g1_stu_enr_crs_c2b_blk_blktsmon_blk_B2C (TO of Course2Block) - link g1_stu_enr_crs_c2b_blk_blktsmon_Blk::BlockID = g1_stu_enr_crs_c2b_blk_blktsmon_blk_B2C::BlockID

      10 g1_stu_enr_crs_c2b_blk_blktsmon_blk_b2c_COURSE (TO of Course) - link g1_stu_enr_crs_c2b_blk_blktsmon_blk_B2C::CourseID = g1_stu_enr_crs_c2b_blk_blktsmon_blk_B2C::CourseID

      Line 7 to 10 are repeated for each day of the week (BLKtsTUE, for example, matching all DayOfWeek fields with 'Tuesday', using Block table field gConstTue).  

      The verbal logic of this sequence would be:Select a specific student ID (g1_)Select the student with that ID (STU)Select the current year and term's enrollment records for that student (ENR)Select the courses defined by the current enrollment records (CRS)Select the blocks for each of the selected courses (C2B, BLK)Select the timeslots in which each block occurs (BLKts~~~) where ~~~ is Mon, Tue, etc.

      Finally, after all that, I have a layout which contains 5 portals, each of which show one of the days of the school week, using the relevant TO set from the above TOG.   Within these portals, that day's timeslots are listed, sorted by start time, and showing the start time, title, and room for the related course.


      Unfortunately, both via script and manually, some of the time when I select a student ID and switch to this layout, I get a blank set of portals more than half the time.  I haven't been able to identify what the factor is when I DO get data.  <frown>


      Is there something I should start looking at?  Or if there is a simpler way to accomplish this, either by rearranging the tables (shudder) or TOs, I would like to know that, too.  I hope I've provided enough information; if not, let me know what I've left out and I'll report.  


      Any suggestions gratefully appreciated!  

      Screen_shot_2011-01-11_at_8.01.49_AM.png

        • 1. Re: DataSeparated portals sporadic in showing data
          philmodjunk

          It's difficult to follow all that without an actual screen shot of the relationship graph or an ER diagram.

          Question, do any of these portals use a portal filtering expression specified in Portal Setup... (This is a feature new with FileMaker 11.) If so, do you use a script with Refresh Window [Flush Cached join results] to update the filtered portals each time a field or variable referred to in the filter expression changes?

          • 2. Re: DataSeparated portals sporadic in showing data
            MatthewMiller

            Thanks Phil!  I've attached a screenshot of the relationship graph.  I use FM9, so no portal filtering possible for me, yet.  

            I do have a script step in my main script for this layout that refreshes the window w/ flush cache (both join and SQL), as well a utitily script to do that when I've manually selected a student; neither seems to work consistently.  That's what's most frustrating about this to me - when the failure is consistent, I can usually figure out what the issue is; this inconsistent result is really bugging me because I can't isolate when it's working vs when not, so I'm not sure what to persue as a potential solution.

            • 3. Re: DataSeparated portals sporadic in showing data
              philmodjunk

              Finally, after all that, I have a layout which contains 5 portals, each of which show one of the days of the school week, using the relevant TO set from the above TOG.   Within these portals, that day's timeslots are listed, sorted by start time, and showing the start time, title, and room for the related course.

              On what TO is the layout based? (Show Records From in Layout Setup...)

              • 4. Re: DataSeparated portals sporadic in showing data
                MatthewMiller

                Sorry, should have caught that myself!  The layout is based on the g1_ TO.  

                • 5. Re: DataSeparated portals sporadic in showing data
                  philmodjunk

                  That leaves you with something like 5 TO's between G1 and your portals. That's where you likely have a problem. Every record in every data-source table referred to by these TO's must have records that keep the record in g1 linked to the records in your Portal's data source table If even one record is missing or has the wrong value in a key field, the link is broken and you may get an empty portal.

                  It's also possible that you are hitting some kind of limit into how far FileMaker can "tunnel" through the TO layers.

                  I make it a practice to never put fields/portals on a layout that are more than one (many to one, one to one, one to many) or two TO's ( Many to Many) away unless I have no choice.

                  If I were you, I'd take a very careful look at my structure here and see if I can't recreate this layout without such an extensive chain of TO's between layout and portal.

                  You might use a find or GTRR that takes the data you've specified at the G1 TO and finds the related records for a table much closer to the portal TO's and then you place your portals on a layout based on a TO for that table instead of one based on G1.