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

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.

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

The XML Wrap and Unwrap technique

Editor’s Note: today we have a guest article with demo files by Italian developer Giuseppe Pupita, whose helpful and knowledgeable postings in various online forums are always worth reading. Here he presents a technique to move data between disconnected FileMaker systems.

11-16-2014 7-15-18 PM

It is becoming increasingly necessary to move easily and reliably data from one file to another, and I’ve been recently faced with a similar problem. Continue reading