FoundSet_List ( field, isUniqueList )

Create a List or a Unique List of values from one field from the found set of records.

Be the first to rate this function Log in to vote

Kyle Williams - Show more from this author
Dreamclinic Massage
https://www.youtube.com/channel/UCpJlB7cmVNMjbjgY96MEsng

Sample input:

FoundSet_List ( Contacts::nameFirst, 1 )

Sample output:

James
John
Judy
Kyle
Michael

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

/*
Author:  		Kyle Williams
Date:    		 9/24/2019
Purpose: 		To pull all values (or unique values) of one field from the current found set
Dependencies: 	None
*/

Case (
    not $LoopCount
    ;Let ( [
 	 $FoundCount = Get ( FoundCount )
        ;$LoopCount = 1
     ];
        FoundSet_List ( field ; isUniqueList )
     )
    ;$LoopCount < $FoundCount
    ;Let ( [
	 value = GetNthRecord ( field ; $LoopCount )
	;$AnyText = If( IsEmpty(value);$AnyText; If( IsEmpty( $AnyText ); value; $AnyText & ¶ & value ))
	;$AnyText = If( isUniqueList; UniqueValues( $AnyText); $AnyText)
        ;$LoopCount = $LoopCount + 1
     ];
        FoundSet_List ( field ; isUniqueList  )
     )
    ;$LoopCount ≥ $FoundCount
    ;Let ( [
	 value = GetNthRecord ( field ; $LoopCount )
	;$AnyText = If( IsEmpty(value);$AnyText; If( IsEmpty( $AnyText ); value; $AnyText & ¶ & value ))
	;$AnyText = If( isUniqueList; UniqueValues( $AnyText); $AnyText)
        ;$LoopCount=""
 	;$FoundCount = ""
        ;$FieldName =""
	;$AnyText = If( isUniqueList; SortValues( $AnyText); $AnyText)
//UniqueValues function creates a blank entry which gets sorted to the first entry:
	;$AnyText = If( isUniqueList; If(Left($AnyText; 1) = ¶; Right($AnyText; Length($AnyText) - 1 );$AnyText);$AnyText)
 	;AnyText = $AnyText
 	;$AnyText = ""
     ];
        AnyText
     )
)

Create either a List or a Unique List of values by referencing one field from the found set of records.
NOTE: Founds sets of 10,000 or more records may take a long time to complete...

Comments

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

FoundSet_List ( field, isUniqueList )

Comments

Support this website.

Support this website.