12 Replies Latest reply on Mar 12, 2012 7:05 PM by mikeo'neil

    Calculation Field Question & Design Method

    mikeo'neil

      Hello All,

       

      Need some guidance on my design and calculation methods.

      I need to create a database to determine the system status of the Fire Department where I work. A little background: Win FMP 11.03 and Server 11. I need to find out how many times a unit is busy on medical emergency calls, a fire, or other alarms, throughout a 24 hour period, in 15 minute blocks, for all of our units. For example, from 00:00:00 to 01:00:00 am, I need to find out how many units are assigned to an alarm during any 15 minute block 24 hours per day.

      I am importing all incidents from our 911 Center into standard fields; (alarm number, Call Type, Unit, Assigned Timestamp, Available Timestamp, and T_Start & T_End that hold the time in seconds (Number).

      It looks like I will need to create 96 Calculation fields to address each 15 minute time block and then 96 summary fields to count the totals. Is there a better way and what is it?

      The below calculation is to return a “1” if the unit was busy between 00:45:00 and 00:60:00 (hh:mm:ss). I am not too comfortable with this and would appreciate some input.

      Case( T_Start ≥ 2701 and T_Start ≤ 3600;1;

      T_End ≥ 2701 ;1;"")

       

      Below calculations cover the first hour…

       

      00:00:00 thru 00:15:00 minutes (0 thru 900 seconds)

      Case(T_Start ≥ 0 and T_Start ≤ 900;1;

      T_End ≥ 0;1;"")

       

      00:15:01 thru 00:30:00 minutes (901 thru 1800 seconds)

      Case( T_End ≥ 901 and T_Start ≤ 1800;1;

      T_End ≥ 901;1;"")

       

      00:30:01 thru 00:45:00 minutes (1801 thru 2700 seconds)

      Case( T_Start ≥ 1801 and T_Start ≤ 2700;1;

      T_End ≥ 1801;1;"")

       

      00:45:01 thru 00:60:00 minutes (2701 thru 3600 seconds)

      Case( T_Start ≥ 2701 and T_Start ≤ 3600;1;

      T_End ≥ 2701;1;"")

       

      Thanks,

      Mike

        • 1. Re: Calculation Field Question & Design Method
          woytovich

          I would start with a single calc that determines the "block" then use that block number.

          1 of 1 people found this helpful
          • 2. Re: Calculation Field Question & Design Method
            comment

            mikeo\'neil wrote:

             

            It looks like I will need to create 96 Calculation fields to address each 15 minute time block and then 96 summary fields to count the totals. Is there a better way and what is it?

             

            I believe you should define a Slots table with 96 records. Have each record  calculate its start and end timestamps using a global view date and a serial number. Use a relationship to summarize the incidents related to the calculated slot.

            1 of 1 people found this helpful
            • 3. Re: Calculation Field Question & Design Method
              mikeo'neil

              Michael, thank you but I am not understanding the concept of a Slots table. Any chance you could explain a little more. I don't know if I explained what I am trying to do correctly. We might have up to 9 or 10 units assigned to single emergency or as little as 1 or 2 units on the average of 40 to 50 fire, medical, special operations and other type of emergency calls during a 24 hour period. We hope to look at the data daily, monthly, yearly and so on with a number count and visual hi-lighting showing us how many of our units are busy during any 15 minute block during the 24 hour period.

               

              Thanks and Sorry for my senior moments...

               

              Mike

              • 4. Re: Calculation Field Question & Design Method
                comment

                Do you mean you just want to summarize the events by the 15 minute blocks? For example:

                 

                Date: March 10, 2012

                00:00 - 00:15: 6 events

                00:15 - 00:30: 4 events

                00:30 - 00:45: 9 events

                ...

                23:45 - 00:00: 7 events

                 

                Date: March 11, 2012

                00:00 - 00:15: 3 events

                ...

                • 5. Re: Calculation Field Question & Design Method
                  mikeo'neil

                  Michael,

                   

                  I attached a pdf of what I started to work on that shows what I am trying to do. I need to display each emergency call with some info and then indicate when the unit was busy. We could have a unit busy for 5 minutes or they could be tied up on the call for hours, and crossing over midnight. I hope the attached pdf explains thing a little better. I needed to split the pdf to 2 pages.

                   

                  Thanks,

                   

                  Mike

                  • 6. Re: Calculation Field Question & Design Method
                    comment

                    To get that format, you would indeed need 96 calculation fields (or a repeating calculation field with 96 repetitions).

                     

                    The other issue is the events that crossed midnight: suppose you have an event from Sunday 22:00 until Monday 02:00. When you do a find for events on Sunday, this event will incorrectly contribute to the morning columns; when you report on Monday, the same event will incorrectly add to the evening columns.

                    • 7. Re: Calculation Field Question & Design Method
                      mikeo'neil

                      I spent the past couple of days researching and attempting to build a calculation formula to compare date and time ranges.

                       

                      What I am having trouble with is a formula that is able to compare a range ( Start Timestamp and an End Timestamp against another block range and if so, calculate a value of 1 [Number] ).

                       

                      Example 3/1/2012 00:05:50 Start TS

                                    3/1/2012 00:21:39 End  TS

                       

                      How to determine if the Start TS or End TS is in the first time block range (1 to 900) seconds? Also, the second Time Block range would need to evaluate this as being in the range.

                       

                      First Time Block Start Range would be 3/1/2012 00:00:01

                      First Time Block End Range would be 3/1/2012 00:15:00

                       

                      Second Time Block Start Range would be 3/1/2012 00:15:01

                      Second Time Block End Range would be 3/1/2012 00:30:00

                       

                      I keep running into a dead end, as you can see above, the Start TS is > than the first block begin time and the End TS is > than the end time block range. I have 96 block ranges for the 24 hours starting a 1 second ending at 86400 before a date change. I have tried to use the Timestamps as number but still no luck… I am so lost with this…

                      • 8. Re: Calculation Field Question & Design Method
                        jason.delooze

                        What you want to test is the following:

                         

                        For the 1st time block (0:00-0:15),

                         

                        ( Time( Start_TS ) <= Time( 0 ; 15 ; 0 ) ) and ( Time( End_TS ) > Time( 0 ; 0 ; 0 ) )

                         

                        2nd block: (0:15:00 - 0:30:00)

                         

                        ( Time( Start_TS ) <= Time( 0 ; 30 ; 0 ) ) and ( Time( End_TS ) > Time( 0 ; 15 ; 0 ) )

                         

                        3rd block: (0:30:00 - 0:45:00

                         

                        ( Time( Start_TS ) <= Time( 0 ; 45 ; 0 ) ) and ( Time( End_TS ) > Time( 0 ; 30 ; 0 ) )

                         

                        etc.

                         

                        With the proper modification, the above could be placed in a single repeating field (with 96 repetitions) - soyou would not need to create 96 separate calculations.

                        • 9. Re: Calculation Field Question & Design Method
                          jason.delooze

                          Mike,

                           

                          As Michael Horak mentioned, since the Event can start on one date and end on another date, the "15 minute array" information will be incorrect for the (Start) Date.  In such cases, you could split the Event into 2 (or more) Events, each covering just a single date; that is, the Start_Date and End_Date would be the same.  FOr example, if an Event starts at 3/11/2012 22:18:03 and ends on 3/12/2012 03:12:15, you could split this event into 2 events: one event from 3/11/2012 22:18:03 to 3/11/2012 23:59:59 and the 2nd event from 3/12/2012 00:00:00 to 3/12/2012 03:12:15.  Doing so would properly report each day's events and totals correctly.

                           

                          On the other hand, if you acknowledge that any Event that starts on one day and ends on the next day will be counted solely in the Starting Date's totals, then you can do as you are planning.

                           

                          Also note that, depending on all your reporting requirements (and we have seen only 1 or 2 of them here), a different approach may be needed to handle all requirements.  Remember that just because you get the information in the format from the Call Center does not mean that you are restricted to using that format.

                           

                          I've included a quick demo that implements a report similar to the one you showed in your pdf.  Good luck!

                           

                           

                          Jason L. DeLooze

                          • 10. Re: Calculation Field Question & Design Method
                            mikeo'neil

                            Thanks Jason, I tried your formula but was unable to get it to work. I created and attached a very simple test db and documented the field definitions. If you could look and see what I am doing wrong I would appreciate it. I will take a look at what you just sent while I was writing this. Thanks-Mike

                            • 11. Re: Calculation Field Question & Design Method
                              comment

                              Please address the issue of events crossing midnight. I could simply fix your calculations, but it seems pointless if you want accurate results.

                              • 12. Re: Calculation Field Question & Design Method
                                mikeo'neil

                                Jason & Michael,

                                 

                                Your advice and the CrewLog file is outstanding and looks like this will solve the issue and I will test it for the next week or so with live data.

                                 

                                Thanks so much for all your help,

                                 

                                Mike