2 Replies Latest reply on Jan 28, 2014 6:58 AM by philmodjunk

    Summary by Time Range across Dates



      Summary by Time Range across Dates


           I'm trying to figure out how to create a summary of records that occur between 5pm yesterday and 5pm today. My records contain Date, Time and Amount fields like so:

           1/15/2013 13:01:00 $10.00  (exclude)

           1/15/2013 14:44:10 $10.00  (exclude)

           1/15/2013 17:01:00 $10.00  (include)

           1/16/2013 06:12:02 $10.00  (include)

           1/16/2013 09:15:00 $10.00  (include)

           1/16/2013 17:02:00 $10.00  (exclude)

           Only those records indicated should be included in the summary total. I can't figure out how to tell Filemaker to summarize across 24 hours that are not on the same date. Any help would be appreciated. A calculation, relationship, or a summary field would all work for my solution. Thanks.

        • 1. Re: Summary by Time Range across Dates

               Finally solved my own problem.

               1. Created a calculation field that equals "0" or "1" depending on whether the time was after 5pm.

               2. Created another calc (date) field to add the result of the above field to the date. If the time was after 5pm the new date field is calculated as the next day. 

               3. Records are then sorted by the new date field and a summary field contains the total.

          • 2. Re: Summary by Time Range across Dates

                 It would be simpler to record date and time in a single field of type Timestamp. Then you can perform a find for all records in a given timestamp range or set up a relationship that matches by that range;

                 MainTable::StartTimeStamp >Transactions::TimeStamp AND
                 MainTable::EndTimeStamp < Transactions::TimeStamp