1. Pages:
  3. 1
  4. ...
  5. 21
  6. 22
  7. 23
  8. 24
  9. 25
  10. 26
  11. 27
  12. 28
  13. 29
  14. 30
  15. 31
Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 1

Kevin Frank

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

Many end users appreciate FileMaker’s ability to export a found set of records in Excel format — it’s fast, straight-forward and empowering. One request that I have received from clients multiple times over the years is: make the exported field names in the top row of the spreadsheet “human-friendly”…

For example, instead of this:

…perhaps they would rather see this:

Another request has been to allow data from multiple source fields to be combined into single export columns, as per columns A and C here:

And a third request has been to provide an interface so that users can pick and choose fields to export, including (where appropriate) related fields, without having to navigate the complexity of the native FileMaker export dialog. Continue reading “User-Friendly Excel Exports, part 1” →

Level: Intermediate

Google Route Mapping, part 2

Kevin Frank

Those of you who attended DevCon 2011 in San Diego may recall meeting Andries Heylen of BH&A, who cheerfully answered questions in the FileMaker Visionary Bar. Well, from the sow’s ear demo I posted in part 1, Andries has woven a silk purse (google-route-mapping-part-2), which I am sharing with his permission, and which uses Google “waypoints” to produce an optimized travel route.

Continue reading “Google Route Mapping, part 2” →

Level: Beginner, Version: FM 8 or later

Google Route Mapping, part 1

Kevin Frank

The other day someone asked a question on the FMP Experts list about plotting a driving route in Google Maps using FileMaker data.

Mark Rubenstein posted a simple solution, and my reactions were, in this order: “No way, it can’t possibly be this easy; I should build a demo (google-route-mapping) to find out; wow, it really works… hey, I wonder if he’d be okay with me posting this on FileMaker Hacks?” Continue reading “Google Route Mapping, part 1” →

Level: Intermediate

GetAsDate Head-Scratcher

Kevin Frank

Recently I was doing some FileMaker training with my older son; we were looking at the “DayOfWeek” function in the data viewer, and I showed him that…

DayOfWeek ( Date ( 1 ; 22 ; 2012 ) )

…returns a 1, since this particular date falls on a Sunday, and FileMaker considers Sunday to be the first day of the week. Then I said, “Of course I could instead express it like this…”

DayOfWeek ( GetAsDate ( "1/22/2012" ) )

…and was nonplussed when a ? was the result. Continue reading “GetAsDate Head-Scratcher” →

Level: Intermediate, Version: FM 8 or later

Locating Matching Records, part 2

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 (except for the section on case-sensitive searching).

Have you ever right-clicked into a field (or Ctrl-clicked if on a Mac) and chosen Find Matching Records? It works remarkably well, but oddly enough, there is no scripted equivalent for this command.

Of course if we do a Find Matching Records and then invoke Modify Last Find…

…we can see what FileMaker is doing behind the scenes. Continue reading “Locating Matching Records, part 2” →

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” →