Category: Level: Advanced

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

JSON, Level: Advanced, Version: FM 18 or later

FM18: JSON.InsertArrayElement

Kevin Frank

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

Demo file: JSON-Array-Custom-Functions.zip (requires FM 18 or later)

201-08-23-animated

Have you ever noticed that FileMaker does not provide a JSON function to insert a new element into an existing array? For example, given this array (and bearing in mind that JSON uses a zero-based index)…

[ “A” , “B” , “C” , “D” ]

…JSONSetElement ( array ; 2 ; “XXX” ) will transform it to…

[ “A” , “B” , “XXX” , “D” ]

… but we have no native JSON function to “push” a new element into position 2 so that existing elements slide one position rightward to accommodate, like so:

[ “A” , “B” , “XXX” , “C” , “D” ]

Continue reading “FM18: JSON.InsertArrayElement” →

JSON, Level: Advanced, Version: FM 18 or later

FM18: JSON.UpdateArrayElement

Kevin Frank

Disclaimer: the technique shown in this article is an attempt to work around an annoyance that can crop up when using FileMaker’s native JSONSetElement function, and is provided on an as-is basis. Use with appropriate caution and at your own risk.

Acknowledgement: I owe a debt of gratitude to Paul Jansen for many illuminating JSON-related discussions over the last year or so. Thank you Paul.

Bug fixes: Demo file has been updated to v3.

Introduction

Today we’re going to look at a custom function, JSON.UpdateArrayElement, written to circumvent a problem you may encounter when using JSONSetElement. (Note: see Thinking About JSON, part 1 and part 2 if you need a basic FM/JSON overview or refresher.)

Demo file: JSON-UpdateArrayElement-v3.zip (requires FM 18 or later)

2019-08-13_16-02-58.png

Why did I write a custom function to do what JSONSetElement apparently already does? Because under certain circumstances JSONSetElement does not work the way I believe it should. We’ll get to the custom function in a minute, but first there are a couple issues we need to examine. Continue reading “FM18: JSON.UpdateArrayElement” →

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

Magic Portals

Kevin Frank

Today we’re going to look at a design pattern I’ve recently been using to accommodate a client request. The request is to be able to view and edit a parent, all related children, and all related grandchildren via a single “flattened” interface.

2019-04-24_16-22-61

Demo file: magic-portals.zip (requires FM 16 or later) Continue reading “Magic Portals” →

JSON, Level: Advanced, Version: FM 16 or later, Virtual List

Virtual List Reporting with JSON Arrays

Kevin Frank

Acknowledgment: 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.

Introduction

As a follow up to my recent “Virtual List on Steroids” presentations at DIG-FM and dotFMP, today I want to take a fresh look at using JSON arrays in conjunction with Virtual List Reporting.

JSON Array

JSON arrays + Virtual List are a natural fit, but, as we shall see, small changes in methodology can make a huge difference in terms of performance, and the approaches we’re going to explore today are the result of a lot of trial and error, and incorporate feedback and suggestions from Dave Graham, Paul Jansen and Perren Smith.

What follows will assume the reader is somewhat familiar with the basic ideas behind Virtual List. If you aren’t familiar, or need a refresher, check out Virtual List Simplified. We’ll get to the demos in just a minute, but first, a review of some of the benefits of using virtual list.

  • Flexible framework accommodates complex reporting challenges
  • Fast performance
  • 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
  • Under certain circumstances, virtual list reports (VLRs) can be much faster to develop than traditional FM reports
  • 100% multi-user safe and friendly

Overview

Here’s the main idea in a nutshell:

2018-06-24_182509.png Continue reading “Virtual List Reporting with JSON Arrays” →

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

Custom Field-Based Value Lists

Kevin Frank

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

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

Virtual List Reporting, part 3

Kevin Frank

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

CustomList

Kevin Frank

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…

2016-09-19_000003

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 “CustomList” →

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