The Original FileMaker Community
Business Templates - Demo Apps - Video Tutorials -Samples - Help - 46000 Member Forum

The Leading Filemaker Developer Tools

Outer Joins in FileMaker 12, part 2 – FileMaker Today

Get real time updates directly on you device, subscribe now.

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.

You may also recall that Week View is a special viewer layout using Bruce Robertson’s Virtual List technique, with eight columns (calculated fields) parsing a 2-dimensional array which is produced by a single ExecuteSQL statement, and looks like this:

(This was covered in extensive detail in part 1, so I’m glossing over it now.)

The only significant difference in this week’s files is how that array is produced. The SQL statement in demo 2 was written thus:

…and I’m embarrassed to say, contains flawed logic which may account for its terrible performance.

But Dr. Noda’s approach uses SQL “Case” statements and produces results a) about eight times faster (!!!), and b) which happen to be correct.

Interestingly page 36 of the FileMaker 12 ODBC/JDBC Guide says that LEFT OUTER JOIN will not work, but Dr. Noda’s code appears to indicate otherwise.

He also pointed out that if I was willing to give up the requirement that employees with no sales during the week be shown, that a WHERE clause would further boost performance. You can see this in his second demo.

The bottom line: ExecuteSQL is not as fast as the native FileMaker relational approach used in the Outer Join Demo 3 from last week, but a properly formed left outer join query is sizzlingly fast compared to my previous malformed attempt. Thank you very much Dr. Noda for the impressive demos.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More