Today we’re going to do some experiments with the FilterValues function, which takes two return delimited lists for input…
FilterValues ( textToFilter ; filterValues )
…and produces the intersection of the two lists as output.
If you’ve generated JSON objects via FileMaker, you’re likely aware of the disparity between the order in which you specify the elements, and the order in which they subsequently appear (i.e., alphabetized by key name). As I wrote two years ago (in Thinking About JSON, Part 2)…
Both JSONSetElement and JSONFormatElements will automatically alphabetize key/value pairs within JSON objects. This can be disconcerting if you aren’t expecting it, but eventually you work through the stages of grief and come to accept that it’s just the way things are. The JSON Data Interchange Standard definition at json.org specifies that “An object is an unordered [emphasis mine] set of name/value pairs”, whereas an array is an “ordered collection of values”. In other words, by definition the order of the key/value pairs within JSON objects simply does not, and should not, matter.
Well… okay… you know that, and I know that, and FileMaker knows that, as do the JSON Jedi… but what if you are demonstrating a proof of concept to a client, and to avoid cognitive dissonance and unnecessary explanation you’d like to “doctor the evidence” (so to speak) and order the elements meaningfully?
For example, you’d like them to see this…
Long time readers of this blog will be familiar with the following scenario —
An ExecuteSQL expression is given, e.g.,
ExecuteSQL ( " SELECT SUM ( net_amount ) FROM cc_transactions WHERE batch_date = ? AND batch_region = ? AND card_type = ? " ; "" ; "" ; $theDate ; $theRegion ; $theCard )
…followed by a disclaimer along the lines of
For readability, static code has been used… in the real world I would employ robust coding practices to prevent accidental breakage due to field and/or TO renaming.
…with the link pointing to custom functions utilizing a combination of GetFieldName and Quote to ensure that using reserved words or potentially-problematical characters (such as a space or #) in table occurrence or field names, or renaming either of the preceding, will not break your SQL code. Continue reading “GetFieldName Revisited”
[Note: several hours after posting this article I realized the “Hide Object” calc could be streamlined. Screen shot and demo have been updated to reflect this.]
Demo file: virtual-portal-v2b.zip (requires FM 18 or later)
This is a quick follow up to the Virtual Portal article I posted the other day. As you may recall, the objective was to use virtual list to display disparate entities in a portal…
…via an array like this…
Demo file: virtual-portal-v1b.zip
This is a follow-up to last month’s article on virtual list, and this time we’re going to explore a way to use virtual list in a portal.
Imagine you have built a system where a Company can be a parent of a Mill, Refinery, Estate, or another Company.
Each of these entities exists as a separate table in your database…
…and from the perspective of a Company record, you’d like to be able to see all immediate children. Continue reading “Virtual Portal”
Demo file: virtual-list-simplified.zip
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”
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.
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.
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…
…we came up with a way to sort our value list (a.k.a. “VL”) items like so…
…as opposed to what you would get with a standard field-based VL, i.e., this:
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).
Demo file: JSON-Array-Custom-Functions.zip (requires FM 18 or later)
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” ]