Category: ExecuteSQL

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

ExecuteSQL, SQL

FM 12 ExecuteSQL “Unconference” Session

Kevin Frank

11 Sep 2012: The ConditionalVL_SQL demo has been updated to fix a minor bug.

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 basic intro, and showed a few simple demos. Next we moved on to a discussion of some of the issues, nuances, gotchas, etc. Then John showed a couple ExecuteSQL-powered demos which I strongly commend to your attention: a) Multi-Column Dynamic Portal Sorting…

…and b) Dynamic Value Lists…

…which, incidentally, caused every jaw in the room to drop, due to the fact that John’s method of constructing these conditional value lists seems to violate at least one fundamental law of FileMaker physics — more on this in an upcoming posting.

Here are links to everything we referred to during the presentation, in roughly chronological order.

FileMaker Hacks postings:

John Ahn’s demos:

Other links:

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

Dwindling Value Lists, part 1

Kevin Frank

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 value lists that shrink up, by removing individual list items as they are selected. They can come in very handy when you need to schedule resources and want to prevent double booking. This example comes from one of today’s demo files, Dwindling Value Lists, and shows how a DVL might be used to schedule employees for a work shift.

Essentially, a DVL is a filtered value list that updates in real time (or something very close to real time), and this is done by building a multiline key of selected values, and then filtering the VL to only show remaining eligible values. Continue reading “Dwindling Value Lists, part 1” →

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

FM 12 ExecuteSQL: Robust Coding, part 2

Kevin Frank

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 examine #6 on the list, and then look at a few miscellaneous odds ’n’ ends, and today’s demo file is ExecuteSQL Sandbox, v2, if you’d like to follow along.

Changing a Field Type

Why would changing a field’s type cause a problem? Well, first off, as I mentioned last month, if you are using that particular field as a predicate in a JOIN, the query will break, and your reward will be the the dreaded “?”.

I don’t have a fix for this, just some advice: Don’t change a field’s type if you’ve used it as a JOIN predicate in a working SQL statement. (The only reason I’ve needed to change a field type recently was to fix a broken JOIN where the field types were mismatched.)

Continue reading “FM 12 ExecuteSQL: Robust Coding, part 2” →

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

FM 12 ExecuteSQL: Robust Coding, part 1

Kevin Frank

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

Demo file: ExecuteSQL-sandbox.zip

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 renamed and your code breaks.” He had a good point: code is “brittle” if it works initially, but then subsequently breaks as a result of a seemingly innocuous action.

Here are some things that can cause ExecuteSQL code to break:

  1. Renaming a table occurrence (TO)
  2. Renaming a field
  3. Using a “reserved word” as a field or TO name
  4. Having a problematic character in a field or TO name
  5. Executing the code in a foreign country (!)
  6. Changing a field’s type (e.g., from text to number or vice versa)

…and today we’re going to look at some defensive measures we can employ to prevent problems when these things occur.

Using the “orders” table in the demo file…

Continue reading “FM 12 ExecuteSQL: Robust Coding, part 1” →

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

FM 12 ExecuteSQL: Dynamic Parameters, pt 2

Kevin Frank

This is a quick follow-up to part 1, with a couple more observations about dynamic parameters.

Embedded Apostrophes

Here’s one I can’t believe I forgot to mention 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 contains an embedded apostrophe, in standard SQL you must escape it by prepending another apostrophe, for example compare these two “standard” (non-dynamic) queries:

As you might expect, you don’t have to worry about this if you instead use a dynamic parameter… just quote the search term the way you would any FileMaker text string (i.e., in double quotes) and go about your business.

Continue reading “FM 12 ExecuteSQL: Dynamic Parameters, pt 2” →

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

FM 12 ExecuteSQL: Dynamic Parameters, pt 1

Kevin Frank

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. I’ll get to the reason for this in a minute, but first let’s compare two ExecuteSQL statements, which were discussed last month in FM 12 ExecuteSQL, part 1.

Continue reading “FM 12 ExecuteSQL: Dynamic Parameters, pt 1” →