SortRecordsByJSON_AppleScript ( sort_array )

Dynamic sorting for hierarchical field order to create nested subsummaries (macOS only)

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

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

Sample input:

{
"sort" :
[
{
"field" : "tablename::fieldname1",
"order" : "ascending"
},
{
"field" : "tablename::fieldname2",
"order" : "descending"
}
]
}

Sample output:

sorting of the current layout

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

/*
Name:		SortRecordsByJSON_AppleScript ( sort_array )
Parameter:	JSON Array
--------------------------------------------------------------------------
Author:		Thomas Siebert
Version:	32.0
Created:	24.09.2021
E-Mail:		info@t-seabird.de
Website:	www.t-seabird.de
-------------------------------------------------------------------------
Description:	Perform AppleScript Version
	 	CAUTION: Activate "fmextscriptaccess" in the Extended Privilege
 	 	Settings.

Dynamic sorting for hierarchical field order.
Use  the Scriptstep "perform AppleScript" with the option "Calculated AppleScript".

Deliver an JSON-Array in following form:

JSON Array Scheme:

[
	{
		"field" : "tablename:: field1",
		"order" : "ascending"
	},
	{
		"field" : "tablename:: field1",
		"order" : "descending"
	}
]



You may use following calculation to generate the JSON:


JSONSetElement ( "[]" ; 
[ "[0]field" ; GetFieldName ( tablename::field1 ) ; JSONString ] ;
[ "[0]order" ; "ascending" ; JSONString ] ;
[ "[1]field" ; GetFieldName ( tablename::field2 ) ; JSONString ] ;
[ "[1]order" ; "descending" ; JSONString ] 
)
-------------------------------------------------------------------------

*/

Let ([
Sort_Lists = While ( 
	[ 
		count = 0 ; 
		result_field = "" ; 
		result_order = "" ; 
		sort_array =  sort_array
	] ; 
	count < ValueCount ( JSONListKeys ( sort_array ; "" ) ) ; 
	[
		full_fieldname = JSONGetElement ( sort_array ; "[" & count & "]field" ) ;
		order = JSONGetElement ( sort_array ; "[" & count & "]order" ) ;
		order = Substitute ( Lower ( order ) ; [ "ascending" ; "asc" ] ; [ "descending" ; "desc" ] ; [ "asc" ; "ascending" ] ; [ "desc" ; "descending" ] )  ;

		fieldname = GetValue ( Substitute ( full_fieldname ; [ "::" ; ¶ ] ) ; 2 )   ;
		tablename = GetValue ( Substitute ( full_fieldname ; [ "::" ; ¶ ] ) ; 1 )   ;

		field_string = "field " & Quote ( fieldname ) & " of table " & Quote ( tablename )  ;

		result_field = Substitute ( List ( result_field ; field_string ) ; [ ¶ ; ", " ] )  ;
		result_order = Substitute ( List ( result_order ; order ) ; [ ¶ ; ", " ] )  ;

		count = count + 1
	] ;
	List ( result_field ; result_order )
) ;

AppleScriptText = 
"tell database " & Quote ( Get ( FileName ) ) & "
¶	sort layout " & Quote ( Get ( LayoutName ) ) & " by {" & GetValue ( Sort_Lists ; 1 ) & "} in order {" & GetValue ( Sort_Lists ; 2 ) & "}
¶end tell" 

];

AppleScriptText

)

Dynamic sorting for hierarchical field order to create nested subsummaries.
Use the Scriptstep "perform AppleScript" with the option "Calculated AppleScript".

Deliver an JSON-Array in following form:

[
{
"field" : "tablename:: field1",
"order" : "ascending"
},
{
"field" : "tablename:: field1",
"order" : "descending"
}
]

You may use following calculation to generate the JSON:

JSONSetElement ( "[]" ;
[ "[0]field" ; GetFieldName ( tablename::field1 ) ; JSONString ] ;
[ "[0]order" ; "ascending" ; JSONString ] ;
[ "[1]field" ; GetFieldName ( tablename::field2 ) ; JSONString ] ;
[ "[1]order" ; "descending" ; JSONString ]
)

Comments

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

SortRecordsByJSON_AppleScript ( sort_array )

Comments

Support this website.

Support this website.