As I already described in the article about recusive custom functions, you can solve complex tasks very elegantly with the help of these functions.
In this article I describe a function that I myself needed for a solution at one of my customers.
I needed a simple solution that required the IDs of records from a portal (i.e. a table connected via a relationship) in which the field “Country” contains certain values.
This could certainly be easily solved using a script loop, but a script loop in Filemaker is never the fastest solution and usually not even the best solution.
If we formulate the problem, the functionality of the custom function becomes quickly clear:
Go through each line of the portal and test if the field “Country” contains one of the searched values. If yes, then note the ID otherwise ignore the ID.
So the custom function should check from the first to the second last line, enter the ID in a list if necessary and then pass the result list to itself and check the next line.
For the last line, only the check of the line is to be performed, the result list is to be extended if necessary and then the result list is to be returned.
The following function does this for one search parameter:
PortalFilter
/*
Fetches the field contents from a portal where the CompareFieldName has the content CompareValue.
ReturnFieldName = "Portal_1::ID"
CompareFieldName = "Portal_1::Country"
CompareValue = "DE"
counter = 1 or empty
CountRows = Get(Portal_1)
Returns all IDs from portal "Portal_1" where field "Country" has the content "DE"
Script:
PortalFilter ( "Portal::ID" ; "Portal::Country" ; "DE" ; 1 ; Get(Portal_1::ID) )
*/
Let (
[
counter = If (counter = 0 or counter = "" ; 1 ; counter ) ;
CountRows = If (CountRows = 0 or CountRows = "" ; counter ; CountRows )
] ;
Case (
CountRows > 1000 ; "Cancel - too many recursions"
;
counter < CountRows ;
List (
If ( GetNthRecord (GetField ( CompareFieldName ); counter) = CompareValue ;
GetNthRecord (GetField ( ReturnFieldName ); counter) ;
"" )
;
PortalFilter ( ReturnFieldName; CompareFieldName; CompareValue; counter + 1 ;CountRows ) )
;
counter = CountRows ;
If ( GetNthRecord (GetField ( CompareFieldName ); counter) = CompareValue ;
GetNthRecord (GetField ( ReturnFieldName ); counter) ;
"" )
)
)
What we have to do now is to call the function “PortalFilter” as a sub custom function in a main custom function for multiple search parameters.
This function calls the subfunction with each search parameter and then also executes itself until it is at the last search parameter.
MultiPortalFilter:
/*
Fetches a field from a portal where the CompareFieldName has the content CompareValue
(Can be a list of values).
ReturnFieldName = "Portal_1::ID"
CompareFieldName = "Portal_1::Country"
CompareValue = "DE¶AT"
counter = 1 or empty
CountRows = Get(Portal_1)
Returns all IDs from portal "Portal_1" where field "Country" has the content "DE" or "AT"
Script:
MultiPortalFilter ( "Portal_1::ID" ; "Portal_1::Country" ; "DE¶AT" ; 1 ; Get(Portal_1::ID) )
This function needs the „PortalFilter“ as a sub custom function
*/
Let (
[
maxCounter = ValueCount ( CompareValueList )
] ;
If ( counter < maxCounter ;
PortalFilter ( ReturnFieldName ; CompareFieldName ; GetValue ( CompareValueList ; counter ) ; 1 ; CountRows )
& ¶ &
MultiPortalFilter ( ReturnFieldName; CompareFieldName; CompareValueList; counter +1 ; CountRows )
;
PortalFilter ( ReturnFieldName ; CompareFieldName ; GetValue ( CompareValueList ; counter ) ; 1 ; CountRows )
)
)
Calling the MultiPortalFilter function is very simple. The only important thing is that the field names of the result field (here the ID) and the comparison field (the field “Country”) must be passed to the function with the table names as a string (i.e. in quotation marks):
MultiPortalFilter ( “Portal::ID” ; “Portal::Country” ; “DE¶AT” ; 1 ; Count ( Portal::ID ) )
That’s all.
You have any questions? Feel free to send me an email, I’ll try to answer as soon as possible.