5 Replies Latest reply on Mar 15, 2012 9:00 AM by philmodjunk

    Trying to calculate elapsed time from two different records.

    Jtmwalk

      Title

      Trying to calculate elapsed time from two different records.

      Post

      I have and arrive time field and a depart time field in each record.  I am trying to calculate the elapsed time from Depart time to arrive time, all while working with only records involved in a filterer portal.  Any idea on how to write a script to calculate this? 

        • 1. Re: Trying to calculate elapsed time from two different records.
          philmodjunk

          Can you describe your portal's records in more detail? Arrival and departure times could be two different fields in the same record. Your post makes it obvious that this is not the case, but doesn't indicate what structure you do have for your data.

          • 2. Re: Trying to calculate elapsed time from two different records.
            Jtmwalk
            Sorry about that..... My company dispatches drivers and we need to track not only their arrive/depart times for each customer, but we must also track the elapsed time between customers. I currently have it set up so each customer move is a single record, with arrive/depart. When the driver moves towards the next customer, it creates a new record and will require new arr/dep times. I have it calculating the arr/dep times correct, but want to track from the LAST Depart time record for that specific driver, to the NEXT Arrive time on the new record.      
            • 3. Re: Trying to calculate elapsed time from two different records.
              philmodjunk

              What is the relationship and filter used for this portal?

              Do you want a single value calculated that references the last two records in the portal and that updates each time a new portal record is added or do you want each portal record to compute this time interval by referencing the previous record?

              Assuming the latter, you can set up cTimeInTransit in the portal table as:

              If ( Get ( RecordNumber ) > 1 ; ArrivalTime - getNthRecord ( DepartureTime ; get ( RecordNumber ) - 1 ) )

              • 4. Re: Trying to calculate elapsed time from two different records.
                Jtmwalk

                 I must apologize for the earlier confusion, and thank you for the response, however, I think I was ahead of myself and need to simplify my need.

                An example of my records and fields:

                Record#1:     Date         Driver       Customer(A)     Arrive   Depart    Duration   (This works fine)

                Record#2:     Date         Driver       Customer(B)     Arrive   Depart    Duration   (This works fine)

                Record#16:   Date         Driver       Customer(C)     Arrive   Depart    Duration   (This works fine)

                I have two tables;  1-Driver ,  2-Detail. My calculations will all be done in the Detail table, which holds the time fields.

                Each record captures only the arr/dep time and duration AT the customer.  When I run the report, I will filter on the Driver specific and bring in all related records for that driver.  Being that there are many other drivers during the day, the record numbers will not run consecutive, however, when I sort the found set by arrive time A-Z, the records will fall in chronological order.  At that point, I want to take the earliest Depart time - Next portal record in order Arrive time to calculate the 'transit' time. 

                Customer (A)   To Customer (B) Transit time:

                Customer (B)   To Customer (C) transit time:

                Similar to running a check book balance from one line to the next.

                Sorry for the previous confusion, and hoping you can help me find the correct script to perform this task.  Once I can establish this function, I can easily enhance it for other needs within the data base.

                Thank you again for your help!

                • 5. Re: Trying to calculate elapsed time from two different records.
                  philmodjunk

                  Get ( RecordNumber ) returns the postion of the record in the current found set or portal. Get ( recordNumber ) will return 1 for any record that is currently the first record in the found set or the first record in the portal. It is not determined by the order in which the records were created. Thus, in either the portal or in this report layout which appears to be based on the Detail table, it should work for you. The one case where it will have trouble is if your report lists multiple drivers at the same time. In that case the If function would need to test for more than just Get ( recordNumber ) = 1.