3 Replies Latest reply on Aug 31, 2012 4:11 PM by PaulWebb

    Imported Timestamps data issue

    PaulWebb

      I imported data into my DB. For every record there is a create timestamp but only some have a close timestamp. Those with a close timestamp calculate correctly using the custom function below. Those without a close timestamp return a value similar to "-734202 days". If a record returns a negative value as previously stated I can go into the create timestamp field and higlight and retype a character or add a space to the end of the field after the time designator. Once I make the change and exit the field the Age updates with the correct calculation result. This leads me to believe there is something wrong with my data or it needs to be refreshed. Is there a way I can refresh the values so it calculates correctly without going record by record?

       

       

      FileMaker Pro 12 Advanced

      DB - three fields. CreateDate (timestamp), CloseDate (timestamp), Age (custom function)

       

      Custom Function - ElapsedTime ( TStampBegin ; TStampEnd ; Format )

      // Input must be TimeStamp fields not strings. Format is a string

      Let ( [

      ElapsedSeconds = ( If ( IsEmpty ( TStampEnd ) ; Get ( CurrentHostTimeStamp ) ; TStampEnd )) - TStampBegin ;

      Days = Int ( ElapsedSeconds / 86400 ) ;

      Hours = Int ( ( ElapsedSeconds - ( Days*86400 ) ) / 3600 ) ;

      Minutes = Int ( ( ElapsedSeconds - ( ( Days * 86400 ) + ( Hours * 3600 ) ) ) / 60 );

      Seconds = Int ( ElapsedSeconds - ( ( Days * 86400 ) + ( Hours * 3600 ) + (Minutes * 60 ) ) ) ;

      ElapsedDays = ElapsedSeconds / 86400 ;

      ElapsedHours = ElapsedSeconds / 3600 ;

      ElapsedMinutes = ElapsedSeconds / 60

       

      ] ;

       

      Case (

       

      Format = "Seconds" ;

      // Result is in number or time format

      ElapsedSeconds ;

       

      Format = "Minutes" ;

      // Result is a string

      ElapsedMinutes & " minutes" ;

       

      Format = "Hours" ;

      // Result is a string

      ElapsedHours & " hours" ;

       

      Format = "Days" ;

      // Result is a string

      Round ( ElapsedDays ; 0 ) & " days" ;

       

      Format = "String" ;

      // Result is a string

      If ( Days = 1 ; Days & " day, " ; Days & " days, " ) & If ( Hours = 1 ; Hours & " hour, " ; Hours & " hours, " ) &

      If ( Minutes = 1 ; Minutes & " minute, " ; Minutes & " minutes, " ) & If ( Seconds = 1 ; Seconds & " second, " ; Seconds & " seconds." )

       

      )

      )

        • 1. Re: Imported Timestamps data issue
          PaulWebb

          Ok, so I created a script to loop through and update all open fields. Guess my question is what happened to the data causing the problem. Any ideas?

          • 2. Re: Imported Timestamps data issue
            tech_liaison

            My first guess would have been that this is a data type mismatch error - i.e., one of your date fields is a text field and not a timestamp - but it looks like you've got that covered. So I would agree with your assessment that there are some values in your import file that FMP is not recognizing as a timestamp. One thing you can try to do to correct the problem at time of import is to add auto-enter calcuations to the CreateDate and CloseDate fields which will sanitize the imported value (be sure to uncheck the, Do not replace existing value of field option to ensure that the calculation replaces the imported value with the sanitized value). Also be sure to allow auto-enter events to occur during the import. HTH.

             

            Best,

            Dave

            • 3. Re: Imported Timestamps data issue
              PaulWebb

              Thanks Dave. The field originally was a date field and I converted it to a timestamp. I thought I had done this prior to importing the data but I cannot remember now. That might have caused my problem. I'll have to test with another import to see.