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

FM 13: Virtual List Charts, part 2

Picking up where we left off in part 1, today we’re going to take a look at examples 2 through 6 in the Virtual List Charts demo file (the demo has been updated since part 1, so I recommend downloading a fresh copy).

5-16-2014 4-08-08 PM

We covered example 1 and most of the general concepts last time, so today we’re mainly going to touch on specific points of interest, but to briefly recap… Continue reading “FM 13: Virtual List Charts, part 2”

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

FM 13: Virtual List Charts, part 1

4-20-2014 11-18-24 AMToday we’re going to look at applying the virtual list technique to FileMaker charting with the goal of producing a reusable chart “object”, or rather, a series of chart objects. We’ll need more than one because while certain attributes (e.g., chart title) can be set programmatically, others, including type (e.g., column or line), must be hard-coded into the chart object.

We’ve already explored Bruce Robertson’s virtual list on this site a number of times, but briefly, you create a utility table in your solution to facilitate non-standard viewing, reporting, etc., and pre-populate it with “more records than you’ll ever need”. The records in this table will derive their data “virtually”, by parsing it from an array — typically one or more $$variables.

Well it turns out the technique can be applied to charting as well, and today we have a demo file, Virtual List Charts, that contains six examples: three for Web Visits…

4-20-2014 2-03-32 PM

Continue reading “FM 13: Virtual List Charts, part 1”

Level: Intermediate, Summary List, Version: FM 13 or later

FM 13: Summary List + Virtual List

Recently a question came up on the FMP Experts list re: the possibility of displaying a found set of customers in 3 columns in browse mode in FileMaker 13. While FileMaker doesn’t naturally display data this way, there are various ways to trick it into doing so, and today we’re going to look at a method that combines the new-in-13 Summary List field type with the Virtual List technique.

Demo file: FM 13 Summary List + Virtual List

2-1-2014 10-03-20 PM

Continue reading “FM 13: Summary List + Virtual List”

Level: Intermediate, Version: FM 10 or later

Conditional Subsummary Report in Browse Mode

Last time we looked at several summary reporting tricks, including a conditional subsummary (when an item’s Status is “Scheduled” it will have a value in the Substatus field — otherwise Substatus will be blank). The challenge was to generate a summary report showing Substatus only where appropriate, without seeing any annoying empty gray rows beneath Pending, Cancelled or Completed. And last week’s report worked fine… in preview mode.

9-11-2013 10-43-07 PM

Continue reading “Conditional Subsummary Report in Browse Mode”

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

Outer Joins in FileMaker 12, part 3

I’ve said before, and no doubt will say again, that one of my favorite things about this blog is how much I learn from your feedback and the demo files you send me.

Recently I received a file from Otmar Kramnis of the Hochschule Luzern demonstrating the fastest SQL-based method I have yet seen to solve the challenge we looked at in part 1 and in part 2, and with a few minor modifications, this is the demo we’re going to focus on today:  Outer Join Demo 7

As you may recall, the aim is to show a week’s worth of daily sales totals for all employees whether they had any sales or not.

4-17-2013 8-39-48 AM

Or, to restate the problem in more generic terms: we need to show all values from table A, whether or not there are any matching values in table B. This is known as an “outer join”, or more precisely a “left outer join”, since we want to see all values in the “left” table (Employees), whether or not they have corresponding matches in the “right” table (Sales).

Continue reading “Outer Joins in FileMaker 12, part 3”

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

Outer Joins in FileMaker 12, part 2

Last week in part 1 we looked at four “outer join” reporting approaches. Two of them involved ExecuteSQL, and I ended that section with the plea: Of course it’s possible that you, dear reader, know some FileMaker SQL voodoo to speed things up, and would be willing to share? Well Dr. Osamu Noda of Japan was kind enough to not only respond, but has provided a pair of demos (Outer Join Demo 5 and Outer Join Demo 6) which are significantly faster and which I am sharing with his permission.

Both of the demos are based on my original files from last week, and as you may recall, the aim was to show a week’s worth of sales for all employees whether they had any sales or not.

Continue reading “Outer Joins in FileMaker 12, part 2”

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

Outer Joins in FileMaker 12, part 1

Recently I had an on-screen reporting challenge, and decided to try several different approaches to see which would be fastest. The challenge: Starting with two tables, one containing 20 Employees, and one containing 2,000 Sales records for the current year…

…display daily sales totals per employee in a seven-day grid, like so:

Continue reading “Outer Joins in FileMaker 12, part 1”

Level: Advanced, Version: FM 9 or later

User-Friendly Excel Exports, part 5

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

Well, we’ve reached what may well be the final posting in this extended series of articles. We’ve covered a lot of ground, and this article assumes familiarity with what has come before (in parts 1, 1.1, 2, 3 and 4).

Today we’re going to look at a couple “Virtual List” implementations of the User-Friendly Excel Export technique, which will, among other things, allow us to eliminate these calculated fields from the Contacts table…

…as well as the corresponding set of fields in Donations.

Bruce Robertson’s Virtual List technique has made a couple previous appearances on FileMaker Hacks…

…so I won’t rehash its merits in detail here, but if you aren’t familiar with it, I recommend you check out one or both of these earlier articles. Continue reading “User-Friendly Excel Exports, part 5”

Level: Intermediate, Version: FM 11 or later

Long Documents in FileMaker 11

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, Version: FM 10 or later

Exporting Data to iCal, Outlook, Google Calendar, etc.

Demo file: 2010-12-13-export to ical

The other day I needed to export some appointments from FileMaker to iCal. I’d never done this before, but I did a bit of reading on Wikipedia and elsewhere, and it turns out to be fairly straight forward. I don’t claim that what follows is in any way authoritative, just that it works… and not just with iCal, but with Outlook, Google Calendar and any other program that recognizes the iCalendar format.

Let’s start with a basic table of appointments, like this.

We’re going to create an “ics” file, which is a text file with three distinct elements. At the beginning of the file is the header, which looks like this:

PRODID:-//FileMaker Pro//NONSGML yourSolutionNameHere//EN

Next we have one or more body entries that look like this:

SUMMARY:Marketing Meeting

And at the very end of the file comes the footer, which looks like this:


Let’s take a closer look at the body section. The BEGIN, SUMMARY and END entries seem self-explanatory, but what’s going on with those “DT” entries? Well, as you’ve probably guessed, they’re timestamps. First we have the date formatted as YYYYMMDD, followed by a T, followed by the time in 24hour format, hhmmss, followed by a Z.

One important observation about the time portion: it is represented as UTC time, not local time. So the first row in our appointments table, with a starting time of 10:00 Am, will be converted to the UTC equivalent of 180000 when we export it, because the database is located in the Pacific time zone, which is 8 hours offset, i.e., UTC-08. During Daylight Savings Time, the offset value would be -7, but we’re on Standard Time now, so the correct value is -8.

Clearly DTSTART is the beginning time for the appointment, and DTEND is the ending time, but what’s DTSTAMP? That is the “creation time” of the appointment, and apparently is required. Since I don’t care about that, I’m just making it the same as DTSTART.

And finally we come to the UID entry, which is simply a unique serial number. OK, I lied. Ideally it’s some form of “UUID” which means that heroic measures have been taken to attempt to guarantee its absolute uniqueness. But as long as it’s unique within your calendar, you’ll be fine.

The UID is very important, because if you import the same appointments more than once, the calendar will be smart enough to update existing entries where there is a matching UID, rather than allowing them to appear twice. That’s right… if an appointment changes in your database, you can export it, and reimport it into your calendar without having to worry about duplicates.

So, let’s add a UID field (auto-enter, text) and a UTC offset field (global, number).

Now that we understand the design of a minimal “ics” file, we can define a calculation field, ical_basis, in our database to assemble and format the data accordingly. Reminder: the first thing we do when we define a calculation field is, of course, make sure the result type is correct: in this case we want the result type to be text. Also, let’s set the storage type to “unstored”.

This calculation is the heart of the whole operation. Its main purpose is to generate one body segment per appointment record. Additionally, it will create the header at the beginning of the first record, and the footer at the end of the last record with the help of the “rn” and “fc” variables.

Note how the conversion to UTC is handled:

  1. transform a date and time into a timestamp
  2. add 8 hours (8 * 3600 seconds)
  3. break it into individual components (year, month, etc.)
  4. pad the components with leading zeros if necessary
  5. reassemble them as YYYYMMDDThhmmssZ

Let’s reduce our found set to three records, go into preview mode and see how that calc evaluates. Looks like we’re home free doesn’t it?

We simply export our found set of appointments to a text file with an “ics” extension and declare victory, right? Well, not quite. If we try that, here’s what we get:

…a text file with three lines, each a mile long. While inconvenient, this makes sense if you think about it. When you export to a text file, FileMaker uses ASCII 13 as the record delimiter, i.e, puts a hard return between the data from each record. To avoid confusion, any existing hard returns from within the source data are converted to a “vertical tab” character (ASCII 11).

But we’re not going to let a puny ASCII character defeat us, are we? (Yes, I know, these are Unicode, not ASCII, codes… but the values are the same either way, and it’s easier to refer to ASCII codes than Unicode “code points”.)

Broadly speaking, there are two different approaches we can take to solve this problem:

A. Generate the text file, and then post process the file using some sort of external technology.

B. Parse each row of ical_basis into a separate record, and then export those records.

Let’s look at option A first. There are various ways the post-processing can be accomplished, including AppleScript on the Mac, and VB Script or PowerShell on the PC, but it can also be done via a 3rd-party FileMaker plug-in, Troi File. (There may be other plug-ins that can accomplish this as well, but Troi File is the one I’m familiar with, having used it since 1998.)

What I like about the TroiFile method is, a) it works the same regardless of whether you’re on a Mac or PC, and b) how incredibly easy it is. One little command…


…and no more pesky problem.

Method B involves creating a special table to facilitate the generation of the ics file. As with method A, there are any number of ways to accomplish this, but the one I want to look at here is Bruce Robertson’s Virtual List technique. It’s a highly versatile tool to add to your box of tricks, and it also happens to score a perfect 10 on the coolness scale.

Here’s a basic explanation of how Virtual List can be implemented to solve this particular problem, and don’t worry if it doesn’t make immediate sense. You don’t have to fully understand it to reap the benefits, and the act of implementing it in one of your solutions will help you understand it better.

  1. Create a new table in your solution. Name the table VL_Utility, and don’t create any records yet.
  2. Define a number field, serial_number, as an auto-enter serial number with an initial value of 1.
  3. Define a calculated text field, virtual_list, as
    GetValue ($$virtual_list;serial_number)
    …and set the storage type to unstored.
  4. Create “more records than you’ll ever need” in this table. If that sounds unhelpfully vague, why not start with 10,000? You can always add more later.
  5. Back in your Appointment table, locate the found set you wish to export.
  6. Using a looping script, walk the records and populate the $$virtual_list variable.
  7. Define a variable, $vc, as ValueCount($$virtual_list) .
  8. Locate records in VL_Utility where serial_number <= $vc, and make sure these records are unsorted.
  9. Export these records to a text file with an “ics” extension, e.g., appointments.ics

The result looks like this and is ready to import into your calendar program.

For Google Calendar, you can import entries by clicking the “Add” button at the left of the calendar. On the Mac, you can import an “ics” file to iCal by double clicking it, and for Outlook choose Import and Export from the File menu.

Here’s what we see after importing.

Really we’ve just scratched the surface of what can be accomplished using the iCalendar format. There are many optional properties that can be included, for example “description” (a.k.a. note). If we want to include notes in our export, we can modify our ical_basis calculation as follows:

And here’s how an appointment with a note looks in iCal: