Tag Archives: Summary List

Transfer a Found Set to a UI File

If you work with the separation model, or any other multi-file configuration where a layout in file A is based on a table in file B, you may have encountered the following situation: while FileMaker makes it easy to transfer a found set from file A to file B…

2016-08-31_195116

…there is no obvious mechanism to go the other direction. Stated in other words, you can easily transfer a found set from an interface (a.k.a. UI) file to a data file, but not vice-versa. Nor for that matter, is there an obvious way to transfer a found set from one UI file to another UI file. Continue reading

FM 13: Pseudo Indexing

If you’ve spent much time working with FileMaker Pro, you are probably familiar with the ability to view a field’s index.

To view a field index, put your cursor inside any indexed field and press Cmd-i (on the Mac) or Ctrl-i (on the PC) to see a list of unique (non-duplicated) entries for that field.

It’s a great feature, but it has some limitations:

  1. Field indexes are not found set aware — you get unique entries for all records
  2. The field must be on the layout (and enterable)
  3. The field must be indexed
  4. When viewing a field index, it’s possible to accidentally insert data into the underlying field
  5. You can’t copy what you see — it’s view only
  6. If you’re doing a screen share, and zoom the database up to a higher magnification, the index window does not correspondingly zoom

Well today we’re going to look at a technique to circumvent these limitations, and I invite you to follow along in today’s demo file, FM 13 Pseudo Indexing, if you are so inclined. Continue reading

Global Multiline Key As Relational Predicate

This is a quick follow up to my previous article on using a Summary List field as a relational predicate, and transferring a found set from table A to the corresponding found set in table B across that relationship via the Go To Related Record (current record) script step.

To avoid needless repetition, I will assume the reader has read that article. As discussed in the comments section (thank you Mardi Kennedy and Jerry Salem), there is a tried and true method for going to a found set of related records that has worked since FileMaker Pro 3 was released in December 1995:

  1. Define a global text field
  2. Link this field relationally to the primary key field of another table
  3. Populate the global text field with a list of keys
  4. Go to related records (a.k.a. GTRR)

2-17-2015 9-30-13 AM Continue reading

Summary List As Relational Predicate, part 2

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

8 Feb 2015: Demo updated to include a “refresh window / flush cached join results” step before GTRRing across a summary list relationship

Last week we looked at a technique to allow a summary field to be used as a relational predicate, directly, i.e., without the need to use a “helper field” as a predicate. If you’re not comfortable employing an undocumented hack in your solutions, I encourage you to read on anyway, because what you’re about to see may still be worth doing, whether you use last week’s method, or the safer (but a bit slower) “helper field” approach as delineated here on FileMaker Hacks last year in Summary List Fields in FM 13, part 1.

We’ve established that a summary list field can be used as a relational predicate (either directly or indirectly); the aim of today’s article and demo file, FM-13-Summary-List-As-Predicate, is to offer a compelling reason to do so. Continue reading

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:

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

Unique Records Revisited… again, part 2

In part 1, we looked at a new way to count unique entries in a sub-summary report based on an arbitrary found set of sales data. To avoid redundant explanation, this article will assume the reader is familiar with part 1, but in a nutshell…

1. we used a pair of FM 13 summary list fields
2. to create found-set-aware multiline keys
3. which we related to special purpose table occurrences
4. which provided the basis for related value lists of salespeople
5. the ValueListItems function allowed us to grab lists of unique salespeople
6. and the ValueCount function allowed us to count them

The demo in part 1 was based on a single “flat table” of data. Today we’re going to unflatten that table, and see how the technique can be applied to a properly normalized system, i.e., with separate tables for Sales, People and Zones, related like so…

graph 1

Continue reading

Unique Records Revisited… again, part 1

Several recent postings on this blog have offered variations on a theme: using the new-in-13 summary list field type as the basis for a multiline relational key. The net effect is to produce a relationship that is found-set-aware. (If you aren’t familiar with summary lists, you can read about them here.)

Last week we looked at basing a value list on one of these “summary list relationships”, to facilitate counting unique values within the current found set…

4-2-2014 8-02-41 AM

…and today we have a demo file, FM-13-Count-Unique-v1, that extends the concept to sub-summary reporting.

4-1-2014 10-57-14 PM

Continue reading

FM 13: Column Chart on Found Set, revisited

Today I’m going to address a shortcoming in the “FM 13 Column Chart on Found Set” demo in my previous article. Specifically, I took a lazy path of least resistance re: calculating the chart title.

3-27-2014 11-47-00 AM

The challenge was to count the number of states in the found set, and since there weren’t many records in the original demo I decided to use a recursive custom function to build a list of unique states, and then the ValueCount function to count the number of entries in that list.

And that was fine. When the found set was small. Continue reading