9 Replies Latest reply on Mar 14, 2017 2:21 PM by philmodjunk

    Adding Running-Time calculation


      I have two Tables, Service and TimeMGMT.  From the Service Table I have a TimeMGMT portal.  When the ticket Service::Status changes, a new Service::ModifiedTime is generated.  The Service::TicketID and Service::ModifiedTime is copied to the TimeMGMT Table.  The TimeMGMT Table's portal basically shows the life of a ticket from Open to Closed with timestamps to match each TimeMGMT Table record that was recorded for the specific Service::TicketID


      I would like a clock/timer to start rolling/counting when a TicketID is in a Working status.  If the TicketID goes into a Pending status I would like the clock to stop.  If the TicketID goes back into a Working status then I would want the clock to start again.  With this scenario I would then want to ADD the Times (however many) together to get a time in hh:mm:ss of actual work.

        • 1. Re: Adding Running-Time calculation

          So what you really need is an auto-enter calculation field in TimeMGMT that calculates:

          TimeMGMT::EndTime - TimeMGMT::StartTime     (result = time)


          you could also potentially convert that to a decimal:

          ( TimeMGMT::EndTime - TimeMGMT::StartTime ) / 3600


          From there, you can just create a summary field in the TimeMGMT table, or from your Service table:

          Sum ( TimeMGMT::DecimalTime )

          to get the sum total of actual work.


          Remember, time is stored as a number of seconds in the backend of filemaker. so subtracting start from end will give you the total number of seconds worked for all intents and purposes.

          • 2. Re: Adding Running-Time calculation

            Let me give it the ole college try

            • 3. Re: Adding Running-Time calculation

              Wait.  There is not a start and stop per say, Each record has it own time.  So the start time would be record1 Status::Working  and the stop would record2 Status::"Whatever".  So I guess in this case I will need to calculate the Status::Working time up to the next record with a different Status.

              • 4. Re: Adding Running-Time calculation

                It would make much more sense to store a start AND end time on the same record. If you sort the relationship by newest first, then you can just enter a value in the record directly through the relationship via a single script step:

                Set Field [ TimeMGMT::ClockOut ; Get(CurrentTime) ]

                So your scripts would be something like:

                Clock In Button:

                Set Variable [ $id ; Service::PrimaryKey ]

                Freeze Window

                Go To Layout [ TimeMGMT ]

                New Record/Request/Page

                Set Field [ TimeMGMT::ForeignKey ; $id ]

                Set Field [ TimeMGMT::ClockIn ; Get(CurrentTime) ]

                Commit Record [ no dialog ]

                Go To Layout [ original layout ]

                Clock Out Button:

                If [ Not IsEmpty ( TimeMGMT::ClockOut ) ]

                     Show Custom Dialog [ "ERROR, no record is currently clocked in" ]

                     Exit Script

                End If

                Set Field [ TimeMGMT::ClockOut ; Get(CurrentTime) ]


                After establishing those two buttons and sorting the relationship, then you would add the auto-enter calc field as I previously described, and then the summary fields.

                • 5. Re: Adding Running-Time calculation

                  Without the start and stop time on the same record, it will be much more complicated to calculate time. Essentially you would need to check for any missing time, EG if you records in order went:


                  Start Time

                  End Time

                  End Time

                  Start Time

                  End Time


                  There's a missing start time in there, so what happens then?


                  Even if the data was 100% sure in the right ascending date/time order and alternation between in and out, you would then need a complex calculation and potentially recursive custom function (which would perform very poorly) that would be able to "walk through" all of the related records to get the time total.


                  With an easy modification of one field to the TimeMGMT table, you can make things much simpler and perform better than trying to work with what you've currently got setup.


                  You can either write a one time script that will parse your existing information into a new format once you've set up the new format. Or export the "EndTime" records, and reimport the time to the new "EndTime" field for the matching "StartTime" records, before removing the old end records from your table.

                  • 6. Re: Adding Running-Time calculation

                    I now have new TimeStart, TimeEnd and TimeElapsed fields created and where TimeElapsed is calculating the elapsed time in hh:mm for each TicketStatus.  Here is my problem though.  I am trying to figure out something that may seem simple but is giving me a headache.


                    How can I get the TimeStart time of a new record be the TimeEnd time of another record?  I am trying to avoid setting up a button to do this task.

                    • 7. Re: Adding Running-Time calculation

                      You can set it to auto-enter either the value of a field from the previous record, if there is one, or the current time if there is no such record--meaning that it's the first record entered.


                      Let ( R = Get ( RecordNumber ) ;

                              If ( R > 1 ; GetNthRecord ( TimeEnd ; R - 1 ) ; Get ( Current Time ) )

                            ) // Let

                      • 8. Re: Adding Running-Time calculation

                        Will this work even if the copy-to-record is not in sequence?

                        • 9. Re: Adding Running-Time calculation

                          You would need to sort via relationship or portal set up to make them sequential.

                          I would think that you'd want that in any case.


                          An alternative approach is possible using auto-entered serial numbers and a self join.