CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )

List every table names of current file followed by count of records which modified within StartTimestamp and EndTimestamp.

Average rating: 3.0 (1 vote) Log in to vote

Koji Takeuchi - Show more from this author
TonicNote, Inc.
https://tonicnote.com

Sample input:

CountModifiedRecords ( "za_timestampModified" ; "2018/5/1 15:00:00" ; "2018/5/3 9:00:00" )

Sample output:

140 OrderItem
32 Order
2 Customer
0 Products

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

/*
CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )
2018.05.28, Koji Takeuchi <takeuchi@nemoux.com>


description:
	List every table names of current file followed by count of records which modified within StartTimestamp and EndTimestamp.

	*works on version 16 or greater*

ex1:
	CountModifiedRecords ( "za_timestampModified" ; "2018/5/1 15:00:00" ; "2018/5/3 9:00:00" )
	 -> "140<tab>OrderItem¶32<tab>Order¶2<tab>Customer¶0<tab>Products"
ex2:
	CountModifiedRecords ( "za_timestampModified" ; GetAsTimestamp ( Get ( CurrentDate ) - 10 ) ; GetAsTimestamp ( Get ( CurrentDate ) ) )
	 -> "521<tab>OrderItem¶340<tab>Order¶83<tab>Products¶45<tab>Customer"
	# It returns -1 as count if the table does not have the field which specified.
*/



Let (
[
_NAMETIMESTAMPFIELD =
	NameTimestampField ;
_TIMESTAMPSTART =
	StartTimestamp ;
_TIMESTAMPEND =
	EndTimestamp ;
_BASEFILENAME =
	Get ( ファイル名 ) ;
$$array_table =
	Case (
		not IsEmpty ( $$array_Table ) ;
		$$array_table ;
		RemoveEmptyValues ( 
			ExecuteSQL ( 
				"SELECT BaseTableName, MAX ( TableName ) FROM FileMaker_Tables WHERE BaseFileName LIKE ? GROUP BY BaseTableName ORDER BY BaseTableName" ; "	" ; "" ; _BASEFILENAME & "%" )
		)
	) ;
$$num_array_table =
	ValueCount ( $$array_table ) ;
$$counter_array_table =
	$$counter_array_table + 1 ;


_CURRENTROW =
	GetValue ( $$array_table ; $$counter_array_table ) ;
_NAMETABLE =
	GetValue (
		Substitute (
			_CURRENTROW ;
			"	" ; ¶
		) ;
		1
	) ;
_NAMETO =
	GetValue (
		Substitute (
			_CURRENTROW ;
			"	" ; ¶
		) ;
		2
	) ;
_FIELDTIMESTAMP =
	Quote ( _NAMETO ) & "." & Quote ( _NAMETIMESTAMPFIELD ) ;
_QUERY =
	"SELECT " & ¶ &
		"Count ( " & ¶ &
		"	" & _FIELDTIMESTAMP & ¶ &
		") " & ¶ &
	"FROM " & ¶ &
		Quote ( _NAMETO ) & ¶ &
	"WHERE " & ¶ &
		_FIELDTIMESTAMP & " >= ? " &
		"AND " &
		_FIELDTIMESTAMP & " <= ? " ;
_ISVALID =
	IsValidExpression ( 
		FieldType ( _BASEFILENAME ; _NAMETO & "::" & _NAMETIMESTAMPFIELD )
	)  ;
_NUM =
	Case (
		not _ISVALID ;
		ExecuteSQL ( _QUERY ; "" ; "" ; _TIMESTAMPSTART ; _TIMESTAMPEND ) ;
		-1
	) ;
_NUM_AND_NAMETABLE =
	Case (
		not IsEmpty ( _NUM ) ;
		_NUM ;
		not _ISVALID ;
		0
	) &
	"	" & _NAMETABLE ;
$$RESULT =
	List (
		$$RESULT ;
		_NUM_AND_NAMETABLE
	)
] ;

Case (
$$counter_array_table < $$num_array_table ;
CountModifiedRecords ( _NAMETIMESTAMPFIELD ; _TIMESTAMPSTART ; _TIMESTAMPEND ) ;

Let (
[
_RESULT = SortValues ( $$RESULT ; -2 ) ;
$$array_table = "" ;
$$num_array_table = "" ;
$$counter_array_table = "" ;
$$RESULT = ""
] ;
_RESULT
)
)

)

CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )
2018.05.28, Koji Takeuchi

description:
List every table names of current file followed by count of records which modified within StartTimestamp and EndTimestamp.
ex1:
CountModifiedRecords ( "za_timestampModified" ; "2018/5/1 15:00:00" ; "2018/5/3 9:00:00" )
-> "140OrderItem¶32Order¶2Customer¶0Products"
ex2:
CountModifiedRecords ( "za_timestampModified" ; GetAsTimestamp ( Get ( CurrentDate ) - 10 ) ; GetAsTimestamp ( Get ( CurrentDate ) ) )
-> "521OrderItem¶340Order¶83Products¶45Customer"
# It returns -1 as count if the table does not have the field which specified.

Comments

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

CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )

Comments

Support this website.

Support this website.