7 Replies Latest reply on Feb 10, 2012 9:27 AM by philmodjunk

    Problems creating a relationship that will display a weekly schedule

    veramilo_1

      Title

      Problems creating a relationship that will display a weekly schedule

      Post

      I'm working on a db to track attendance, among other things, for a small tutoring business.  I need to be able to print out a weekly schedule (roll call) so attendance can be taken and students rescheduled since the computer is usually unavailable.  At the end of each day, the database is to be updated from the printed schedule.

      In my system, I have a table named SESSIONS that has one record for each session that a student has scheduled (whether they attend the session or not).  It has fields such as student_ID, session_date, session_time, and attendance_status. There are 3 possible sessions a day (3PM, 5PM, & 7PM, Monday thru Friday), and students generally attend the same session every week, but that can vary.  We usually schedule students for an entire month in advance.  Class sizes fluctuate, but if, for example, I had 5 students in every class for one month, there would be about 300 records in the table; for a year, it would be about 3,900 records.

      I created a layout with 15 portals (10 rows showing in each): there's one portal for each of the 3 sessions on each of the 5 weekdays (5 columns representing weekdays, 3 rows representing each daily session).  Each portal has a filter applied so only the names of the students signed up for THAT session of THAT day of the week should display in that particular portal. 

      I would like the schedule to only display records for sessions taking place during the current week so I created a calculation field, cur_week_sessions, that shows "1" for all records where the value of session_date is within the current week range and is empty for the rest of the records.

      My problem is that I can't figure out how to create a relationship to display the current week of records nor which relationship to assign to the layout and which to the portals.  I've made dozens of attempts: usually the portals are blank, other times they repeat one student's name and nobody else.  It's been horribly frustrating because I DID get it to work a couple months ago, then accidentally destroyed the relationships and never realized it until it was too late to restore it!

      Can anyone point me in the right direction for creating this relationship?  I hope I have described the relevant parts of the system adequately because, unfortunately, I cannot post any files, but I can post some screen shots, if that helps.  BTW: I suspect I may also be doing something wrong with my index setting options since I don't fully understand their purpose, so please specify where that matters as well.

      THANK YOU!!!!!

      Vera

      PS: I'm using FMP 11, Advanced

        • 1. Re: Problems creating a relationship that will display a weekly schedule
          philmodjunk

          A cropped screen shot of Manage | Database | Relationships is often helpful in describing the design of your database. Once in a while it's helpful to share the entire file. In those cases, you can upload the file to a file sharing site and then include the download link in your file.

          Here's a calculation you may find useful, learned it from a guy nameed Comment who is active in other forums: DateField - DayOfWeek ( DateField ) + 1

          It returns the date for Sunday of the same week as the date in date field so it gives you a common value for all dates in the same week.

          Let ( D = Get ( CurrentDate ) ; D - dayofweek ( D ) + 1 )

          Will produce the same "Sunday date" but for today's date provided you use it in an unstored calculation field.

          Thus, you can set up relationships such as:

          SomeTable::cThisWeek = Sessions::cWeek

          I am using the table name "some table" as any table will work here for this relationship provided cThisWeek is the second calculation shown and cWeek is the first.

          • 2. Re: Problems creating a relationship that will display a weekly schedule
            veramilo_1

            Thank you, Phil.  I will study this over the next few days and see what I can make of it.  The Let statements sometimes lose me.

            Do you know if or why flagging records for my current week sessions with a "1" (calculation) and then creating a relationship from another table with a "1" (calculation) field wouldn't work?   Oftentimes understanding the reason helps keep me from making the same mistake again.

            BTW: Apart from it being very large, I'm not able to upload my file becasue it contains our customers' personal info and I just don't have the time to replace it with workable dummy data -- especially when there's no guarantee of it getting me any closer to a solution (I've learned that the hard way!).  But if I still can't get it to work, I'll try posting a screenshot of the relationship graph.

            Thanks again!

            • 3. Re: Problems creating a relationship that will display a weekly schedule
              philmodjunk

              Likely, that calculation field is unstored. If so, it won't work as a match field in a relationship between tables if it is on the "many" side of the relationship. It would work, however, if you performed a find on that table or set up portals with portal filters and referred to that field in the filter expression instead of the relationship. Unstored calculations used in such manner, however, will result in slower performance than stored calculation fields so I have recommended a calculation that puts the unstored calculation on the "one" side of your one to many relationship.

               Here's the same expression without using the let funciton:

              Get ( CurrentDate ) - dayofweek ( Get ( CurrentDate ) ) + 1

              It's a tad less efficient as it has to evaluate get ( currentDate ) twice and takes a bit more typing, but produces the same result.

              • 4. Re: Problems creating a relationship that will display a weekly schedule
                veramilo_1

                Yes, my calculations are unstored.  Can you tell me the logical or practical reason why FMP doesn't let unstored calculations work for some relationships?

                I am going to revisit creating foundsets for the schedule, as well as trying to better understand "Let" if that is the better way to do the calculation.

                Thank you.

                • 5. Re: Problems creating a relationship that will display a weekly schedule
                  philmodjunk

                  Unstored calculations do not have an index. An index is a data structure the database engine uses to find and sort records in the database. In a relationship when you have Parent----<Child relationship, it's the index that enables the database software to determine which records in child match to a given value in the parent record. Since the match is from "field value" to index, the match field(s) in the child table must be indexed, but the field in Parent does not.

                  • 6. Re: Problems creating a relationship that will display a weekly schedule
                    veramilo_1

                    I'm will check the storage settings on my match fields.  This may have been something I mistakenly changed before my relationship broke.  Thank you.

                    • 7. Re: Problems creating a relationship that will display a weekly schedule
                      philmodjunk

                      Some calculations, by definition, cannot be stored because the either reference other unstored fields or reference fields in related tables.