Calculation Field Question & Design Method

Question asked by mikeo'neil on Mar 10, 2012
Latest reply on Mar 12, 2012 by 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