Category: ExecuteSQL

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” →
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.

Beverly Voth, ExecuteSQL, SQL

The Missing FM 12 ExecuteSQL Reference

beverlyvoth

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 will be listed at the end of this article. If you don’t need to learn the terms, jump right to the Helpful Example Databases section, below. There you will find links to solutions that help you create and test your queries.

This is not a complete SQL guide, as other databases may use other syntax. This is not a complete FileMaker and SQL guide, as FileMaker may be an ODBC source and the SQL queries made against it may vary from the terms used by ExecuteSQL(). This is not a complete FileMaker and ESS guide using SQL calls (if using Import or Execute SQL script steps or ExecuteSQL() function or ESS). It may not have all the nuances needed for other data sources. This is the ExecuteSQL() function reference for which you’ve been waiting. The FileMaker 12 ODBC and JDBC Guide is helpful, but it has uses outside (and beyond) the ExecuteSQL() function. Any discrepancies between the reference and the function will be noted here, if possible.

Continue reading “The Missing FM 12 ExecuteSQL Reference” →

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

Outer Joins in FileMaker 12, part 2

Kevin Frank

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 2” →

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

Outer Joins in FileMaker 12, part 1

Kevin Frank

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 “Outer Joins in FileMaker 12, part 1” →

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

Aggregates (Summary Fields) in Filtered Portals

beverlyvoth

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. Voila! your count is now filter-specific. (The instructions for creating the filter for a portal and summary field are below.)

This recent topic led me to consider what other aggregate fields could be used with the filtered portal. And what about that Go To Related Record script step? Does it only show the related FILTERED records or all related records? The demo (bvoth_aggregates_in_portals.fmp12) and article have been created to answer these questions.

I started using filtered portals in cross-tab reports shortly after they were introduced. These are generally ways to show something very specific in each portal with sorts and filters and usually one row only. I had not explored the use of aggregates in these cross-tab reports until now.

Kudos to those before me that may have discovered these answers and tricks, too.

Continue reading “Aggregates (Summary Fields) in Filtered Portals” →

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

More Fun with Value Lists, part 2

Kevin Frank

Update 20 Aug 2012: Dr. Noda has updated the demo files to include Rob Russell’s clever fill graphic trick (as per the responses following the article). Thank you Rob and Dr. Noda.

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 it shows some clever outside-the-box thinking, and I appreciate Dr. Noda taking the time to share the demo (ValueListWithoutDefinition-RR-Edition) and write up his notes. One of the things I particularly like about this technique is that it allows the order of the value list items to be customized, even though the values come from a table.

Continue reading “More Fun with Value Lists, part 2” →

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

More Fun with Value Lists, part 1

Kevin Frank

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 two variations on a demo which resulted from a discussion with Nick Chapin on FileMaker TechNet. Actually, the first variation is from John (lib_valueLists_john_ahn_mod), and the second is an alternate approach that I came up with. If you’re a TechNet member (registration is free), you can read the original discussion here. At any rate, the objectives were:

  1. Store the entries for all value lists in a single table (thereby allowing authorized users to construct and maintain value lists using name-value pairs, without having access to FileMaker’s “Manage Value Lists”)
  2. Use UUIDs
  3. Use popup menus
  4. Have the value lists work properly in Find mode
  5. Value lists should be “portable” (i.e., accessible from any table context)

Let’s start with John’s version.

This is the value list table:

Continue reading “More Fun with Value Lists, part 1” →

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

Magic Value Lists

Kevin Frank

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 been something along the lines of…

  • What app are you using? (Because it sure as heck ain’t FileMaker.)
  • Why are you wasting my time with this nonsense?
  • Is today April Fool’s Day?
  • What are you smoking?

But then John Ahn showed this amazing Conditional Value List demo during the DevCon “Unconference” session devoted to ExecuteSQL (see previous posting), and to my way of thinking, the most intriguing part of session was only incidentally concerned with SQL, because John seemingly had achieved the impossible — a value list based on an unstored field.

Continue reading “Magic Value Lists” →