Activity Database - Calculating Activity Duration with Pauses
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.