FindNewTimestampedValues ( data ; searchFromDate ; searchUntilDate ; searchFromTime ; delimiter )

Pull newer values from order lists of timestamped values.

Average rating: 4.5 (20 votes) Log in to vote

Richard Dyce - Show more from this author
Dyce & Sons Ltd.
http://dyce.com

Sample input:

$$test ="25.02.2016 10:32:21 Fu
25.02.2016 09:32:16 Bar
24.02.2016 10:32:13 Double-bar
24.02.2016 09:32:10 Mungo
23.02.2016 09:32:04 Midge
22.02.2016 10:14:40 Bingo
22.02.2016 10:14:40 Fluffy
21.02.2016 10:14:40 Bongo
21.02.2016 10:14:40 Flump"

FindNewTimestampedValues ( $$test ; GetAsDate( "23.02.2016" ) ; Get( CurrentDate ) ; "9:45" ; " " )

Sample output:

25.02.2016 10:32:21 Fu
25.02.2016 09:32:16 Bar
24.02.2016 10:32:13 Double-bar
24.02.2016 09:32:10 Mungo

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// FindNewTimestampedValues ( data ; searchFromDate ; searchUntilDate ; searchFromTime ; delimiter )

Let(
	[
		p = Position ( data ; GetAsDate( searchFromDate ) ; Length( data ) ; -1 ) ;
		v = If( 
			p = 0 ; "" ;
			PatternCount( Left( data ; p ) ; ¶   ) + 1 // last value the search date appears in
		)
	];

	Case(
		// make sure don't go beyond until date
		GetAsDate( searchFromDate ) > GetAsDate( searchUntilDate ) ; "" ;
		
		// date not found, so try next day from 00:00:00
		p = 0 ; FindNewTimestampedValues( data ; GetAsDate( GetAsDate( searchFromDate ) + 1 ) ; GetAsDate( searchUntilDate) ; GetAsTime( "00:00:00" ) ; delimiter ) ;

		// date is right, so check time..
		Let(
			[
				trimmedData = LeftValues( data ; v ) ;
				thisValue = GetValue( data ; v ) ;
				from = Position ( thisValue ; delimiter ; 1 ; 1) + 1 ;	
				until = Position ( thisValue ; delimiter ; 1 ; 2 ) - from ;
				thisTime =  Middle ( thisValue ; from ; until )
			];

			Case(
				// search time is after this time, but the data appears more than once in the trimmed data, try again with one fewer rows
				GetAsTime( searchFromTime ) > GetAsTime( thisTime ) and PatternCount ( trimmedData ; GetAsDate( searchFromDate ) ) > 1 ;
					FindNewTimestampedValues( LeftValues( data ; v - 1 ) ; GetAsDate( searchFromDate ) ; GetAsDate( searchUntilDate ) ; GetAsTime( searchFromTime ) ; delimiter ) ;

				// search time is after this time, so begin from 00:00:00 next day ( but with trimmed data )
				GetAsTime( searchFromTime ) > GetAsTime( thisTime )  ;
					FindNewTimestampedValues( trimmedData ; GetAsDate( searchFromDate + 1 ) ; GetAsDate( searchUntilDate ) ; GetAsTime( "00:00:00" ) ; delimiter ) ;

				// date found, so return foundDate and position
				trimmedData

			)
		) 

	)
)

This function pulls data from a timestamped list of values (e.g. an audit log), sorted newest first. In other words it either pulls just the first x values from the list that were timestamped AFTER searchFromDate, searchFromTime, or returns ""

Parameters:

data : A timestamped list of values, e.g. "timestamp details¶timestamp details¶timestamp details"
searchFromDate : which date to you want to search from?
searchUntilDate : at what date should you give up searching the list?
searchFromTime : at what time on the first date to start searching?
delimiter: this should probably be a space, but you may have a tab-delimited "date, time, details"-stylelist of values.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

FindNewTimestampedValues ( data ; searchFromDate ; searchUntilDate ; searchFromTime ; delimiter )

Comments

Support this website.

Support this website.