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 2

Kevin Frank

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

[Note: To avoid a huge amount of redundant repetition, this article assumes that you are familiar with part 1 and part 1.1 in this series.]

Today we’re going to take a look at streamlining the approach introduced in part 1 by reducing clutter on the Relationships Graph, going from this…

…to this, with no loss in functionality.

…and you can follow along in today’s demo file, excel exports, part 2, if you are so inclined. Continue reading “User-Friendly Excel Exports, part 2” →

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 1.1

Kevin Frank

Bruce Robertson emailed me off list with an improved version of my demo from part 1, excel exports, part 1.1, which I am sharing with his permission. The improvements are…

a) live preview fields showing the data to be exported.

b) an “Eval Error Tester” script to make sure the output code in the alias table is well-formed.

Thank you Bruce for making this available.

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