GetNthRecordSetFilter ( outputField ; conditions )

Grabs value of a field for all records in a relationship that match given conditions.

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

Ethan - Show more from this author
Tactile Medical
https://www.linkedin.com/in/ethyoder

Sample input:

GetNthRecordSetFilter (
GetFieldName ( Person::PrimaryKey ) ;
JSONSetElement ( "[]" ;
[ "[0]field" ; GetFieldName ( Person::DOB ) ; JSONString ] ;
[ "[0]operation" ; ">=" ; JSONString ] ;
[ "[0]value" ; "01/01/2000" ; JSONString ] ;

[ "[1]field" ; GetFieldName ( Person::FName ) ; JSONString ] ;
[ "[1]operation" ; "bw" ; JSONString ] ;
[ "[1]value" ; "T" ; JSONString ]
)
)

Sample output:

All the primary keys for people:
- born after 2000
- with a first name that starts with T

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

//	NAME		GetNthRecordSetFilter
//	DESCRIPTION	Grabs value of a field for all records in a relationship that match given conditions.
//
//	PARAMETERS	outputField (full TO/field name, text): the value you want to grab for each matching record
//			conditions (json): a json array of objects that contain the proper conditions
//
//	RETURNS	list
//
//	EXAMPLE	GetNthRecordSetFilter ( 
//				GetFieldName ( Person::PrimaryKey ) ;
//				JSONSetElement ( "[]" ;
//					[ "[0]field" ; GetFieldName ( Person::Birthday ) ; JSONString ] ;
//					[ "[0]operation" ; ">=" ; JSONString ] ;
//					[ "[0]value" ; "01/01/2000" ; JSONString ] ;
//					[ "[1]field" ; GetFieldName ( Person::First Name ) ; JSONString ] ;
//					[ "[1]operation" ; "bw" ; JSONString ] ;
//					[ "[1]value" ; "T" ; JSONString ]
//				)
//			)
//	RESULT		All the primary keys for people:
//			- born after 2000
//			- with a first name that starts with T
//
//	DEPENDENCIES	GetFieldType [https://www.briandunning.com/cf/2541]
//			GetAs [https://www.briandunning.com/cf/2540]
//			Compare [https://www.briandunning.com/cf/2542]
//
//	2021-10-27, eyoder, Created CF


While ( 
[
	/* ========== INITIAL VARIABLES ========== */

	//# store variables

	vGrabFieldAndTO = outputField ;
	vConditions = conditions ;


	//# set add field type to conditions json for more accurate evaluating

	vConditionAmt = ValueCount ( JSONListKeys ( vConditions ; "" ) ) ;
	vConditions =
		While ( 
		[
			vJth = 0 ;
			vConditions = vConditions
		] ; 
			vJth < vConditionAmt ;
		[	
			vJthField = JSONGetElement ( vConditions ; "[" & vJth & "]field" ) ;
			vJthFieldType = JSONGetElement ( vConditions ; "[" & vJth & "]fieldType" ) ;
			vConditions =
				Case (
					IsEmpty ( vJthFieldType ) ;
					JSONSetElement ( vConditions ; 
						[ "[" & vJth & "]fieldType" ; GetFieldType ( "" ; vJthField ) ; JSONString ]
					) ;

					vConditions
				) ;
			vJth = vJth + 1
		] ;
			vConditions
		) ;


	//# set up while loop variables

	vResult = "" ;
	vIth = 1
] ; 

	/* ========== CONDITION ========== */

	//# continue until the nth record is no longer valid

	IsValid ( GetNthRecord ( GetField ( vGrabFieldAndTO ) ; vIth ) ) ;

[ 
	/* ========== LOGIC ========== */

	//# loop through every record in the relationship
	//# return the field in the parameters if all condition are met

	vResult =
		List (
			vResult ;
			While ( 
			[
				//# setup loop variables
				
				vID = GetNthRecord ( GetField ( vGrabFieldAndTO ) ; vIth ) ;
				vConditionFailed = False ;
				vJth = 0
			] ; 

				//# stop if we run out of conditions or a condition fails

				( vJth < vConditionAmt ) and not vConditionFailed ;
			[	

				//# check if the condition matches

				vJthCondition = JSONGetElement ( vConditions ; vJth ) ;
				vJthField = JSONGetElement ( vJthCondition ; "field" ) ;
				vJthFieldType = JSONGetElement ( vJthCondition ; "fieldType" ) ;
				vJthOperation = JSONGetElement ( vJthCondition ; "operation" ) ;

				vJthValue = GetAs ( vJthFieldType ; JSONGetElement ( vJthCondition ; "value" ) ) ;
				vJthFieldValue = GetAs ( vJthFieldType ; GetNthRecord ( GetField ( vJthField ) ; vIth ) ) ;

				vConditionFailed = not Compare ( vJthFieldValue ; vJthOperation ; vJthValue ) ;

				vJth = vJth + 1
			] ;

				//# return the outputField field for the record if all conditions match

				If ( vConditionFailed ; "" ; vID )
			) 
		) ;

	vIth = vIth + 1
] ;

	/* ========== RESULT ========== */

	vResult

)

Grabs value of a field for all records in a relationship that match given conditions. Basically, it is a way to filter a set of records through a relationship without changing or adding a TO and listing all of their IDs (or whatever other field you want to grab).

The conditions parameters needs to be a JSON array of objects. Each object should contain "field" (the field you're checking), "operation" (the operator you want to use while checking), and "value" (the value you want to check against the field). The list of operators you can send are located in the Compare function (https://www.briandunning.com/cf/2542).

I would not use this on records set that are larger in size because it will probably not be very fast, however I have not done any testing for this. The most records I've tested this on is around 20.

Comments

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

GetNthRecordSetFilter ( outputField ; conditions )

Comments

Support this website.

Support this website.