1 Reply Latest reply on Dec 25, 2010 9:48 PM by bumper

    Activity Database - Calculating Activity Duration with Pauses



      Activity Database - Calculating Activity Duration with Pauses


      Hi There,

      First - Merry Christmas, All!

      Second - My conundrum that I would be grateful for any insight or suggested remedies for.  I have searched in vain for example databases or forum posts regarding this issue.  If you have either please feel free to point me in the correct direction while mocking my weak Google-fu.

      I am working with FMP 11 Advanced on Windows XP.

      The database tracks tasks that are assigned to people.  Most assignments are 10-30 minutes in length but can be as long as several hours.  It is common for tasks to be 'paused' in the middle while a different assignment is conducted and then 'resumed'.  I am having trouble finding a reliable and accurate process for calculating the total assignment duration minus any time that the task was on hold that also updates a visible 'Duration' field  in real time.  Am I after something that doesn't exist?

      This is a 24 hour operation so the time calculation needs to take in to account starting at, for example,  23:45 and ending at 01:05 the next day and not giving back a negative number.  (one of the places I looked,  the starter solution "Time Cards", does not calculate time across multiple days.)

      My current set up is all on one table.  The pertinent fields are:

      AssignedDate / AssignedTime / PausedDate / PausedTime / ResumedDate / ResumedTime / CompleteDate / CompleteTime / Status (text) / PausedFor (Time) / Duration (Calc)

      After creating the record (AssignedDate/Time are auto populated ) the user selects 1-of-3 buttons to Pause, Resume or Close the assignment.  I would like for there to be an infinite # of Pause/Resume and even Close/Resume.

      Right now each button runs the same script.  Based on the ScripParameter fed in to it by the button pushed it will automatically fill the time and date of the corresponding fields.  If the function selected was "Resume" then it also calculates the difference in time from when it was last paused/closed and dumps that time duration in to the PausedFor field.  The duration calculation subtracts the PausedFor from it's total time.


        • 1. Re: Activity Database - Calculating Activity Duration with Pauses

          1. go with two tables, one probably project (parent) two fields a project id, auto-enter a unique serial number, prohibit modification and a calc field, sum(line item duration). the second table, time line items (child), four fields - foreign ID, start timestamp, end timestamp and line item duration end time - start time,  result as Time.

          2. set up a relationship between the parent ID and the child foreign ID, allow creation of records.

          3. On a parent layout all you need to show is the ID and the Sum field and the a portal of the related line items.

          Each time segment is a separate record

          Using Timestamps will allow the calculations to crossover midnight and still keep the duration accurate. You can use a script attached to a button to set the start and end fields with get(current timestamp).

          This allows employees to go from one project to another as necessary, you could trap for not allowing an employee to create an new line item in a different project if they still have a open record in another project and to not allow alteration of a line item record once it is complete, ie, both start and end contain data.

          Obviously you can add any extra fields required for your needs such as notes as to what was accomplished during each duration, etc.