PaulWebb

Imported Timestamps data issue

Discussion created by PaulWebb on Aug 31, 2012
Latest reply on Aug 31, 2012 by 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." )

 

)

)

Outcomes