Category: Level: Advanced

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

Level: Advanced, Version: FM 10 or later

Thinking About Value Lists, part 3

Kevin Frank

Welcome to the third installment in this series. We’ve been exploring various issues and behaviors in connection with 2-column value lists (VL’s), and today we’re going to look at, and propose a work around for, an issue with filtered value lists in Find mode.

All of today’s demo files feature a basic expense submission system, with each portal row representing a receipt that the submitter would like to be reimbursed for. Here it is in Browse mode:

And here’s the Relationships Graph:

Continue reading “Thinking About Value Lists, part 3” →

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

Level: Advanced, Version: FM 9 or later, Virtual List

User-Friendly Excel Exports, part 5

Kevin Frank

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

Well, we’ve reached what may well be the final posting in this extended series of articles. We’ve covered a lot of ground, and this article assumes familiarity with what has come before (in parts 1, 1.1, 2, 3 and 4).

Today we’re going to look at a couple “Virtual List” implementations of the User-Friendly Excel Export technique, which will, among other things, allow us to eliminate these calculated fields from the Contacts table…

…as well as the corresponding set of fields in Donations.

Bruce Robertson’s Virtual List technique has made a couple previous appearances on FileMaker Hacks…

…so I won’t rehash its merits in detail here, but if you aren’t familiar with it, I recommend you check out one or both of these earlier articles. Continue reading “User-Friendly Excel Exports, part 5” →

Level: Advanced, SQL, Version: FM 11 or later

FileMaker 11 Internal SQL Changes, part 1

Kevin Frank

March 9, 2010 is a date I recall quite clearly: not only was it the release date for  FileMaker 11; it was also the day a bunch of my FileMaker SQL code broke, due to changes in the FM 11 internal SQL parser.

If you’re new to this subject, or perhaps a bit rusty, I have written about internal SQL (a.k.a. FQL) on various occasions over the last year, and there are a number of important points that I won’t discuss today, because they have already been mentioned in one or more of these articles:

What follows is by no means exhaustive, but merely what I’ve documented. There is no comprehensive source of information about FQL, although chapter 7 of the FileMaker 11 OBDC and JDBC Guide is a good place to start (just remember that not everything you read there will apply to FQL).

Continue reading “FileMaker 11 Internal SQL Changes, part 1” →

Chart, Level: Advanced, Version: FM 11 or later

Array Charting, part 2

Kevin Frank

Last week we looked at building a FileMaker 11 native chart based on data that has been parsed into a field-based array.

2011-07-11-b

I happen to like field-based arrays, for reasons I’ll get to in a minute, but is a field-based array really necessary to generate the above chart? Continue reading “Array Charting, part 2” →