Version: FM 11 or later – FileMakerHacks

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

November 26, 2011February 8, 2014 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

November 10, 2011February 8, 2014 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

October 18, 2011August 24, 2022 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” →

Chart, Level: Beginner, Version: FM 11 or later

A Charting Tip from the Old Advance Man

July 17, 2011February 28, 2014 Kevin Frank

Here’s a tip I picked up from Steven Blackwell the other night on Friday Night FileMaker Chat. FileMaker 11 doesn’t provide an obvious way to prevent a user from interacting with a chart in browse mode (e.g., copying, clicking on, dragging, or mousing over to view tooltips).

Continue reading “A Charting Tip from the Old Advance Man” →

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

Array Charting, part 2

July 14, 2011August 24, 2022 Kevin Frank

Last week we looked at building a FileMaker 11 native chart based on data that has been parsed into a field-based array.

2011-07-11-b

I happen to like field-based arrays, for reasons I’ll get to in a minute, but is a field-based array really necessary to generate the above chart? Continue reading “Array Charting, part 2” →

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

Array Charting, part 1

July 3, 2011August 24, 2022 Kevin Frank

In recent postings we’ve looked at…

Using a lookup to populate or clear an array (Lookups and Repeaters)
Determining the position of an item in a list (ValuePosition)
Parsing subsummary values from a found set (Fast Summaries)

While these three techniques may not appear to have much in common, all of them are used as building blocks in today’s demo, line-chart-from-field-array. Our data set is a table of web site visits, by week and by state, beginning in March 2006 and running through June 2011.

Wouldn’t it be nice to to use FileMaker 11’s built-in charting capabilities to produce a line chart showing weekly visits per year? Continue reading “Array Charting, part 1” →

Level: Intermediate, Version: FM 11 or later

Popup Pickers

May 9, 2011August 24, 2022 Kevin Frank

Back in 2009, David Graham released a demo called Spotlight Filter that caused a minor revolution in the FM development world: a) it neatly solved a problem that many developers routinely face, and b) it was an early and convincing implementation of FileMaker script triggers.

The problem it solved was, “How can I provide my users with a lightning-quick, keyboard-driven popup-window to pick from a set of values, for example, to add a customer or a product to an invoice?” Continue reading “Popup Pickers” →

Level: Intermediate, Version: FM 11 or later

Filtered Relations, part 2

February 6, 2011August 24, 2022 Kevin Frank

Today we’re going to take another look at the challenge we encountered in part 1 of this series. The challenge is: given a simple database consisting of Products, Invoices and Invoice Line Items, how can we show total sales per product filtered by date range?

One thing is certain: assuming a normalized data model (with the date in the Invoices table), some sort of trick will be required. In part 1, we leveraged the FileMaker relational model to solve this problem, by adding additional table occurrences to the Relationships Graph, as well as some calculated fields in the products table. Continue reading “Filtered Relations, part 2” →