Working with Repeating Fields

Demo file:  RepeatingFieldFunctions.fp7

I began using FileMaker at the spry old age of FileMaker Pro 8, so I’ve never been through the trenches of using repeating fields to accomplish what can now be done with portals. However, repeating fields still have a variety of uses, and I’m happy to have them in my toolkit.

When I’m working with a repeating field, there are several questions I might ask of it, depending on the task at hand.

• What is the nth value?
• How many total repetitions does the field have?
• How many repetitions contain values?
• How many repetitions are empty?
• What is the first non-null value?
• What is the first repetition number that contains a value?
• What is the first null repetition number?
• What is the last non-null value?
• What is the last repetition number that contains a value?
• What is the last null repetition number?
• What is the nth non-null value?
• What is the list of all values, excluding nulls?
• What is the list of all values, including nulls?
• What is the list of all values, including only nulls that precede the last non-null value?
• What is the list of repetition numbers containing a non-null value?
• What is the list of repetition numbers containing a null value?
• What is the list of repetition numbers containing a specific value?

That’s a lot of questions. And FileMaker only provides us with three repetition-savvy functions to help in answering them. Many of our answers to these questions will be similar to (if not identical to) the answer if we asked the same question of a list, because a repeating field is a type of list. However, as a special class of list, a repeating field cannot always be interrogated in exactly the same way.

Let’s tackle each question one at a time. I’m going to hop around a bit, answering the easy questions first. For all questions, we’ll be looking at TABLE::FIELD, partitioned into 10 repetitions, and with the following values.

 

1. What is the nth value?

Simple. We use the native function:

   GetRepetition ( TABLE::FIELD ; 3 )

returns

   green

 

2. What is the list of all values, excluding nulls?

Also simple, as List operates on a single repeating field much as it operates on related data.

   List ( TABLE::FIELD )

returns

   blue
    green
    red
    yellow
    yellow
    pink

Now that we’ve turned the repeating field into a simple list, we can easily answer the next few questions.

 

3. How many repetitions contain values?

   ValueCount ( List ( TABLE::FIELD ) )

returns

   6

But even better:

   Count ( TABLE::FIELD )

returns

   6

 

4. What is the first non-null value?

   GetValue ( List ( TABLE::FIELD ) ; 1 )

returns

   blue

 

5. What is the nth non-null value?

   GetValue ( List ( TABLE::FIELD ) ; 3 )

returns

   red

 

6. What is the last non-null value?

We could continue to leverage our previous solutions, to come up with something like this:

   GetValue ( List ( TABLE::FIELD ) ; ValueCount ( List ( TABLE::FIELD ) ) )

returns

   pink

But better to use the second of FileMaker’s native repetition functions.

   Last ( TABLE::FIELD )

returns

   pink

Okay, that’s it for the easy ones. The rest we’ll need to work for. This next question will ultimately result in our most important function. As we get into more complicated questions, we’re going to use recursion to traverse the list; and we can’t use recursion unless we have an upper limit to describe our exit condition.

 

7. How many total repetitions does the field have?

There are several ways to attack this question. First, we could just hardcode the value, since this is a schematic configuration and can’t change at runtime. Second, we could put the fully extended field on a layout somewhere, then detect its repetitions using FieldRepetitions. But let’s find something more elegant.

   FieldType ( Get ( FileName ) ; GetFieldName ( TABLE::FIELD ) )

returns

   Standard Text Unindexed 10

So we’re in luck. This design function suffixes the field description with the maximum defined repetitions.

   GetAsNumber ( FieldType ( Get ( FileName ) ; GetFieldName ( TABLE::FIELD ) ) )

returns

   10

Not only does this work on calculated and summary fields, but it also works on fields with only one repetition. This formula is useful enough that we’ll store it in a custom function, FieldReps ( field ).

 

8. How many repetitions are empty?

   FieldReps ( TABLE::FIELD ) - Count ( TABLE::FIELD )

returns

   4

 

9. What is the list of all values, including nulls?

List is useful for non-null values, because it automatically strips out the nulls. But this can be problematic if we’re trying to compare two lists side-by-side; we don’t want values shifting vertically. When I was first getting started in FileMaker, I heavily used a function adapted from John Mark Osborne’s ListSet. It uses GetNthRecord to traverse a found set, scooping up values, effectively using List on a found set. We’ll use a similar approach here. Beginning with the first repetition, and continuing until we have considered each repetition, return the value of that repetition (inserting a carriage return between values).

We’ll start with a shell function: RepList ( field )

   RepListLoop ( field ; FieldReps ( field ) ; 1 )

Which in turn calls a sub-function: RepListLoop ( field ; repMax ; rep )

   Case (
        rep > repMax ; ""
        ;   Case ( rep = 1 ; "" ; ¶ )
        &   GetRepetition ( field ; rep )
        &   RepListLoop ( field ; repMax ; rep + 1 )
    )

 

10. What is the list of all values, including only nulls that precede the last non-null value?

There might be any number of reasons why we want to stop building the list when we hit the last non-null value, but let’s look at the problem purely from a performance perspective. Assuming we don’t care about trailing nulls, we’re only wasting cycles by gathering them. And since a repeating field may contain up to 32,000 repetitions, we’re potentially saving on tens of thousands of unnecessary recursions.

RepList does most of the work for us, but the problem is detecting an early exit point. I’ve seen a solution that counts backward from the end until it hits a non-null value, but this approach guarantees one recursion per repetition. Instead we’ll incorporate our solution from #3 as a decreasing counter. Every time we process a non-null value, we know we are one step closer to completion; no need to continue interrogating thousands of repetitions if we know we’ve already gathered all the values we came for.

RepListOmitTrailingNulls ( field )

   RepListOmitTrailingNullsLoop ( field ; FieldReps ( field ) ; 1 ; Count ( field ) )

RepListOmitTrailingNullsLoop ( field ; repMax ; rep ; maxValues )

Case (
    rep > repMax or maxValues = 0 ; "" ;
    Let ( [
        value = GetRepetition ( field ; rep )
    ;   maxValues = Case ( IsEmpty ( value ) ; maxValues ; maxValues - 1 )
    ] ;
        Case ( rep = 1 ; "" ; ¶ )
    &   value
    &   RepListOmitTrailingNullsLoop ( field ; repMax ; rep + 1 ; maxValues )
    ) // end Let
) // end outer Case

So far most of these questions have dealt with extracting values from the repeating field. But if we want to interact more fully with the field, we might instead want to know about the repetition number that corresponds to specific values. This could be useful for exchanging values (e.g. find ‘red’ and replace with ‘brown’), or perhaps for filling in gaps.

The best approach I know to solve these problems is to use recursion. However, recursion can be costly, especially as the number of repetitions grows. So we need to be as efficient as possible. The two main strategies I employ for recursion efficiency are:

• Wrapper functions, as we’ve already seen in the examples above. These are responsible for initializing counters (to make the function easier to use) and any constants (so we never recalculate a constant twice)
• Intelligent upper limits, so that we don’t continue to interrogate the field even though we’ve found everything we’re looking for.

Consider each of the following questions:

11. What is the first repetition number that contains a value?

12. What is the first null repetition number?

13. What is the last repetition number that contains a value?

14. What is the last null repetition number?

15. What is the list of repetition numbers containing a non-null value?

16. What is the list of repetition numbers containing a null value?

17. What is the list of repetition numbers containing a specific value?

These can all be abstracted (sort of) into the following question: find n repetition numbers matching value x. Using a little syntactical wizardry, we can decide that in the context of n, “” means find all repetitions, and “” (null) means find the last repetition; and in the context of x, we can decide that “” means find any non-null, and “” (null) means find any null.

As my custom functions grow in complexity, I find that the biggest challenge is naming my function parameters and Let variables intelligently, so that the code makes sense. In this case, depending on the parameters passed, we need to keep track of the following:

• value. What is the user looking for?
• matchTarget. How many matches does the user want? Just the first? The nth? The last? All?
• matchMax. How many values in the list match the target value? We must re-adjust matchTarget accordingly, because we don’t want to look for the 100th match if we already know there aren’t any, or are only a few.
• matchCount. How many matches have we found so far?

Armed with those parameters, we can quickly traverse the list, looking for matches, and returning one or more repetition numbers, as appropriate.

RepNumber ( field ; value ; matchTarget )

   Let ( [
        matchMax = Case (
            IsEmpty ( value ) ; FieldReps ( field ) - Count ( field ) // Syntax: Null values.
        ;   value = "*" ; Count ( field ) // Syntax: Non-null values.
        ;   ValueCount ( FilterValues ( List ( field ) ; value ) ) // Specific value.
        ) ;
        matchTarget = Case (
            IsEmpty ( matchTarget ) ; matchMax // Syntax: Return last match.
        ;   matchTarget = "*" ; matchTarget // Syntax: Return all matches.
        ;   matchTarget > matchMax ; "" // If there are fewer matches than sought by targetMatch, return nothing.
        ;   matchTarget
        )
    ] ;
        Case (
            matchMax and not IsEmpty ( matchTarget ) ; RepNumberLoop ( field ; value ; matchTarget ; matchMax ; 0 ; 1 )
        ;   "" // If the target value is not in the list, or there are too few matches, don't waste time searching the list.
        ) // end Case
    ) // end Let

RepNumberLoop ( field ; value ; matchTarget ; matchMax ; matchCount ; rep )

   Let ( [
        repValue = GetRepetition ( field ; rep )
    ;   match = Case (
            IsEmpty ( value ) and IsEmpty ( repValue ) ; True
        ;   value = "*" and not IsEmpty ( repValue ) ; True
        ;   value = repValue ; True
        ;   False
        ) ;
        matchCount = matchCount + match
    ] ;
        Case (
            match and matchTarget = "*" ; rep // Syntax: we found a value, but should continue to look for more.
        &   Case (
            matchCount = matchMax ; ""
            ;   ¶ & RepNumberLoop ( field ; value ; matchTarget ; matchMax ; matchCount ; rep + 1 )
        ) // end inner Case
        ;   matchCount = matchTarget ; rep // We found the value we're looking for.
        ;   RepNumberLoop ( field ; value ; matchTarget ; matchMax ; matchCount ; rep + 1 )
        ) // end outer Case
    ) // end Let

In the sample file at the top of this article, click each of the questions to see the result.