GetNthRecordFiltered ( primary_fieldname ; controlvalue_fieldname, recordNumStart ; recordNumEnd ; filterValue )

This function enables you to select a set of related records of a single field based on a value of a second field that is filtered by a keyword.

Average rating: 4.1 (39 votes) Log in to vote

Kenneth Durrum - Show more from this author
Released Solutions
https://releasedsolutions.net

Sample input:

GetNthRecordFiltered (locationName, FoodServed,1,100,"Pizza")

Sample output:

Pizza Hut
Johnny's Pizza
Pizza World
Sal's Pizza
Sabarro

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

Let (
[
// ------ BEGIN VARIABLE DECLARATIONS BELOW -----
startParam = recordNumStart ; 
start = Case ( startParam < 1 ; 1 ; startparam ) ; 
end = recordNumEnd ; 
thevalue = filterValue;
thisVal = Case ( // this limits "?" result on empty related records
                           PatternCount ( GetNthRecord ( controlvalue_fieldname ; start ) ; thevalue ) > 0 ; 
                                                                   GetNthRecord ( primary_fieldname ; start )

                        ) ; // END CASE
nextStart = start + 1
] ;
// ----------- FUNCTION RESULTS BELOW -----------

thisVal & 
Case ( 
                 
 // TEST : Either no end Value given or next value is within the end value
        
 ( ( end = "" or end = 0 ) or nextStart <= end )  ; 

                   // RESULT: Return Character & Recurse again
         If(Length(thisVal) > 1;         "¶" ; "")  & GetNthRecordFiltered(primary_fieldname;controlvalue_fieldname;nextStart;end;thevalue);
        ) // END CASE

) // END LET

Use this function to obtain a list of values based from a field based on the filtering of another field. In the example listed in the sample input we have a field called locationName which holds a list of eatery locations. In the FoodServed field in the same table as the locationName field we have store the values of the food items served at the location.

The function loops through the return values of the related field and only returns the locationNames of the records that contain the filterValue in the FoodServed field.

Comments

		Steve Woods, Antiquity Software Nov 19, 2018
I think there is a bug where related values that are a single character get merged with next related value. In line 25, should be Length(thisVal) > 0 instead of Length(thisVal) > 1 i.e. If(Length(thisVal) > 0; "Â¶" ; "") & GetNthRecordFiltered(primary_fieldname;controlvalue_fieldname;nextStart;end;thevalue);

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

GetNthRecordFiltered ( primary_fieldname ; controlvalue_fieldname, recordNumStart ; recordNumEnd ; filterValue )

Comments

Support this website.

Support this website.