Category: Version: FM 12 or later

Level: Intermediate, Version: FM 12 or later

Disabling New Window

Kevin Frank

Today I want to talk about a potential problem lurking in a small percentage of FileMaker solutions, and discuss various options for mitigating it.

As you may know, there are three ways to display a new window…

  1. Via the New Window script step
  2. Via the Go To Related Record script step
  3. Manually via Window > New Window

…and it’s this last option that can, depending on circumstances, be problematical.

2019-02-23_13-06-51.png

I say “depending on circumstances” because typically it’s okay for a user to spawn a new window, which is to say that doing so doesn’t pose any challenges beyond those the developer already faces when building a multi-user system, which presumably the developer has considered and has intelligently dealt with.

Continue reading “Disabling New Window” →

ExecuteSQL, Level: Intermediate, SQL, Version: FM 12 or later

User-Friendly Excel Exports, part 7

Kevin Frank

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

This is a follow up to last week’s part 6, with three refinements.

Continue reading “User-Friendly Excel Exports, part 7” →

Level: Intermediate, Version: FM 12 or later

User-Friendly Excel Exports, part 6

Kevin Frank

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

29 Mar 2017: this file (Excel Exports, v6d) fixes a formatting bug when currency values < 1000 are not preceded by a “$”. Explanation forthcoming in next week’s “part 7” article.

Recently I was asked to implement user-friendly Excel exports on a WAN-based solution, utilizing some of the techniques explored earlier in this series (part 1, part 1.1, part 2, part 3, part 4 and part 5 — all from five years ago). There was just one problem: while performance was great locally, and okay on a LAN, it was decidedly less-than-satisfactory across a WAN.

Well today we’re going to look at three demos showcasing a brilliant, outside-the-box technique I picked up from Beverly Voth, which is not only significantly faster on a WAN (4x or 5x faster), but also remedies some other shortcomings associated with my earlier approaches.

Demo files:

Continue reading “User-Friendly Excel Exports, part 6” →

ExecuteSQL, Level: Intermediate, SQL, Version: FM 12 or later

FM/SQL Portal Filter + Dynamic Sort

Kevin Frank

Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.

Demo file: fm-sqlportal-filter-and-dynsort.zip (75 Mb compressed, 230 Mb uncompressed)

browseaksortcitycomp Continue reading “FM/SQL Portal Filter + Dynamic Sort” →

Level: Advanced, Level: Intermediate, Version: FM 12 or later

Set Variable by Name

Kevin Frank

28 Dec 2020: for further thoughts on this subject see Set Variable By Name Revisited.

29 July 2017: custom function updated to simplify the clearing of existing vars.

16 April 2017: custom function has been updated to auto-interpret dates (rather than treating them as sequential division problems)… e.g., to recognize “4/16/2017” as a date, as opposed to “4 ÷16 ÷ 2017”.

Recently, in the midst of various reporting and charting projects, I’ve found myself wishing for an easy way to “dynamically instantiate” one or more variables… in other words, set them by name, with the name determined programmatically, as opposed to being hard coded.

This issue has come up from time to time on this blog over the last five years or so, most notably in Dynamic Variable Instantiation, where I observed that…

2016-04-06_074440

…and then proposed an unwieldy mass of gobbledygook as a workaround, e.g.,

2016-04-06_083324

Actually, the preceding isn’t too bad for occasional use, especially if the value you’re passing to the variable is as simple as the GetSummary string shown above. Continue reading “Set Variable by Name” →

Level: Intermediate, Version: FM 12 or later

PDF Trickery

Kevin Frank

If you produce reports or other FileMaker output in PDF format, here’s a little trick that can be a real time saver in terms of development.

Imagine this: a client receives a nightly report via email in PDF format, and asks you to make sure it will fit on a single page in landscape orientation. The report always contains three groups of 12 records, with each group preceded by a leading subsummary, a latticework of horizontal and vertical gridlines, etc.

It is a busy, crowded report, but with patience and fine tuning, you manage to get everything to fit on a single page. Just. Barely.

Now the client wants you to add a trailing sub-summary to each group, but of course still expects everything to fit on a single page. The report is already at 8pt type, with tight vertical margins, and you are not looking forward to a bunch of time-consuming heroic measures to accommodate this request, so instead you employ the following trick. Continue reading “PDF Trickery” →

Level: Intermediate, Version: FM 12 or later

Successfully Find Duplicate Values Within A Set Of Records

Kevin Frank

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 “Successfully Find Duplicate Values Within A Set Of Records” →

Level: Intermediate, Version: FM 12 or later

Non-Breaking Reporting Groups

Kevin Frank

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 “Non-Breaking Reporting Groups” →

Level: Intermediate, Version: FM 12 or later

Extending FileMaker’s PDF capabilities with PDFtk Server

Kevin Frank

One of the great things about FileMaker is its ability to not only create a PDF from a single “print job”, but to take a series of separate jobs and concatenate them into a PDF via the “Save Records As PDF [append]” feature, like so:

7-13-2014 2-20-22 PM

(The above code comes from PDF Catalog with Table of Contents, which appeared on this site last year.) Basically, anything you can print from FileMaker, you can instead output as a PDF.

But what if you have existing multipage PDFs stored in container fields, and want to combine them into a larger PDF? This is something FileMaker cannot do natively. Recently I had a need to do just this, and happened to mention it to Jon Rosen at ErgoSoma, who suggested I take a look at a cross-platform utility called PDFtk Server.

7-15-2014 8-23-53 AM Continue reading “Extending FileMaker’s PDF capabilities with PDFtk Server” →

Level: Intermediate, Version: FM 12 or later

Process Lock that Clears Itself

Kevin Frank

If you build multi-user business systems, you may have had a need at one time or another to temporarily “lock” a process, so that only one user at a time can perform it. A typical drawback to most process locking approaches is that the lock does not automatically clear if the user who locked the process crashes or otherwise bails out of the system abnormally.

Well, I have some good news: Bob Gossom of Absolute Advantage has come up with a very clever, and well-documented, self-clearing process lock technique, which I’m pleased to share here with his permission.

6-24-2014 2-33-36 AM

Continue reading “Process Lock that Clears Itself” →