Tag Archives: Outer Join

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