While I’m still writing the next larger article, “Week/Month Planner”, I’ll quickly slip in a short article about one of
Filemaker’s most powerful and best features.
I am talking about the WHILE function.
It is compact, fast and allows very elegant solutions to common problems with found sets and portals.
In many situations you need data from such a result set – this can be a list of UUIDs as multikey for a relation,
or a value from one of the fields.
The WHILE function is so powerful because it contains within itself the functionality of the LET command.
In fact, a LET function with a built-in loop is in the formula part.
So you can use variables locally in the WHILE routine, or set them locally/globally for a script and get only
certain field values even with a condition as a filter.
The command can be used in a formula or a script, but also allows very powerful CUSTOM FUNCTIONS.
So, let’s code !
To process all records in a relation (or portal), the WHILE function can be used in this form.
The first example simply creates a list of the uuidNumber of the related portal rows:
While (
[
max = Count ( relatedTable::anyField ) ; // Number of passes through the "While" loop
out = "" ; // initial value of the output
i = 1 //initial value loop counter
//you can add here more variables for the loop
] ;
i ≤ max ; // loop until last row
[
out = List ( out ; GetNthRecord ( relatedTable::uuidNumber ; i ) ) ; // Get field "uuidNumber" of the
portal row at the index i from the related table
i = i + 1
] ;
out // return value
)
Next we use a condition to get only uuidnumbers that match the condition
We define a numeric value in the custom function as the adjustment value – this can also be defined as a local (or global $$ variable) in the script.
While (
[
max = Count ( relatedTable::anyField ) ; // Number of passes through the "While" loop
out = "" ; // initial value of the output
i = 1 ; //initial value loop counter
$number = 11
] ;
i ≤ max ; // loop until last row
[
out = List ( out ; If ( GetNthRecord ( relatedTable::number ; i ) = $number ;
GetNthRecord ( relatedTable::uuidNumber ; i ) ; "" ) ) ;
// out gets only uuidNumber where relatedTable::number = 11
i = i + 1
] ;
out // return value
)
Please note that the filter condition must also be present in the WHILE function of a filtered portal, otherwise the values from the unfiltered portal will be returned.
Portal filters are always optical filters that refer to the layout.
Similarly, you can also loop through a found set of records.
You only have to adjust the line with the determination of the maximum loop value
max = Count ( actualTable::anyField ) ;
and then access the current table in the loop.
out = List ( out ; GetNthRecord ( actualTable::uuidNumber ; i ) ) ;
So the function for the found set looks like this:
While (
[
max = Count ( actualTable::anyField ) ; // Number of passes through the "While" loop
out = "" ; // initial value of the output
i = 1 //initial value loop counter
] ;
i ≤ max ; // loop until last row
[
out = List ( out ; GetNthRecord ( actualTable::uuidNumber ; i ) ) ; // Get field "uuidNumber" of record at
the index i from the actual table
i = i + 1
] ;
out // return value
)
You can certainly solve much more complex problems with the WHILE function (from my own experience I can say that you can also recursively nest WHILE functions, which definitely leads to headaches for me), but this article should show a simple recipe for this function.
You have any questions? Feel free to send me an email, I’ll try to answer as soon as possible.