Author Archives: Kevin Frank

Virtual List Reporting, part 1

Self-plagiarism alert: to avoid repeatedly referring the reader back to earlier virtual list articles, portions of text from those earlier articles are incorporated here.

Welcome to the first installment of a multi-part series on producing reports using the virtual list technique, or more properly, collection of techniques. Demo file: VLR-part-1.zip

demo

Invented and popularized by Bruce Robertson, virtual lists are incredibly flexible, and have made a number of appearances here in the past, including… Continue reading

Set Variable by Name

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

Fast Summaries Revisited

If you do complex data analysis, then from time to time you probably need to group, summarize, and parse data into variables and/or fields. There are various ways to accomplish this, including the Fast Summary technique, which regular readers of this blog may be familiar with, as it has made a number of appearances here over the years.

Well today we’re going to look at a couple alternatives to Fast Summaries, with the help of some demo files, which you can use to do your own benchmarking.

2016-03-29_223920

Or, if you’d rather not do your own benchmarking, scroll down to see results from my tests. Bottom line: each of these three techniques can be fastest, depending on circumstances.

(Also, if you’ve been putting off exploring Perform Script On Server, a.k.a. PSOS, host the demo files on FileMaker Server 13 or later. The reporting routine optionally uses PSOS, so you can dive in painlessly and see what you’ve been missing.) Continue reading

PDF Trickery

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 subsummary 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

Obscure Multiple Window Issue

Note: Thank you to Colleen Hammersley for bringing this issue to my attention, and to Howard Schlossberg for proposing a clever and elegant work around that addresses the issue on the Windows platform. (We’ll also look at another fix that works cross-platform.)

Today we’re going to explore a windowing behavior change that was introduced in FileMaker 12. If your solutions display multiple on-screen windows, then you may at some point find yourself facing this issue.

2016-01-31_16-47-32

The problem manifests in different ways on the Windows vs. Mac platforms; we’re going to start out by looking at what happens in FMP/Win via the Window Focus, v1 demo.

Continue reading

Tips ’n’ Tricks, part 2

Welcome back for a second dip into the developer bag of tips and tricks. A few of today’s entries assume familiarity with part 1, so if you haven’t already checked it out, I invite you to do so before proceeding.

We begin today with a couple ExecuteSQL tips.

Tip #1: Get to know COALESCE

The SQL Coalesce function takes a series of arguments and returns the first non-null result, like so:

COALESCE ( arg1 ; arg2 [; arg3 ; etc.] )

For example, given a table of customer data including these records…

2015-12-23_9-44-36 Continue reading

Tips ’n’ Tricks, part 1

Over the last few years I’ve been collecting miscellaneous tips and tricks with the thought of eventually aggregating them into a blog posting, and it appears that day has finally come. Ideally there will be something new and useful here for developers of all levels, from the neophyte to the most grizzled of FileMaker veterans.

We start off with a couple FM 14-specific tips.

Tip #1: Tool tips in layout mode

In layout mode in FileMaker 14 you can hover your mouse over any badge and an informative tool tip will display.

2015-11-30_13-32-06

Tip #2: Shift-Return in the Script Workspace

Did you know you can add new line above the current line in the Script Workspace by pressing Shift-Return? It works with the standard Return key, as well as the numeric Enter key.

2015-11-30_14-08-17

Before… line 1 is active, and the user is about to press Shift-Return

After... a new top line has been added

After… a new top line has been added, and existing entries have moved down to accommodate

Continue reading

Multi-State Dynamic Graphic Buttons

I had a client request recently to toggle an image button to a second image on mouse over. Under normal circumstances, this would not be difficult to accomplish since you can assign one image to the Normal state, and a different image to the Hover state, like so.

2015-10-16_9-32-26

However, if you go this route, you can’t use container fields as the source for the images, but instead must make static file selections, like so:

2015-10-25_15-08-03

Since this button would be appearing on dozens of layouts throughout a 20+ file solution, the client wanted to be able to store the two images in container fields, so an authorized user could easily swap in new container images (in browse mode, not layout mode) and have the changes ripple through their entire solution.

2015-10-25_15-23-05

Demo Files:

Continue reading

Found Set Awareness Revisited

Recently a colleague contacted me with some questions about making ExecuteSQL found-set aware, and I realized that while I had addressed aspects of the problem in various blog postings over a multiyear period (for example, here and here), I had not assembled my thoughts into one cohesive, up-to-date article.

Well, that shortcoming has now been addressed, and today we’re going to look at three demo files, all of which rely on the summary list field type (introduced in FM 13).

9-20-2015 8-52-01 AM Continue reading

FM 14: Separation Model Data Mining

Note: Interface file #1 requires FM 14 or later; interface file #2 works with FM 12 or later.

What do you get when you combine the Separation Model + FM 14 placeholder text + ExecuteSQL + a million-record table + a variable array + Get(CalculationRepetitionNumber) + the Mod and Ceiling functions + a couple custom functions, with blazingly fast (local, LAN and WAN) performance thrown in for good measure?

This article w/ accompanying demo (fm-14-separated-data-mining.zip), that’s what.

7-7-2015 12-33-33 PM

The Challenge

Provide a data-mining interface to query a million-record table (cc_transactions) containing 20 years’ worth of credit card transactions. The client wants to be able to pick a date via a calendar widget, and see transaction info for that date, summarized by card type, transaction type and region.

7-6-2015 8-54-58 AM

This is a separated solution, with cc_transactions living in a file called z_data.fmp12, and a specific requirement for this project is to not make any schema changes to the data file. The data-mining will take place in a separate interface file… or in this case, two interface files, since we’re going to look at two methods.  Continue reading