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

Demo 4

Here we begin with a simple table view showing products and related info.

2019-11-11_063230.png

Clicking the Search button brings up the filter interface we saw in demo #2 last time, but now repurposed as a card window.

2019-11-11_071340.png

The Find button will be greyed out until you make one or more choices from the pop-up menus. When you click Find this script will run.

2019-11-11_10-38-15.png

So, for example, enter these criteria and click Find…

2019-11-11_16-02-52

…and you will see these results

2019-11-11_16-06-09.png

Demo 5

In this demo, choices made via the pop-up menus are reflected in the portal.

2019-11-11_065248.png

I hesitate to call this a “filtered portal”, though that clearly describes what’s happening, because we are not using FileMaker’s built-in portal filtering.

2019-11-11_16-11-02.png

Instead the filtering is handled relationally…

2019-11-11_16-19-14.png

….with a global text field (“mlk”, i.e., multiline key) on the left, and the product primary key on the right.

When the user makes or clears a selection from one of the pop-up menus, this script is invoked…

2019-11-11_16-26-07.png

…the mlk field is populated with a return-delimited list of product ids, and the portal is refreshed.

One other item of interest is the clickable-sortable column headings.

2019-11-11_16-52-07.png

The sorting technique is basically the same as the one explored in detail in Portal Sorting, part 2 (way back in 2011), except one of the variables has been renamed, and Hide Object is now used to selectively display the proper sort indicator.

3 thoughts on “2-Column Magic Value Lists, part 2”

  1. Hi Kevin,

    First I want to thank you for your amazing website, it has been a great source and inspiration for me over the years!

    I’ve been working on 2-column MagicValue lists for a while and have come based on your original article on VirtualList to a similar approach.
    Instead of having multiple field-pairs in one VL, I’ve made a generic VL, getting it’s $$-var from its table- and field names, so it can just be duplicated if another value list is needed. A ValueListLink-table with just one field (no records) for the cross-join connects all the ValueLists.

    I’ve merged it into your sample file, might be of interest.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.