De-Duplicate Within A Found Set Of Records

15 May 2015: Demo and article have been updated to make the technique 100% portable.

This is a quick follow up to the Ralph Learmont technique I posted the other day, in response to a comment seeking a generic technique to de-duplicate from within a found set.

Clearly there are various ways to skin this particular cat, and I have a suspicion my initial attempt can be further improved upon, but using Ralph’s demo as a starting point here’s what I’ve come up with:  De-Duplicate-A-Found-Set.zip

5-11-2015 10-47-47 AM

Bear in mind that the goal here is for the script to be 100% portable, i.e., you should be able to copy and paste this script into your solution and run it without any modification at all.

Continue reading

Successfully Find Duplicate Values Within A Set Of Records

The need to isolate duplicates within an existing found set has often been a source of frustration for developers. A couple weeks ago we examined a technique by Ralph Learmont demonstrating that, contrary to popular belief and experience, the ! (find duplicates) operator can, under certain conditions, be coerced into isolating duplicate values within a found set.

4-24-2015 3-24-21 PM

Here’s Ralph’s original statement of the problem:

There’s a quirk in the way Filemaker deals with duplicate records. This makes it difficult to find duplicate records WITHIN a found set of records. If you try to constrain the Find to the current set of records, you might discover extra spurious records appearing. These records have “partner-duplicates” outside of the found set. These unwanted spurious records will appear as single occurrences when you inspect a sorted column. Technically they are duplicates. It’s just that their partners lie “outside”, hidden in the omitted slab of records.

And why it’s a cause for concern:

I think it’s important to explain why finding duplicates in a found set is something that often appears to work… and it won’t be apparent there actually IS AN ISSUE unless one goes to the trouble of actually sorting results and checking for those “single-occurrences”.

While the technique was blazingly fast, it seemed to me there was something a bit voodoo-like about Ralph’s previous demo, as well as my attempt at simplification. Apparently Ralph wasn’t satisfied with it either, because he has provided a new demo (Find Duplicates in Found Set) showcasing a more straight-forward technique, and kindly agreed to share some of his thoughts about it as well. Continue reading

FM 13: Anti-deduping, part 3

Today we have two great examples of using demos from this site as a starting point and making significant improvements: one from Malcolm Fitzgerald in response to FM 13: Anti-deduping, part 1, and one from Ralph Learmont in response to FM 13: Anti-deduping, part 2.

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 since the ! operator does not play nicely with constrain, it was apparently not an option for this particular challenge.

Or so I thought. These gentlemen convincingly prove otherwise — and like all great techniques, what you’re about to see has the potential to be useful in a variety of situations, not just the narrow confines of this particular challenge.

Malcolm Fitzgerald – Example #1

4-25-2015 5-06-32 PM

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

Interview with Ray Cologon

Today it’s my distinct pleasure to present an interview with Dr. Ray Cologon, whose contributions to the FileMaker community over the last decade and more have been invaluable, and whose DevCon sessions are always packed. To add your name to a priority list for future or alternate Master Classes, complete the online expression of interest form at the NightWing Enterprises site.

You’re currently offering FileMaker Master Classes in a number of locations, including two within the US. How did that come about?

The classes being offered presently are in a sense a sequel to two classes that were offered in 2014 in Berlin and London. The original impetus came from Egbert Friedrich of FileMaker Mentoring in Berlin, who invited me to consider presenting developer-oriented material for a Berlin class. It really grew out of that.

I was interested in the idea because my own experience has been that there has been very little available to take people beyond the basics and the levels of ability required (for example) for certification. There are a few DevCon sessions each year that are listed as advanced, and there are some online resources (of which filemakerhacks is one, in fact) that delve into deeper issues. But there has been no coherent framework to address the concepts and challenges that advanced developers face.

What I think essentially sets these events apart is that the content is broad-ranging, and the topics are inter-related. It’s not taking a specific area such as user interaction or interface design in isolation. Those things are definitely under discussion, but as part of a much wider agenda.

So the Master Classes are intended for advanced developers. What does that mean, and who would you consider to be at an appropriate level to take the class?

Yes – this is three days straight of advanced content, with a pretty densely packed list of topics. It really assumes people don’t just know all the basics already, but that they’ve built and deployed some sizeable systems and have a sense of the complexities and challenges that go with that. 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

Non-Breaking Reporting Groups

Today we’re going to look at a reporting challenge that comes up from time to time, and a technique that can be useful to have in your developer bag of tricks.

In the demo file, Non-Breaking-Groups, we have a simple personnel file containing employees spread across all 50 U.S. states, with between two and nine employees per state.

1-20-2015 8-45-43 PM

We have a requirement to produce a report showing personnel grouped by state, like so:

1-20-2015 4-16-27 PM

Continue reading

What is happening when FileMaker Server becomes overloaded (and how to avoid it)

Editor’s Note: Today I am pleased and honored to present the first in what I hope will become a series of articles by guest author Nick Lightbody of Deskspace Systems Ltd.

Summary: we will describe, discuss and illustrate the statistics that enable you to understand the why and how of FileMaker Server performance and suggest means of delivering a predictable and acceptable performance to your users.

Why this is important

FileMaker Server 13 is a wonderful and very reliable product, provided (as with any product) you recognise, understand and work within its limits.

However, Server is a binary product, in the sense that it either performs “good” or it performs “bad” — very slowly, but very reliably — as it grinds through its backlog until its load has reduced sufficiently for it to catch up on its queued calls and return to “good” mode.

The Deskspace server performance test shown in fig 1 illustrates a common scenario as the number of users increases and suddenly performance declines – dramatically.

fig1 -

fig1 – user numbers increase until Server chokes – suddenly and dramatically – with little warning.

There really is very little middle ground, so when you look at the server statistics and watch the graph crawling along the floor — thinking that you are not really using its full capacity — you may in fact be deluding yourself, as we will illustrate. Continue reading