1. Pages:
  3. 1
  4. 2
  5. 3
  6. 4
  7. 5
  8. 6
  9. 7
  10. ...
  11. 25
Level: Advanced, Version: FM 16 or later, Virtual List

Virtual List Simplified

Kevin Frank

Demo file: virtual-list-simplified.zip

Note 1: The example in today’s article/demo is intentionally very basic.
Note 2: The demo is self-populating to keep the data current, so the values you see in the screen shots will not exactly match those you encounter in the demo.

Recently I had the pleasure of discussing virtual list with Paul Jansen and Jeremy Brown on The Context podcast. One consequence of having written so much on the subject over a period of many years, is that information has been spread across many articles. Another consequence is that my thinking re: certain implementation specifics has changed over time.

At the risk of stating the obvious, there are many, many ways to skin the virtual list cat, and the purpose of today’s article is not to say “this is the best way”, or imply that other approaches are flawed, but simply to propose one particular approach you might take — especially if you are either: a) new to virtual list, or b) already using virtual list, but aren’t completely happy with your current implementation.

At any rate, my aim today is to gather some useful insights from earlier articles into a single document (with an occasional new idea thrown in as well), and some of what follows has been recycled from those earlier articles. Continue reading “Virtual List Simplified” →

Level: Any

Table Of Contents for FileMaker Hacks

Kevin Frank

As 2019 draws to a close, I am mildly astonished to note that FileMaker Hacks is now nine years old. As promised in my recent conversation with Jeremy Brown, and in response to a number of  reader requests, here, for the first time, is a comprehensive table of contents.

While some of these articles have become obsolete, a surprising number of the older ones are still relevant today… a tribute to the continuity of FileMaker and how knowledge gained in one version can remain useful indefinitely.

Another aspect driven home for me during the compilation of this T.O.C. (which entailed revisiting 200+ articles — some of which I’d completely forgotten about) is what a profoundly collaborative effort this blog has been, not only thanks to the illustrious list of guest authors, but also due to the sheer volume of articles that, while written by me, would not have existed without the input, examples and inspiration provided by so many others in our community.

28 Dec 2019: Table of Contents has been moved to its own page, and will be updated as new articles appear.

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

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

Level: Intermediate, Version: FM 18 or later

New in FM 18: While, part 2

Kevin Frank

Today we’re going to dig a little deeper into the new-in-18 While function, and to avoid repetition, will assume readers are familiar with last month’s article on SetRecursion and While. We’ll look at some new examples ranging from basic to advanced, investigate the circumstances under which While can use variables previously declared via Let, and check out some benchmark results comparing While vs. CustomList.

Demo Files

Continue reading “New in FM 18: While, part 2” →

Level: Intermediate, Version: FM 18 or later

New in FM 18: SetRecursion and While

Kevin Frank

Author’s note: Inevitable comparisons will be drawn between the new While function and the venerable CustomList custom function, which I wrote about at length several years ago. Preliminary testing indicates that CustomList is faster under certain circumstances and While is faster under others. In my opinion, both of them belong in your developer tool kit.

Today we’re going to take a look at two new functions introduced in FileMaker 18: SetRecursion and While, and you can follow along in today’s demo files if you are so inclined.

maclaurin-series.png

Demo files: While-Sandbox.zip and Maclaurin-Series.zip

Continue reading “New in FM 18: SetRecursion and While” →

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