Category Archives: Level: Advanced

Custom Field-Based Value Lists



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? 


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


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

Virtual List Reporting, part 3

Last year I posted a couple articles on the topic of virtual list reporting (part 1 and part 2), a.k.a. VLR. Today we’re going to look at some additional things you can do with VLR, and to avoid a lot of repetition, this article will assume the reader is familiar with the material covered in part 1, wherein benefits are extolled and fundamentals explained.


Demo file:  (3.4 Mb compressed)

Did you notice in parts 1 and 2 that we were limited to producing a single report at a time? Yes, we could sequentially output them and concatenate them into a PDF, but we couldn’t display multiple reports on-screen simultaneously, nor could we interact with them in browse mode.

This time around we’re going to look at a method to produce multiple independent reports concurrently, each in its own window, based on a single virtual list table. This will entail generating a large number of dynamically instantiated $$vars[withReps], so we will also explore a way to automatically clear them out (without the overhead of having to keep track of them during instantiation).

Disclaimer/Warning: before running multiple Customer reports, open your Data Viewer and activate the Watch tab. You don’t want to display the Current tab with 5K or more variables in memory, unless you’ve got a lot of… time… on… your… hands.

Continue reading

Virtual List Table Of Contents

A few years back I did an article on producing a PDF catalog + table of contents, with the TOC based on a vendor table and the catalog entries based on a products table. Today we’re going to use a different approach to generate a table of contents, building on techniques explored in last year’s Virtual List Reporting series… specifically we’re going to generate one big PDF combining nine reports, prefaced by a table of contents.


Demo files: and


Continue reading


Earlier this year, in Virtual List Reporting, I used a custom function written by Agnès Barouh called CustomList to generate a couple basic lists, and then remarked…


Well today I’m going to attempt to make good on that assertion. In case you aren’t already familiar with CustomList, it allows you to iteratively generate and/or parse lists using the full power of the FileMaker calculation engine. CustomList can process up to 500,000 rows — it is non-recursive and very fast — and today we’re going to use it to solve a variety of problems, both real world and theoretical. [Actually, the row limit can be adjusted, and it varies by platform — for full details see the CF definition.]

Today’s article features four demos…

…as well as a number of examples that don’t require a demo to illustrate their point. Continue reading

Virtual List Reporting, part 2

Welcome back for the second installment in our exploration of Virtual List Reporting (a.k.a. VLR). Demo file:


To avoid repetition, this article will assume the reader is familiar with concepts and techniques introduced in part 1 (some of what follows may appear to be gibberish if the reader is not)… but to briefly recap, here are some benefits of VLR:

  • Flexible framework accommodates complex reporting challenges
  • Fast performance (we use the Multifind technique in this demo)
  • No need to tamper with schema in your data tables or on the relationships graph
  • Unlike traditional FM reports, you can easily combine data from unrelated tables (we saw this in report 6 in part 1)
  • Under certain circumstances, VLRs can be much faster to develop than traditional FM reports (as per discussion of report 3 in part 1)
    Continue reading

Virtual List Reporting, part 1

Self-plagiarism alert: to avoid repeatedly referring the reader back to earlier virtual list articles, portions of text from those earlier articles are incorporated here.

Welcome to the first installment of a multi-part series on producing reports using the virtual list technique, or more properly, collection of techniques. Demo file:


Invented and popularized by Bruce Robertson, virtual lists are incredibly flexible, and have made a number of appearances here in the past, including… Continue reading

Set Variable by Name

29 July 2017: custom function updated to simplify the clearing of existing vars.

16 April 2017: custom function has been updated to auto-interpret dates (rather than treating them as sequential division problems)… e.g., to recognize “4/16/2017” as a date, as opposed to “4 ÷16 ÷ 2017”.

Recently, in the midst of various reporting and charting projects, I’ve found myself wishing for an easy way to “dynamically instantiate” one or more variables… in other words, set them by name, with the name determined programmatically, as opposed to being hard coded.

This issue has come up from time to time on this blog over the last five years or so, most notably in Dynamic Variable Instantiation, where I observed that…


…and then proposed an unwieldy mass of gobbledygook as a workaround, e.g.,


Actually, the preceding isn’t too bad for occasional use, especially if the value you’re passing to the variable is as simple as the GetSummary string shown above. Continue reading