Category: Version: FM 12 or later

Chart, ExecuteSQL, Level: Advanced, Version: FM 12 or later

Weekly Sales Comparison Charts

Kevin Frank

If you are responsible for helping business decision makers analyze data, you are probably familiar with questions like:

  • Are we on track to meet or exceed last year’s sales totals?
  • How is our sales team doing now, compared to this time a year or two ago?

Today’s demo file, weekly sales comparison charts, v3, can help answer these questions. It consists of an Employee table with 20 records, a Sales table with approximately 40,000 records, seven chart types, and an option to chart weekly amounts either individually or cumulatively.

6-23-2013 11-46-18 PM

When we look at the weeks individually, it’s clear that Zola Buchanan’s sales figures are mixed so far this year, compared to 2011 and 2012. But what may not be immediately apparent is whether overall she’s doing better, the same, or worse.

Continue reading “Weekly Sales Comparison Charts” →

Level: Advanced, Version: FM 12 or later

Radical Separation, part 4

Kevin Frank

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

FYI: In March at the Portland PauseOnError un-conference, Matt Navarre and I had a freewheeling Separation Model discussion, a podcast of which has just been posted as episode 85 at FileMaker Talk.

Welcome to the fourth and final installment in our series on Radical Separation. Today’s article assumes familiarity with part 1, part 2 & part 3, and continues in the direction we were headed at the end of part 3. Specifically, we will finish up our exploration of “virtual calculations” by examing an intriguing approach suggested by Barry Isakson to a) reduce the field count, b) solve the “define more fields than you’ll ever need” problem, and c) accommodate summary fields, and I invite you to follow along in today’s demo file, Virtual Calculations, Part 4, if you are so inclined.

6-9-2013 4-10-10 PM

Continue reading “Radical Separation, part 4” →

Level: Advanced, Version: FM 12 or later

Fast Grid Displays on a WAN

Kevin Frank

Last October I began a series on Outer Joins, which explored a number of different ways to display summarized information in a grid, and at the time I concluded that the “fastest” method was to leverage the FileMaker relational model. And it was plenty fast, locally… and not too bad on a LAN… and technically, it was the fastest method on a WAN but only because the other methods we looked at were even dog-slower than it was.

I like to test solutions on a WAN, even if they’re only going to be deployed on a LAN, because it’s a great way to uncover performance bottlenecks. But recently I needed to deploy a summarized grid on a WAN, and was incentivised to come up with something faster… and after a bit (well, okay, a lot) of trial and error settled on the approach we’re going to look at today. To cut to the chase, with a million records in the test file, the previous best grid rendering time of 11 seconds on a WAN has been reduced by a factor of 10, to just over one second.

5-28-2013 11-11-16 PM

Continue reading “Fast Grid Displays on a WAN” →

Level: Advanced, Version: FM 12 or later

Radical Separation, part 3

Kevin Frank

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

Today we’re going to delve further into the concept of virtual calculations, picking up where we left off last time, and with the assumption that readers are familiar with the material in part 1 and part 2 . We’ll look at some ways to make this technique less brittle (prone to breakage if objects are renamed), and also less opaque to DDR analysis tools such as BaseElements and Inspector. We’ll also see if the technique can be applied to auto-enter calc fields, and finally, we’ll explore some ways to make the technique easier to implement.

5-10-2013 10-41-51 AM

Demo Files:

Continue reading “Radical Separation, part 3” →

Level: Advanced, Version: FM 12 or later

Radical Separation, part 2

Kevin Frank

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

In part 1 of this series, we defined radical separation as a separation model scenario in which the developer no longer has access to a data file once a solution had been deployed. Updates to the solution are delivered in the standard separation model manner: by swapping in a new interface file.

We explored the concept of “virtual calculations”, where certain (unstored) calculated fields in a data file derive their definitions from syntax stored as data in a special table in the interface file. The advantage of this being that calculation logic can be redefined programatically by the simple expedient of replacing the interface file.

5-2-2013 9-09 PM

In the six weeks that have gone by since I posted part 1, I have made a couple improvements to the technique, one of which which we’ll examine in today’s demo file: Virtual Calcs, Part 2

Continue reading “Radical Separation, part 2” →

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

Outer Joins in FileMaker 12, part 3

Kevin Frank

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: Advanced, SQL, Version: FM 12 or later

Radical Separation, part 1

Kevin Frank

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

Earlier this month I had the honor and the privilege to do a presentation on the topic of Radical Separation at the PauseOnError un-conference in Portland, Oregon, which included a demo file resembling this one: virtual-calcs-part-1-v2

3-20-2013 2-54-43 PM

Before the conference I posted a pseudo-F.A.Q. which included the following…

  • Q. What’s your experience with the Separation Model?
    A. I’ve used it heavily over the last seven years, for a variety of vertical market applications, custom projects and, recently, on a vertical market FMGo app.
  • Continue reading “Radical Separation, part 1” →
Level: Intermediate, Version: FM 12 or later

Graph Management + Conditional Icons

Kevin Frank

Recently I received a dual-technique FileMaker 12 demo from Eden Morris. Here’s what he had to say about technique #1:

In the Relationships Graph I show the use of colored labels to indicate where record creation, cascade deletes, and sorted relationships are enabled. Looking at the graph it easily shows that I can create customers from a company table but that they don’t cascade delete, you can create invoices and invoice lines from a project and the invoices and their lines cascade delete, and that the invoice lines are sorted.

1-27-2013 11-50-00 PM

It’s an elegant way to display this info (but for some reason, it makes me crave jelly beans).

Continue reading “Graph Management + Conditional Icons” →

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

ExecuteSQL: Using IN with Faux Dynamic Variables

Kevin Frank

Editor’s note: Today we have a guest article written by John Weinshel, whose knowledge of SQL in general, and FileMaker+SQL in particular, runs deep. John’s contributions in various online forums are always worth reading, and it’s a privilege to present his thoughts on this topic here.

The new ExecuteSQL() function in Filemaker 12 does not work dynamically (with the question mark) as expected with the IN function (nor with BETWEEN). For example, we might expect the following statement to return all the ID’s for contacts whose first name is either John, Mary, or Renee:

ExecuteSQL (
"
SELECT c.PKContact
FROM Contacts c
WHERE c.fname IN ( ? )
";
""; ""; " 'John','Mary','Renee' "
)

…but it doesn’t. The following does work, but it’s not dynamic:

ExecuteSQL (
"
SELECT c.PKContact
FROM Contacts c
WHERE c.fname IN ( 'John','Mary','Renee' )
";
""; ""
)

Continue reading “ExecuteSQL: Using IN with Faux Dynamic Variables” →

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

PDF version of FM 12 ExecuteSQL Reference

Kevin Frank

Beverly Voth has produced a PDF version of The Missing SQL Reference with some additional material not available in her original posting from October 19th.

And the SQL4_fmdev2.fmp12 demo file has been updated with new queries.

Thank you Beverly for this major contribution to the FileMaker community.