3 Replies Latest reply on Feb 27, 2015 7:53 PM by philmodjunk

    Table Relationship for Calendar



      Table Relationship for Calendar


      I've got a tricky table relationship question.  I'm developing a calendar layout.  I have an Events table with 2 records with StartTime and EndTime and I have a "CalendarHours" table with 24 records (one for each hour).  I want to create a relationship like the following:

      Event1 StartTime = 4am  EndTime = 6am
      Event2 StartTime = 5am  EndTime = 6am
      Event3 StartTime = 8am  EndTime = 9am

      I want my resulting table to have 24 records, the 4am record should have a list field containing Event1, the 5am record should have a list field containing Event1,Event2 and the 8am record should have a list field containing Event3.

      I've done lots of simple relationships before but have no idea where to begin on this one.

      Thanks for your help!


        • 1. Re: Table Relationship for Calendar

          I think you have a typo. Don't you want an end time of 5 am for the first row?

          I am not clear what you mean by a "list field containing event 1". Is Event 1 a record in another table, data with a time that falls between 4 and 5 am? Why a "list"? do you mean that there could be multiple events falling in that same time range?

          • 2. Re: Table Relationship for Calendar


            No typo, the first event overlaps the second.  I have an Event table with 3 records.  I wanted my resulting relationship to show the 24 CalendarRecords with all matching Events (for that hour).

            In the end, I resolved the issue myself using the following relationship:

            Events::startTime <= CalendarHours::hour
            Events::endTime > CalendarHours::hour

            And then I have an id_Event field in CalendarHours as a Calculation with just Events::id.  This doesn't allow overlapping Events, but I'm ok with that for now.

            One question I have though, the relationship I had above seems backwards, I originally had the following, but nothing ever matched, why is the logic "backwards"!?

            Events::startTime >= CalendarHours::hour
            Events::endTime < CalendarHours::hour





            • 3. Re: Table Relationship for Calendar

              It's the phrase "the 24 Calendar records" that has me puzzled. What 24 records is that? why 24? One for each hour of the day? 24 days?

              As to why it seems backwards, well it depends on context. If the current record is a CalendarHours record on a layout (or portal) based on CalendarHours, Its Hour must be greater than (come after )the event start time and less than (come before) the end time or the records don't match.