5 Replies Latest reply on Nov 23, 2009 6:25 AM by comment_1

    Calculated Time fields results

    paulo.casa

      Title

      Calculated Time fields results

      Post

      Hello

       

      I have theexperimental version of Filemaker Pro 10 for Windows. I am trying to see if Icould solve this problem:

       

      In mydatabase I have a few records and one of the fields is a time value.

      Imagine 15 records and  witch one with a different time value.

      How can Icalculate de difference between time values from one record to another, no matterthe sort order and save the result on a field: I think it shouldbe something like this but I can put this in Filemaker’s language

      The time valueof the second record less the time value of the first record;

      The time valueof the third record less the time value of the second record

      And so one…..UntilI reach the last record

      Is itpossible to do. Can someone help me?

       

      Regards

      Paulo

        • 1. Re: Calculated Time fields results
          raybaudi
            

          Hi Paulo

           

          try:

           

          Case(
          Get ( RecordNumber ) > 1 ; YourTimeStampField - GetNthRecord ( YourTimeStampField ; Get ( RecordNumber ) - 1 )
          )

           

          result of type time.

          • 2. Re: Calculated Time fields results
            raybaudi
               P.S.: the result of that calculation must be UNSTORED
            • 3. Re: Calculated Time fields results
              paulo.casa
                

              Ciao Daniele
              La ringrazio per il vostro aiuto

               

              In fact the solution you achieved works very well for an unsorted database.

              But what I need is a solution for when I have to group and sort data. 

              I will try to explain

               

              A - I have a race with 10 racers.

              B - When the race starts I reset the system clock to 0h 0m 0s

              C - every time a racer cross the finish line, after a lap, I create a new record with the racer number (manually) and the time he cross the finish line (automatic),

              D - I do this until the race finishes for every pass at the fish line and obviously for every racer.

              E - Then I grouped the records by racer number and I would like to calc the time by lap for each racer

               

              Regards,

               

              Paulo

               

              PS: Your English is very good

              I am portuguese, from Azores islands 

               

              • 4. Re: Calculated Time fields results
                raybaudi
                  

                paulo.casa wrote:

                 

                ...the solution you achieved works very well for an unsorted database.


                ?

                 

                The solution works on each found set and, to give exact results, MUST be sorted.


                • 5. Re: Calculated Time fields results
                  comment_1
                    

                  You should define a self-join relationship between the lap records, something like:

                   

                  Laps:RacerID = Laps 2::RacerID

                  AND

                  Laps::LapID > Laps2::LapID

                   

                  with records from Laps ID sorted by LapID, descending. Then you can auto-enter the finish time from the first related record in Laps 2 into the start field.