Category Archives: Summary List

Found Set Awareness Revisited

Recently a colleague contacted me with some questions about making ExecuteSQL found-set aware, and I realized that while I had addressed aspects of the problem in various blog postings over a multiyear period (for example, here and here), I had not assembled my thoughts into one cohesive, up-to-date article.

Well, that shortcoming has now been addressed, and today we’re going to look at three demo files, all of which rely on the summary list field type (introduced in FM 13).

9-20-2015 8-52-01 AM Continue reading

FM 13: Anti-deduping, part 2

In part one, we explored various “anti-deduping” techniques. As you may recall, the challenge was to retain duplicates and omit unique entries from within an existing found set, as opposed to starting from all records… otherwise we could have just searched on ! (find all duplicates), but the ! operator does not play nicely with constrain, and cannot help us with this particular challenge.

Update 9 May 2015: it turns out that there is a way to reliably use contrain with the ! operator from within a found set — see Ralph Learmont’s technique + great demo + explanation here — Successfully Find Duplicate Values Within A Set Of Records

Also, last time, we were seeking to omit unique zip (postal) codes, which, in the example file, were always five digits long, i.e., of fixed length. Today our goal is to omit unique (a.k.a. “orphaned”) first names, and it turns out there are some additional challenges when the field in question is of variable length, which we will explore in today’s demo file: Anti-deduping, part 2.

10-11-2014 5-25-21 PM

Question: if we say we want to locate “all the Marias”, what do we really mean? Here are some possibilities… Continue reading

FM 13: Anti-deduping, part 1

Recently there was a question about cleaning up a found set on one of the FileMaker discussion forums. When a question of this nature arises, it’s typically some variation on “How can I remove [or delete] duplicate entries?” But this was the opposite: For a given found set of customers, how can I omit those whose Zip codes only appear once in the found set?

In other words, keep the records whose Zips appear multiple times and banish the others.

Note that the challenge was starting from a subset of records. If the challenge had been for all records in the table, one could simply search on ! (find duplicate values) in the Zip code field. However, this trick won’t work when starting from a found set rather than all records. And constrain won’t help here because it doesn’t play nicely with the ! operator.

Update 9 May 2015: it turns out that there is a way to reliably use contrain with the ! operator from within a found set — see Ralph Learmont’s technique + great demo + explanation here — Successfully Find Duplicate Values Within A Set Of Records

Off the top of my head, I suggested…

Sort by Zip code, then loop through the found set from top to bottom… using GetNthRecord() test the current record’s Zip code against the previous record and also against the next record. If both tests are negative, omit, otherwise go to next record (and of course exit after last).

As it turned out, it was a one-time cleanup task, and my suggestion was good enough. But I had a nagging feeling there were better-performing ways to go about this, and today’s demo file,  Anti-deduping, part 1, presents four different methods. I encourage you to download it, experiment, and add your own methods or variations… perhaps you’ll come up with a faster approach, in which case, needless to say, I hope you’ll post a comment at the end of this article.

9-21-2014 2-36-38 PM

Continue reading

FM 13: Summary List + Virtual List

Recently a question came up on the FMP Experts list re: the possibility of displaying a found set of customers in 3 columns in browse mode in FileMaker 13. While FileMaker doesn’t naturally display data this way, there are various ways to trick it into doing so, and today we’re going to look at a method that combines the new-in-13 Summary List field type with the Virtual List technique.

Demo file: FM 13 Summary List + Virtual List

2-1-2014 10-03-20 PM

Continue reading

Summary List Fields in FM 13, part 2

Welcome back for our second look at the new summary list field type in FileMaker 13. Today we have three demo files…

…which in addition to summary lists, utilize a couple other new-in-13 features: popovers and conditional invisibility. As in part 1, the core technique uses a summary list to facilitate relational found-set awareness (in demos 1 & 2). We’ll also apply a modified version of the technique to SQL queries (in demo 3).

Demo 1: FM 13 Summary List – MLK, v2  (reminder: we saw v1 in part 1)

1-19-2014 9-41-31 PM

Continue reading