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. Wow, that’s pretty sweet. On an exact topic I’m having trouble with as well. Thank you for the demos you provide.

  2. 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.