Tag: Virtual List

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

2-Column Magic Value Lists, part 2

Kevin Frank

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

Kevin Frank

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), Marcelo Piñeyro (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” →

Level: Intermediate

Sorting on a Field Rep > 1

Kevin Frank

Recently, in a virtual list report, I had a need to sort on rep number 2 of a repeating field called cell_text_r.

2018-01-27_150024

FileMaker does not offer an obvious way to script a sort on a field rep > 1, and I had worked around the problem in the past by…

A. Defining some special non-repeating fields in my virtual list table like so

2018-01-27_145104 Continue reading “Sorting on a Field Rep > 1” →

Level: Intermediate, Virtual List

Virtual List on Steroids

Kevin Frank

[Note 1: as always a huge thank you to Bruce Robertson, for inventing virtual list, and for many other contributions to the FM community over the years.]

[Note 2: the following assumes the reader is familiar with basic concepts presented in Virtual List Reporting, part 1.]

This is a follow up article to document new material from my recent FM-DiSC presentation: Virtual List on Steroids. The presentation was intended for developers who are (at least) somewhat familiar with virtual list, but for those not actively using virtual list, I decided to begin with a demonstration of “the problem” (or one of the problems at any rate) that virtual list was invented to solve.

Demo file #1: Step One – Admitting There’s A Problem

2018-01-22_112738

Continue reading “Virtual List on Steroids” →

JSON, Level: Intermediate, Version: FM 16 or later

JSON + Virtual List

Kevin Frank

31 July 2017: As per my exchange with Beverly Voth in the comments section of part 2, the date encoding technique used here is strictly intended for situations where FileMaker will both produce AND consume the JSON.

29 July 2017: demo has been updated to require a minimum version of FileMaker 16.02 as per my exchange with John Renfrew in the comments section.

One nice thing about FileMaker being on a yearly release cycle is that there is always something new to learn and write about… and, having recently attended a pair of highly informative sessions on the topic of JSON (JavaScript Object Notation) at Devcon 2017 — thank you Todd Geist and Anders Monsen — it is clear to me that JSON + virtual list is a compelling combination.

2017-07-27_152145

Demo file: json-plus-virtual-list.zip

My original plan was to integrate JSON with virtual list reporting, but in the interest of clarity, I decided to save that for a future article. Today we have a straightforward demo that a) generates a small amount of JSON from a standard FileMaker table, and then b) renders it in a virtual list table. Continue reading “JSON + Virtual List” →

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

Virtual List Reporting, part 3

Kevin Frank

Update 30 September 2021: while this article is worth reading for background information, I now recommend using the approach utilized here: Virtual List Reporting, part 4

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.

2017-07-21_18-46-06

Demo file: VLR-part-3.zip  (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 Reporting, part 3” →

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

Virtual List Table Of Contents

Kevin Frank

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.

2017-01-30_173715

Demo files: virtual-list-toc-v1.zip and virtual-list-toc-v2.zip

2017-01-30_201617

Continue reading “Virtual List Table Of Contents” →

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

Virtual List Reporting, part 2

Kevin Frank

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

2016-06-21_045615

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 2” →
Level: Advanced, Version: FM 13 or later, Virtual List

Virtual List Reporting, part 1

Kevin Frank

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: VLR-part-1.zip

demo

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 “Virtual List Reporting, part 1” →