Here’s an interesting FileMaker challenge:
How can you dynamically slice off a set of values from a repeating field in a related table, the way you’d grab a column of values in a spreadsheet?
It’s easy enough to do this for a single repeating field in the current record, but how do you do this with a repeating field in a different table, operating not just on a single record, but an entire record set?
While the solution to this challenge isn’t immediately obvious, this post will explain how to handle this challenge quickly and elegantly with FileMaker 12. Follow along by downloading a copy of the completed demo file: Just_a_Slice. Here’s a quick peek at the finished solution:
Tools of the Trade
Repeating fields are one of FileMaker’s great secret weapons. When properly used, they can help denormalize and condense related data into compact and flexible structures that can save space and boost performance. In this example, we’ll use not only FileMaker’s native repeating field functions, but also Will Baker’s definitive set of repeating field custom functions.
Flattening Repeating Data
As the screenshot above suggests, we’ll create a matrix-like structure in the parent table from which we’ll take slices as desired. To prepare for this, the first step will be to create a field in the child table that will translate the child table’s repeating field into a flattened string, where each repetition’s value will be nestled between delimiters, like this:
<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |
We could try this using the native List() function to translate repeating fields into list format, then use Substitute() to replace the list’s carriage returns with delimiters. This will fail, though, because List() omits null repetitions. So if, say repetition 2 is empty, repetitions 3-5 will jump forward a slot, which will defeat our downstream calculations.
Instead, we turn one of Will Baker’s repeating list custom functions, RepListOmitTrailingNulls(), which returns a complete list of repetition values including null entries. This ensures that empty values won’t misalign the flattened structure, but will be properly delimited.
As a bonus, Will’s function dynamically determines the last repetition that’s populated and only returns list values up to that point, so the flattened values will scale perfectly for any number of repetitions, and will consume the minimum resources needed. Here’s how the flattened string looks in our child table:
Peeling Off a Slice
With this in place, back in the parent table, we now assemble a matrix-like structure using List ( child::flattenedValues ). This gives a list that’s structured like this:
<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |
<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |
<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |
Now we need a way to take a vertical slice. For this, we’ll create a custom function that accepts our list of flattened values and a target repetition number. Our function will step recursively through each value in our list, using the delimiters to locate and then extract the target repetition of the current line. Then, we’ll need it to recursively call itself, repeating this action for each line, and finally returning a list of these extracted values. Here’s our custom function, GetRepValuesFromList():
Let (
[
count_values = ValueCount ( values ) ;
value_1 = GetValue ( values ; 1 ) ;
values_other = MiddleValues ( values ; 2 ; count_values-1 ) ;
pos_start = Position ( value_1 ; "|" ; 1 ; rep-1 ) + 1 ;
pos_end = Min ( Position ( value_1 ; "|" ; 1 ; rep ) ; Length ( value_1 ) ) ;
chars = pos_end - pos_start ;
rep_value = Middle ( value_1 ; pos_start ; chars )
] ;
Case (
count_values = 0 ;
"" ;
count_values = 1 ;
rep_value ;
List (
rep_value ;
GetRepValuesFromList ( values_other ; rep )
)
)
The String’s Not the Thing
Our custom function works perfectly, letting us fluidly slice any vertical set of repetitions at will, and returning these values to us as a list. Now all we need to do is to get the returned column’s total. Easy peezy… just replace our list’s carriage returns with semi-colons and pop it into a native Sum() function, right?
Well, no, not so fast. Sum (<slicedValues> ) may produce a perfectly valid-looking expression when viewed in a Data Viewer. But it’s not a valid expression, because a sliced value string like “22;11;21;” is, well, just a string, rather than an actual set of delimited numeric values, as aggregate functions require. Happily, this is quickly resolved by assembling our full expression as a string and then wrapping that string in turn within an Evaluate() function, like so:
Evaluate ( “Sum ( ” & values & ” ) ” )
Evaluate() kindly transforms our string into a valid expression, and our Sum() function now works like a champ. Et voilá! We’re finished!
Worth Repeating
This is a simple example of how flexible and powerful FileMaker 12 repeating functions can be. Our technique is light and fast, and it can scale flexibly to any number of child repetitions or child records without modifications.
Note also that this technique can be readily extended in several ways. For example, to allow users to search from a parent table for child records containing values in a specific repetition, or to use aggregate functions to further analyze the related repeating data.
Download the demo file: Just_a_Slice … and let us know what you think.
Enjoy!
– Brian