FileMaker Hacks

The Missing FM 12 ExecuteSQL Reference

There seem to be many questions about the usage of SQL (Structured Query Language) with the ExecuteSQL function in FileMaker 12. This tutorial attempts to explain some of the SQL terms, if you are new to writing SQL statements. Since there are already many examples of how to write the ExecuteSQL queries, links to these [...] Source

0 Comments
1,331 views

Kevin Frank
October 19, 201213 yr

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 [...] Source

0 Comments
1,743 views

Kevin Frank
October 12, 201213 yr

Outer Joins in FileMaker 12

Recently I had an on-screen reporting challenge, and decided to try several different approaches to see which would be fastest. Starting with two tables: one containing 20 Employees, and the other containing 2,000 Sales Data records for the current year… …display daily sales totals per employee in a seven-day grid, like so: Of course, as [...] Source

0 Comments
1,196 views

Kevin Frank
October 3, 201213 yr

An In-Depth Look at “Export Field Contents”

Have you ever wanted to export a single field and maintain all the characters in that field? This article explores the possibility with XML Export and the use of a simple XSLT. But first we’ll explain the good, the bad and the ugly of some different standard ways to export TEXT out of FileMaker. Using [...] Source

0 Comments
1,869 views

Kevin Frank
September 23, 201213 yr

Aggregates in Filtered Portals

The question on one or more of the FileMaker forums was asked and answered. Q: How do you get the Count() of the related records in a filtered portal? A: (paraphrased) duplicate the filtered portal and make it one row in height. Place the related summary field, “Count of…”, in the single row filtered portal. [...] Source

0 Comments
1,512 views

Kevin Frank
September 13, 201213 yr

Thinking About Value Lists, part 1

In July and August we explored several esoteric value list techniques. This time around, and over the next few postings, we’re going to step back from the cutting edge, identify some common value list challenges, and propose some solutions to those challenges. A few thoughts before we begin: Some of the material in this series [...] Source

0 Comments
1,258 views

Kevin Frank
September 10, 201213 yr

FileMaker Blogs

Recently a question came up on one of the lists asking about intermediate-to-advanced FileMaker Blogs. Here are some I’m aware of, and I’m sure I’ve missed some good ones, so I apologize in advance for any inadvertent omissions. Cimbura – http://cimbura.com/tech/blog DB Services – http://www.dbservices.com/articles FileMaker ’n’ the Web: Lessons Learned – http://blog.jsfmp.com FileMaker Weetbicks [...] Source

0 Comments
1,557 views

Kevin Frank
September 1, 201213 yr

Fixed Width for EDI and Other Reporting

Editor’s note: Today it’s my pleasure to present a guest article written by Beverly Voth. Like many other developers, I have enjoyed and benefitted from her ongoing contributions to the FileMaker community. I do a lot of text manipulation for EDI (Electronic data interchange – http://en.wikipedia.org/wiki/Electronic_data_interchange) and plain text exports with fixed-width field data. Some [...] Source

0 Comments
1,229 views

Kevin Frank
August 30, 201213 yr

More Fun with Value Lists, part 2

As promised last time, today we’re going to look at a technique from Dr. Osamu Noda, which uses FM 12′s ExecuteSQL to create what appears to be a value list when in fact there are no value lists defined at all. This technique, like some of the others we’ve seen recently, is decidedly esoteric, but [...] Source

0 Comments
1,441 views

Kevin Frank
August 19, 201213 yr

More Fun with Value Lists, part 1

One of the things I love about FileMaker is how many different ways there are to skin the proverbial cat. Following our recent look at “Magic Value Lists“, I received thought-provoking FileMaker 12 demos from John Ahn and Dr. Osamu Noda. I’m going to save Dr. Noda’s files for next time, but today we have [...] Source

0 Comments
1,533 views

Kevin Frank
August 13, 201213 yr

Magic Value Lists

Folks, we have a superb demo today (MagicValueList), which comes courtesy of Andries Heylen of BH&A… But first a bit of background. Prior to July 18, 2012, if anyone had told me you could base a value list on an unstored field, my response would have something along the lines of… What app are you [...] Source

0 Comments
2,503 views

Kevin Frank
July 25, 201213 yr

FM 12 ExecuteSQL “Unconference” Session

Yesterday I had the pleasure of co-presenting a DevCon “Unconference” session with John Ahn on the topic of ExecuteSQL. I wasn’t sure if there would be much interest in this somewhat geeky subject, but the room was packed, and the audience ran the gamut from SQL newbies to SQL power users. We started out with a [...] Source

0 Comments
2,250 views

Kevin Frank
July 19, 201213 yr

Wim Decorte’s FM 12 Trigger Reference

The other day I had a question about some nuance involving script triggers. The details of that particular question aren’t important, but I recalled that when script triggers were first introduced, Wim Decorte had produced a detailed, color-coded reference. I wondered if he had updated it for FM 12, and it turns out that he [...] Source

0 Comments
2,248 views

Kevin Frank
July 15, 201213 yr

A Simple Backup Script, revisited

In October 2011 I posted a simple backup script that has made my development life easier, but of course that version was for .fp7 files only (the release of FM 12 being six months in the future), and when I began converting files to FileMaker 12, I realized that the backup script was not intelligently [...] Source

0 Comments
1,435 views

Kevin Frank
June 25, 201213 yr

Dwindling Value Lists, part 2

One thing I love about this blog is how much I learn from your comments. Having said at the end of part 1 that I couldn’t think of any serious use for a DVL on a check box set, Michael Rocharde kindly provided this example file (Conditionals), which I am sharing with his permission. This [...] Source

0 Comments
1,546 views

Kevin Frank
June 11, 201213 yr

Dwindling Value Lists

A few months ago I mentioned “dwindling value lists” in passing, and said I would do a proper article on them at some point in the future. Well, then FM 12 was released and I went on an ExecuteSQL binge, so I’m just now getting around to honoring my promise. Dwindling Value Lists (DVLs) are [...] Source

0 Comments
1,268 views

Kevin Frank
June 10, 201213 yr

FM 12 ExecuteSQL: Robust Coding, part 2

In part 1, I listed six scenarios that could potentially cause ExecuteSQL code to break: …and managed to get through the first five before running out of steam. This time, we’re going to look at #6 on the list, as well as some other observations, and I invite you to download and follow along in [...] Source

0 Comments
1,334 views

Kevin Frank
May 21, 201213 yr

FM 12 ExecuteSQL: Robust Coding, part 1

[Note: Some of the material in this article, and in today's demo file ExecuteSQL Sandbox, previously appeared in my March 2011 article, Custom Functions for Internal SQL.] The other day a colleague remarked, “You know, it’s going to be interesting to see if you SQL guys are still so excited about ExecuteSQL when something gets [...] Source

0 Comments
1,508 views

Kevin Frank
May 13, 201213 yr

FM 12 ExecuteSQL: Dynamic Parameters, part 2

This is a quick follow-up to part 1, with a couple more observations about dynamic parameters. a) I can’t believe I forgot to mention this the other day: A major ExecuteSQL headache that dynamic parameters can alleviate is the dreaded “embedded apostrophe” problem. In case you aren’t familiar with it, if your text string has an [...] Source

0 Comments
1,257 views

Kevin Frank
May 8, 201213 yr

FM 12 ExecuteSQL: Dynamic Parameters

I have a confession: when I first read about dynamic parameters in the Help entry for ExecuteSQL, my initial reaction was, “Why do they have to make it so darn complicated?” I have another confession: I am now singing a very different tune indeed. I’ll get to the reason for this in a minute, but [...] Source

0 Comments
1,327 views

Kevin Frank
May 2, 201213 yr

FM 12 ExecuteSQL: A Cool Tool

It’s been a fun week experimenting with the ExecuteSQL function, and also seeing what others are doing with it. In some cases the experience has been eye opening, and I strongly recommend that anyone interested in ExecuteSQL grab the SQL Builder file from Eden Morris, available at http://fmforums.com/forum/files/file/25-sql-builder/ This attractive and powerful tool provides a [...] Source

0 Comments
1,724 views

Kevin Frank
April 28, 201213 yr

FM 12 ExecuteSQL, part 2

One of the fun things about a new FileMaker release is figuring out how new features work, including subtle behavioral nuances. Today’s demo file, FM12 ExecuteSQL Help Example from CW, is based on Corn Walker’s re-working of one of the demos from part 1, and helps demonstrate how ExecuteSQL fits in with the FileMaker security [...] Source

0 Comments
1,386 views

Kevin Frank
April 22, 201213 yr

FM 12 ExecuteSQL, part 1

FileMaker 12 offers many intriguing new features, including new windowing capabilities, significant charting and container field improvements, and a brand new design surface. But to my way of thinking, all of these pale in comparison to the exciting possibilities offered by the new ExecuteSQL function. What’s so great about ExecuteSQL? In a nutshell, it allows [...] Source

0 Comments
1,580 views

Kevin Frank
April 17, 201213 yr

User-Friendly Excel Exports, part 5

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 [...] Source

0 Comments
1,247 views

Kevin Frank
March 30, 201213 yr

User-Friendly Excel Exports, part 4

Colleagues and family members are starting to express concern at my obsessive refusal to bring this series to a close. But we’re getting close. Really. In fact, today, we’re going to look at two cool tricks (and one work around) which are utilized in this article’s demo file, excel exports, part 4, but that could [...] Source

0 Comments
1,399 views

Kevin Frank
March 20, 201214 yr

Entries

Comments

Views

Entries in this blog

Important Information

Configure browser push notifications

Chrome (Android)

Chrome (Desktop)

Safari (iOS 16.4+)

Safari (macOS)

Edge (Android)

Edge (Desktop)

Firefox (Android)

Firefox (Desktop)