4 Replies Latest reply on Jan 17, 2013 9:45 AM by philmodjunk

    date range calculation help



      date range calculation help


           I have a table of events.  They all have a start and end date.  Some are single days and some are multiple.  I would like to view a summary of one day and see the names of all the events that have a date in between or including the start and end date.


           Table example

           Event 1:  Start Jan 1 2013 , End Jan 3 2013

           Event 2:  Start Jan 2 2013, End Jan 2 2013

           Event 3: Start Jan 2 2013, End Jan 3 2013


           Would display like:


           Jan 1:  Event 1


           Jan 2:  Event 1

                        Event 2

                        Event 3


           Jan 3:  Event 1

                        Event 3



           THANK YOU!

        • 1. Re: date range calculation help

               Best approach I can think of is to link in a related table:

               Events::__pkEventID = EventDates::_fkEventID

               Use a script to create one record for each day from StartDate to EndDate in EventDates.

               Then you can set up your report using a layout based on EventDates and yet you can include fields from Events to identify details about each event on the report.

          • 2. Re: date range calculation help

                 I have considered this.  The only issue occurs when the dates in the event list table change.  

                 What  about creating a calcualtion keep ( that would always be updating) that could list the dates between the start and end dates?

            • 3. Re: date range calculation help

                   You have the same record appearing 3 times in that mocked-up 'List View With Summary', so you would need 3 records in a table somewhere.  If it is a 'List view' you want you have to create the those records (and create more, or delete some, if the event dates change), but if you are happy with portal listings of the events for each day then it is easy.

                   @Phil - did you not come up with a clever use of the ExcecuteSQL function to create the same effect as 'records appearing multiple times'?

              • 4. Re: date range calculation help


                     I don't recall that one, but it's certainly possible, but it would essentially be the same has setting up a table with one record for each day of the year on which you would base your report and then you'd add a portal to the layout that displayed all event records that overlap that date range.Either approach--the calculation or the portal would work here if you can live with the limitations that entails.


                     If you used the table of dates that I suggested, a script trigger can run a fairly simple script that would delete the set of related date records and then recreate them each time you modify either the start or end date. This script can be performed off of OnObjectSave triggers on the two date fields in the Event table.