JSON_Audit_Log

Log changes of different fields as JSON

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

Thomas Siebert - Show more from this author
http://www.t-seabird.de

Sample input:

JSON_Audit_Log ( self ; field1 & field2 )

Sample output:

{
"field1" :
[
{
"timestamp" : "29.03.2023 19:11:21",
"username" : "Admin",
"value" : "hello"
},
{
"timestamp" : "29.03.2023 19:11:26",
"username" : "Admin",
"value" : "world"
}
],
"field2" :
[
{
"timestamp" : "29.03.2023 19:10:41",
"username" : "Admin",
"value" : 1
},
{
"timestamp" : "29.03.2023 19:10:46",
"username" : "Admin",
"value" : 2
}
]
}

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

/*
Name:		JSON_Audit_Log ( Audit_Log_Field ; Fields_to_Log )
Parameter:	Audit_Log_Field ; Fields_to_Log
Returning:		JSON
--------------------------------------------------------------------------
Author:		Thomas Siebert
Version:		1.2
Created:		03.04.2023
E-Mail:		info@t-seabird.de
Website:		www.t-seabird.de
--------------------------------------------------------------------------
Create a Text field in your table using a descriptive FieldName like "_json_log".
Use Fields from the same table only

Use the CF in the Calculation such as:
JSON_Audit_Log ( self ; field1 & field2 )

uncheck "Do not evaluate if all referenced fields are empty"
uncheck "Do not replace existing value of field (if any)"

check "Prohibit modification of value during data entry"

deactivate indexing
use "Unicode" as default language

--------------------------------------------------------------------------
*/

Let ([
Audit_Log_Field	=	Audit_Log_Field ;
trigger			=	Fields_to_Log ;

active_field_name	=	Get ( ActiveFieldName ) ;
active_field_type	=	FieldType ( Get ( FileName ) ; active_field_name ) ;
json_value_type	=	Case ( PatternCount ( active_field_type ; "Number" ) ; JSONNumber ; JSONString ) ;

aktive_field_array	=	JSONGetElement ( Audit_Log_Field ; active_field_name ) ;
array_index		=	"[" & Case ( IsEmpty ( aktive_field_array ) ; 0 ; 
						ValueCount ( JSONListKeys ( aktive_field_array ; "" ) ) ) & 
					"]"
];

	JSONFormatElements ( 
		Case ( IsEmpty ( Audit_Log_Field ) ; "{}" ; 
			JSONSetElement ( Audit_Log_Field ; 
				[ active_field_name & array_index & "timestamp" ; Get ( CurrentTimestamp ) ; JSONString ] ;
				[ active_field_name & array_index & "username" ; Get ( AccountName ) ; JSONString ] ;
				[ active_field_name & array_index & "value" ; Get ( ActiveFieldContents ) ; json_value_type ] 
			) 
		)
	)

)

The Function loggs every change from given fields within a record.

Create a Text field in your table using a descriptive FieldName like "_json_log".
Use Fields from the same table only

Use the CF in the Calculation such as:
JSON_Audit_Log ( self ; field1 & field2 )

uncheck "Do not evaluate if all referenced fields are empty"
uncheck "Do not replace existing value of field (if any)"

check "Prohibit modification of value during data entry"

deactivate indexing
use "Unicode" as default language

Comments

		cupertino Apr 20, 2023
Hi Thomas Thanks for this useful feature. I installed it to monitor three critical fields in my database. Basically it works fine, only with some records I get the following syntax errors: ? * Line 1, Column 1 Syntax error: value, object or array expected. * Line 1, Column 3 Extra non-whitespace after JSON value. Can you help me? Thanks.

		cupertino Apr 20, 2023
Hi Thomas Thanks for this useful feature. I installed it to monitor three critical fields in my database. Basically it works fine, only with some records I get the following syntax errors: ? * Line 1, Column 1 Syntax error: value, object or array expected. * Line 1, Column 3 Extra non-whitespace after JSON value. Can you help me? Thanks.

		Thomas Siebert Apr 26, 2023
Try to reset the field using {}

		Andrew Mallison Mar 18, 2024
I added two fields to log but it tracked only one that I didn't choose.

		Andrew Mallison Mar 18, 2024
I added two fields to log but it tracked only one that I didn't choose.

		Thomas Siebert Mar 18, 2024
How did you do it?

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

JSON_Audit_Log

Comments

Support this website.

Support this website.