The problem.

SQL, including the new ExecuteSQL calc function in FileMaker 12, returns dates in this format: YYYY-MM-DD. This can be a bit of a pain if you have a string of text like this and just want to transform the date part:

1170 - 2012-03-09 - - Keep: Beta Presentation @ BRN Co

1171 - 2012-03-15 - 08:30:00 - Call Arthur Murray to finalize data

1172 - 2012-03-16 - 12:30:00 - Conference call with Melinda

1173 - 2012-03-20 - 11:15:00 - @ Breakfast

The calc for the results above looks like this: ExecuteSQL ( “SELECT id, DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )

The solution.

Jason Young discovered that you can cast the dates as text in your results simply by concatenating them with a bit of text, so instead of using just the field DateStart in your query, you can use ” || DateStart to get this:

1170 - 3/9/2012 - - Keep: Beta Presentation @ BRN Co

1171 - 3/15/2012 - 08:30:00 - Call Arthur Murray to finalize data

1172 - 3/16/2012 - 12:30:00 - Conference call with Melinda

1173 - 3/20/2012 - 11:15:00 - @ Breakfast

The calc for those results looks like this: ExecuteSQL ( “SELECT id, ” || DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )

Yes, FileMaker supports concatenate in it’s select statements.

Hope that helps.

Simplify and Stay on Screen

FileMaker 12 lets us run SQL queries against our own FileMaker tables. This has been possible with plugins for a while and a number of serious devs like Mike Lee, Greg Lane, and Kevin Frank have been talking about what a great technique this is. I completely agree and think the new ExecuteSQL calc function is the killer feature of FileMaker 12.

Before SQL

There are a few reasons for this and I hope to get to write about more of them. But for now, consider the following simple script that checks to see if any records match a given ID:

Before SQL (click for a larger version)

There is nothing really wrong with this script and in fact it comes from our FileMaker 10 calendar. But look at all the work we have to do to make sure we’re in the right context to perform the find. We have to:

• Record our current context and find criteria
• Draw a new off-screen window
• Navigate to the new layout / context
• Execute our find, recording the outcome
• Close the off-screen window and return to our original context with the results

Kind of complicated. And frustrating to debug as the action is happening off-screen.

After SQL

Now take a look at the same script in FileMaker 12:

SQL Version (click for a larger image)

Because the new ExecuteSQL calc function lets us search for records independent of context, we don’t need to manage layouts and windows. We can just ask FileMaker if the records exist. Fewer moving parts means simpler scripts, which means more stable solutions: no question about it.

(For what it’s worth, we could have written that as just one line, putting the ExecuteSQL calc in the ExitScript result, but I find these things are easier to debug if you can look in the script debugger for $sc_Result before the script exits. That way, I can alter it right here if I need to.)

Learning SQL

FileMaker’s new ExecuteSQL function only accepts versions of the SELECT statement so there isn’t that much SQL to learn But it does support joins, union, and groupby, so if you already know your way around this stuff you’ll be rewarded. If you don’t, SeedCode will soon be releasing a free tool to help. (You’ll love it.) If you’re not getting our newsletter, sign up here to be notified as soon as we ship.

And if you’re wondering what you can possibly do with just the SELECT statement (which returns lists of matching data), think Virtual List and stay tuned.

We’re thrilled to announce our new Free Calendar Template for FileMaker 12. We’ve rewritten the free calendar to take advantage of some of the new things in FileMaker 12, including the new look and feel, but perhaps most importantly, the new ExecuteSQL function.

This makes the new calendar simpler, less dependent on context, and easier to integrate.

Checkout this short introduction demonstrating how this new SQL stuff makes things simpler:

Now download a copy of the new calendar and dive in or learn more about our calendars.

(And if you’re looking for some more detail on how we made that cool little “x” toggle in the interface, we describe that here. It’s a great little switch that is super portable. That video will also give you a sneak peak into our new Pro calendar for FileMaker 12. Coming soon!)