Summary List As Relational Predicate, part 1

Disclaimer: This article features an undocumented technique. As with all material on this site, use at your own risk and test thoroughly.

One of my favorite features introduced in FM 13 is the summary list field type, which allows developers to easily access a list of field values for records in a found set.

1-31-2015 8-07-11 AM

The idea of using a summary list (indirectly, via a “helper field”) as a relational predicate is not new, and we explored it here on FileMaker Hacks last year:

At the time I wrote, “Unfortunately we can’t use a summary field as a relational predicate, so let’s define a calculated field with a text result, to echo the summary list field.”

1-19-2014 12-32-56 AM

Well it turns out I was wrong. I happened to repeat the above remark to Dr. Ray Cologon the other day, and he astonished me by replying, “Actually, it is possible to use a summary field as a relational predicate,” and then left me to ponder how one might accomplish this.

Hmmm. If you’ve ever tried to assign a summary field as a relational predicate…

1-31-2015 8-48-05 AM

…you’ve seen that it’s greyed out and FileMaker will not allow you to select it.

But what if you were to instead…

  • define the field as a text field (or any other non-summary type)
  • link it up relationally
  • then change it to a summary field?

Know what? It works like a charm.

1-31-2015 7-50-46 AM

I asked Dr. Ray if that was the approach he had in mind, and he replied:

Yep, that’s the procedure… a List summary field will then operate as a multi-key without the need for an ‘intervening’ calc.

And since the intervening calc is required to be unstored it chews up processor cycles, so directly connecting the summary field removes one step in what is otherwise a bucket brigade of sorts – meaning that it runs at about double the speed.

I’ve been using the technique since the day v13 was released, but was initially cautious — as I always am — in relation to anything that could be considered an undocumented feature.

However, while nothing is certain, I think this one is a reasonably safe bet. Internally, List summary fields return their result data typed as text, and it seems reasonable to expect they will continue to do so – and that’s really all that’s required to achieve the effect in this case (along with the ‘trick’ of setting it up that way…).

6 thoughts on “Summary List As Relational Predicate, part 1

  1. Daniel Wood (@FMweetbicks)

    Very interesting technique, thanks for sharing! So I’m thinking of how this could possibly be used. The most useful thing I can think of so far would be when running a report, and wanting to establish a relationship between any given record in the report, and the found set (for various reasons such as checking if it is the first record via relationship or whatnot).

    How would this work with sub-summaries? The list summary field would surely have a different list of values depending on where it is placed on the layout, but how does this translate to a relationship? Does it alway just list everything in the found set? I can’t see how it would know anything about sub-sumamries when it comes to the relationship? ANyhow, must test ! Let me know if you have found any good uses for this.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s