ExecuteSQL, Level: Intermediate, SQL, Version: FM 16 or later, Virtual List

2-Column Magic Value Lists, part 2

Disclaimer: the techniques shown in this article are provided on an as-is basis. Use with appropriate caution and at your own risk.

Introduction

This is a quick follow-up to last month’s article on 2-Column Magic Value Lists, and, to avoid repetition, will assume the reader is familiar with that material. A colleague pointed out that it might be helpful to offer some real-world applications of this somewhat esoteric technique, and today we have two demos based on last month’s demo #2, but this time actually doing something useful.

2019-11-11_065703.png

Demo Files

Continue reading “2-Column Magic Value Lists, part 2”

ExecuteSQL, Level: Advanced, SQL, Version: FM 13 or later, Virtual List

2-Column Magic Value Lists

Disclaimer: the techniques shown in this article are provided on an as-is basis. Use with appropriate caution and at your own risk.

Acknowledgements: today’s article would not have been written without the inspiration provided by Bruce Robertson, John Ahn (FM 12 ExecuteSQL Unconference Session), Andries Heylen (Magic Value Lists), Marcel Pinyero (How to Extend FileMaker Value List Sort Capabilities) and Jonathan Fletcher (It’s Sorta A Value List Thing).

Introduction

Last year, in Custom Field-Based Value Lists, we looked at a technique to enable single-column field-based value lists to display their contents in entry order, as opposed to standard index-based alphanumeric order. For example using the contents of this field (from a single-record table) as the basis for a value list…

2019-10-13_135535.png

…we came up with a way to sort our value list (a.k.a. “VL”) items like so…

2019-10-13_135628

…as opposed to what you would get with a standard field-based VL, i.e., this:

2019-10-13_135646.png

I ended the article by saying…

Today’s article looked at value lists based on return-separated values in a single field. In an upcoming article we’ll explore implementing a similar technique for sorted two-column value lists based on entries in a dedicated value lists table where each list item lives in its own record.

…and now, nineteen months later, am ready to pick up where we left off last time, and to avoid repetition, will assume the reader is at least somewhat familiar with the issues and techniques covered in that article (Custom Field-Based Value Lists).

Note: if you aren’t clear on why a 2-column value list might be preferable to its single-column counterpart (or to a custom value list for that matter), check out Thinking About Value Lists, part 1.

Demo Files

Continue reading “2-Column Magic Value Lists”

ExecuteSQL, Level: Intermediate, SQL, Virtual List

Bill of Materials

29 Mar 2019: Demo file has been updated to v2. See comments for details.

Editor’s note: Geoff Gerhard of Creative Solutions is one of those “quiet” developers who has been helpfully sharing his knowledge in online FileMaker forums for more than 20 years. I personally have benefited enormously from his expertise, and am pleased to feature his first appearance as a guest author here on FileMaker Hacks.

Demo file: BillOfMaterialsDemo-v2.zip

Continue reading “Bill of Materials”

ExecuteSQL, Level: Advanced, SQL, Version: FM 13 or later, Virtual List

Custom Field-Based Value Lists

Introduction

Does this sound familiar? You’ve rolled out a solution that includes a single-record Settings table where authorized users can enter/update various system-wide settings, including one or more fields where the items will appear in popup menus… and then comes the inevitable question: Why don’t items in popup menus appear in the order I entered them? 

2018-03-09_14-44-10
The “problem”: field-based value lists sort alphanumerically

It’s a reasonable question, and in the past you might have replied, “Because field-based value lists rely on a field’s index, and their sort order is alphanumeric”… but after reading today’s article your new answer will be: “No problem — let me take care of that for you.”

2018-03-23_120634
From demo #4: field-based value lists mirroring the order of their underlying source fields

(Interestingly, the above is accomplished with the same value list attached to all three popup menus, and no script triggers… or scripting of any kind for that matter. Read on to learn how.)

Continue reading “Custom Field-Based Value Lists”

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

User-Friendly Excel Exports, part 7

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

This is a follow up to last week’s part 6, with three refinements.

Continue reading “User-Friendly Excel Exports, part 7”

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

FM/SQL Portal Filter + Dynamic Sort

Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.

Demo file: fm-sqlportal-filter-and-dynsort.zip (75 Mb compressed, 230 Mb uncompressed)

browseaksortcitycomp Continue reading “FM/SQL Portal Filter + Dynamic Sort”

ExecuteSQL, Level: Any, SQL, Version: FM 13 or later

Tips ’n’ Tricks, part 1

Over the last few years I’ve been collecting miscellaneous tips and tricks with the thought of eventually aggregating them into a blog posting, and it appears that day has finally come. Ideally there will be something new and useful here for developers of all levels, from the neophyte to the most grizzled of FileMaker veterans.

We start off with a couple FM 14-specific tips.

Tip #1: Tool tips in layout mode

In layout mode in FileMaker 14 you can hover your mouse over any badge and an informative tool tip will display.

2015-11-30_13-32-06

Tip #2: Shift-Return in the Script Workspace

Did you know you can add new line above the current line in the Script Workspace by pressing Shift-Return? It works with the standard Return key, as well as the numeric Enter key.

2015-11-30_14-08-17
Before… line 1 is active, and the user is about to press Shift-Return
After... a new top line has been added
After… a new top line has been added, and existing entries have moved down to accommodate

Continue reading “Tips ’n’ Tricks, part 1”

ExecuteSQL, Level: Intermediate, SQL, Summary List, Version: FM 13 or later

Found Set Awareness Revisited

Recently a colleague contacted me with some questions about making ExecuteSQL found-set aware, and I realized that while I had addressed aspects of the problem in various blog postings over a multiyear period (for example, here and here), I had not assembled my thoughts into one cohesive, up-to-date article.

Well, that shortcoming has now been addressed, and today we’re going to look at three demo files, all of which rely on the summary list field type (introduced in FM 13).

9-20-2015 8-52-01 AM Continue reading “Found Set Awareness Revisited”

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

FM 14: Separation Model Data Mining

Note: Interface file #1 requires FM 14 or later; interface file #2 works with FM 12 or later.

What do you get when you combine the Separation Model + FM 14 placeholder text + ExecuteSQL + a million-record table + a variable array + Get(CalculationRepetitionNumber) + the Mod and Ceiling functions + a couple custom functions, with blazingly fast (local, LAN and WAN) performance thrown in for good measure?

This article w/ accompanying demo (fm-14-separated-data-mining.zip), that’s what.

7-7-2015 12-33-33 PM

The Challenge

Provide a data-mining interface to query a million-record table (cc_transactions) containing 20 years’ worth of credit card transactions. The client wants to be able to pick a date via a calendar widget, and see transaction info for that date, summarized by card type, transaction type and region.

7-6-2015 8-54-58 AM

This is a separated solution, with cc_transactions living in a file called z_data.fmp12, and a specific requirement for this project is to not make any schema changes to the data file. The data-mining will take place in a separate interface file… or in this case, two interface files, since we’re going to look at two methods.  Continue reading “FM 14: Separation Model Data Mining”

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

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