1. Pages:
  3. 1
  4. ...
  5. 19
  6. 20
  7. 21
  8. 22
  9. 23
  10. 24
  11. 25
  12. 26
  13. 27
  14. 28
Level: Beginner, Version: FM 8 or later

Locating Matching Records, part 1

Kevin Frank

Note: FileMaker 12, released a few months after this article was written, introduced a “Find Matching Records” step, effectively eliminating the need for the techniques explored below.

Sometimes a seemingly-simple FileMaker challenge turns out to be more nuanced and educational than first impressions might indicate. This happened recently when I was asked to help make a scripted search behave properly. Most of the time, the existing routine worked correctly, but on certain records it would fail.

The challenge: Click a button to find all records with the same Note text as the current record.

No problem — how hard could that be? Any competent FileMaker developer can do this in his or her sleep, right? Well sometimes properly defining the problem turns out to be half the battle. Later, after the smoke had cleared, I built a demo to explore various approaches one might take…

…and we’ll get to that in a minute, but right now let’s look at the original script and the problem, or rather, series of problems, as they initially unfolded. Continue reading “Locating Matching Records, part 1” →

Level: Intermediate, Version: FM 11 or later, Virtual List

Long Documents in FileMaker 11

Kevin Frank

Update 4 Sep 2012: Preliminary testing indicates that the “line swallowing bug” alluded to below has been fixed in FileMaker 12. Also, in FM 12, the maximum layout length and width have been increased to 444 inches (32,000 pt).

There was a time, many years ago, when the maximum number of characters you could store in a FileMaker text field was 64,000. With the introduction of FileMaker 7 in 2004, that limit was expanded to approximately one billion characters (2 Gb of data divided by 2 bytes per Unicode character), i.e., more than you or I will typically ever need.

But while the capacity of a text field expanded astronomically, the maximum length of a layout remained unchanged at 110 inches (ten 8.5 x 11 inch pages), leading to a dichotomous situation where FileMaker can store vastly more data in a field than it can easily preview, print or output to PDF.

For example, let’s say you’ve decided to build a data warehouse for public domain literary works. Continue reading “Long Documents in FileMaker 11” →

Level: Intermediate, SQL, Version: FM 11 or later

FileMaker 11 Internal SQL Changes, part 2

Kevin Frank

Welcome back for a second action-packed episode, as we continue exploring changes to the FileMaker 11 internal SQL parser. We ended part 1 by looking at the INSERT/SELECT construction which, as you may recall, provides the SQL equivalent of a FileMaker “add new records” import from one table to another.

Actually, when you use INSERT/SELECT, if you wish, the source and target tables can be the same table (or, rather, table occurrence), which once in a while can come in handy, and is something you cannot do via a regular FileMaker import. For that matter, you can also use INSERT/SELECT to map a single source field to multiple target fields, which is another thing a regular FileMaker import cannot do… but I digress.

Continue reading “FileMaker 11 Internal SQL Changes, part 2” →

Level: Advanced, SQL, Version: FM 11 or later

FileMaker 11 Internal SQL Changes, part 1

Kevin Frank

March 9, 2010 is a date I recall quite clearly: not only was it the release date for  FileMaker 11; it was also the day a bunch of my FileMaker SQL code broke, due to changes in the FM 11 internal SQL parser.

If you’re new to this subject, or perhaps a bit rusty, I have written about internal SQL (a.k.a. FQL) on various occasions over the last year, and there are a number of important points that I won’t discuss today, because they have already been mentioned in one or more of these articles:

What follows is by no means exhaustive, but merely what I’ve documented. There is no comprehensive source of information about FQL, although chapter 7 of the FileMaker 11 OBDC and JDBC Guide is a good place to start (just remember that not everything you read there will apply to FQL).

Continue reading “FileMaker 11 Internal SQL Changes, part 1” →

Level: Intermediate, Version: FM 11 or later

Summary Report in a Filtered Portal

Kevin Frank

Have you ever wished there were an easy way to summarize the contents of one portal in another portal? Perhaps something along the lines of the “Summary” portal below?

I’ve touched on the possibility of using a portal to produce (or assist in the production of) an on-screen summary report a couple times in the past…

…but today we’re going to look at a fresh approach, and if you wish, you can follow along in today’s demo file, fm11-summary-report-in-a-portal, which uses a filtered portal and therefore requires FileMaker 11 or later. Continue reading “Summary Report in a Filtered Portal” →

Level: Intermediate, Version: FM 10 or later

Easy Sorting of List Views, part 3

Kevin Frank

Update 22 Jan 2013: Demo file and screen shots have been revised to fix bugs identified by Matt Ayres and David Schwartz (see comments at the end of article).

Ever since I posted part 2 of this series, I’ve been torn between, on the one hand, wanting to move on to other topics, and on the other, the realization that I wasn’t quite done with this one yet. So, here is what I expect will be my final posting, and final demo (dynamic list sorting, v3 rev5), on this subject.

Thus far, we’ve looked at various methods to facilitate dynamic list sorting (by “dynamic” I mean that the field to be sorted is determined programatically). Most of these methods use two fields — one of them uses four — and you can see them all in part 2.

But in the back of my mind has been the knowledge that Ugo Di Luca pulled this off with a single field back in 2004 (EasySort.fp7, shared by permission of the author, and previously discussed last April in an article entitled Portal Sorting, pt 3).

Continue reading “Easy Sorting of List Views, part 3” →

Level: Intermediate, Version: FM 10 or later

Easy Sorting of List Views, part 2

Kevin Frank

Well, I thought I’d said everything I had to say on this subject, but yesterday afternoon I noticed a glaring omission in part 1’s demo — what happens if the user manually unsorts the found set?

The sort indicator doesn’t disappear the way a good little sort indicator should. Fortunately this is easily remedied, thanks to the Get(SortState) function. Continue reading “Easy Sorting of List Views, part 2” →

Level: Intermediate, Version: FM 10 or later

Easy Sorting of List Views, part 1

Kevin Frank

Earlier this year, I posted a three-part series on Portal Sorting, and part 2 focused on dynamically sorting a portal when a column heading was clicked. Well, with just a few tweaks, this technique can be applied to dynamic sorting of found sets, and of course the most likely place to employ something like this would be on a list view.

I should note that on very large found sets, sorts using this technique can be noticeably slower than traditional “hard-coded” scripted sorts. (Performance is fine with normal found set sizes.) The benefit of using this technique, is that a new field can be added to a layout and sort-enabled in about 60 seconds without touching the script itself.

Continue reading “Easy Sorting of List Views, part 1” →