1. Pages:
  3. 1
  4. 2
  5. 3
  6. 4
  7. 5
  8. 6
  9. 7
  10. ...
  11. 31
JSON, Level: Advanced, Version: FM 18 or later

JSONQuery v2 Conversation with Steve Senft-Herrera, part 1

Kevin Frank

Background

JSONQuery 2.0 was released last month with a wealth of new features, along with some improvements to existing features as well. Steve and I spoke about it recently, and we’ll get to that in just a moment. But first, for those who may not be familiar with JQ, here’s a bit of background information.

What is JSONQuery?

JSONQuery is a FileMaker custom function authored by Steve Senft-Herrera that enables you to query JSON (works with FM 18+).

Basic Things JSONQuery Does

1) Query – Allows for finding elements that match a supplied value, with a variety of options for operators.

2) Aggregate – Allows for returning an aggregate, e.g., SUM, AVG, LIST, etc., of values taken from either the matched elements, or all of the input.

3) Transform – Allows for customization of how the results are returned, with the option to pick-and-choose what attributes are included in the output, and how they are named.

Official Site

https://github.com/steve-ssh/FMP_JSONQuery

Other Resources

Introductory Remarks

Kevin Frank: Welcome back Steve. It’s been 3 years since we had our first JSONQuery conversation, and I’m so excited about JQ v2.

Steve Senft-Herrera: Thank you. I checked out the dates just before we met tonight, and saw that it is about 3 years.

KF: Hard to believe it’s been that long, and I want to congratulate you, because I know that it’s been a formidable effort on your part to get v2 out. And I speak for a lot of FM developers when I say we truly appreciate it.

SSH: Well, thanks. The formidable part, I think, was actually just managing my own life schedule to make time for it, more than the actual work that had to be done. There were long periods of time where the work was mostly finished, but just had to sit there patiently before I could get to it and take it to the next milestone. Now it’s officially out. Continue reading “JSONQuery v2 Conversation with Steve Senft-Herrera, part 1” →

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

JSONQuery 2.0 is Here

Kevin Frank

This is a quick post to say that JSONQuery 2.0 is finally here with many new features, and some improvements to existing features as well.

What is JSONQuery?

A FileMaker custom function authored by Steve Senft-Herrera that enables you to query JSON (works with FM 18+).

Basic Things JSONQuery Does

1) Query – Allows for finding elements that match a supplied value, with a variety of options for operators.

2) Aggregate – Allows for returning an aggregate, e.g., SUM, AVG, LIST, etc., of values taken from either the matched elements, or all of the input.

3) Transform – Allows for customization of how the results are returned, with the option to pick-and-choose what attributes are included in the output, and how they are named.

Official Site

https://github.com/steve-ssh/FMP_JSONQuery

Continue reading “JSONQuery 2.0 is Here” →

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

Fast Summaries to JSON, part 2

Kevin Frank

This is a follow up to last week’s Fast Summaries to JSON and will assume the reader is familiar with that material.

Demo Files

After last week’s article appeared, a reader contacted me wondering whether the technique could be adapted to provide a more literal JSON representation of what was visible on-screen. The answer turns out to be yes, with a bit of additional work…

Continue reading “Fast Summaries to JSON, part 2” →

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

Fast Summaries to JSON

Kevin Frank

The other day some colleagues were discussing a need to produce a JSON representation of the data in a summary report. Long time readers of this blog will know that I am obsessed with fond of the Fast Summary technique, and it seemed like it might be a good fit for this challenge, so I built some demos to find out if that would be the case.

Demos 1 and 4 will work with FM 16 or later. Demos 2 & 3 utilize recently-added JSON features, so require FM 21 or later.

Continue reading “Fast Summaries to JSON” →

ExecuteSQL, JSON, Level: Advanced, SQL, Summary List, Version: FM 20.2 or later

OnWindowTransaction JSON

Kevin Frank

Demo file:  on-window-transaction-json-v2.zip
Credentials:  admin / admin
Minimum version:  20.2  [for both client and server]

Note: This demo file is optimized for Perform Script on Server (PSOS), and is intended to be hosted on FileMaker Server or FileMaker Cloud.

Introduction

Recently a colleague mentioned that they were running into a performance issue trying to load a complex found set (customers, invoices and line items) as an array of objects into a $$variable. It was simply taking too long across a WAN connection, and contributing factors included unstored calculations referencing related unstored calculations, and the rendering taking place client side as opposed to server side.

We’ve explored rendering a found set as JSON here previously…

…but both of the above involve constructing the JSON on the fly at runtime. My colleague was hoping for something faster, and today we’re going to look at a fresh approach. Continue reading “OnWindowTransaction JSON” →

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

FastRange Custom Function

Kevin Frank

Recently I needed to produce a large range of consecutive numbers. FileMaker doesn’t provide a built-in function for this but it’s easy enough to accomplish, for example, using the While function or a recursive custom function. In this case, I decided to go a different route based on a tip I’d seen someone post years ago (I’ve forgotten whom so cannot give proper credit) that you can leverage a couple of FileMaker’s JSON functions to facilitate this task, with the advantages being blazingly fast performance and an opportunity to think outside the box.

Demo file:  fastrange-cf-v1.04.zip
Continue reading “FastRange Custom Function” →

Level: Intermediate

Replace + GetNthRecord to Fill Down

Kevin Frank

This afternoon I pulled this data into FileMaker from a spreadsheet, and needed to “fill down” the region names in f2.

Of course I could have filled the region down in Excel prior to importing, but here I was already in FM. At first I was going to use a looping script, but then it occurred to me that it would be nice if Replace could do the trick. Continue reading “Replace + GetNthRecord to Fill Down” →

Level: Intermediate, Version: FM 20.3 or later

January 2024 FM-DiSC Presentation

Kevin Frank

Here are resources re: my January 12th FM-DiSC presentation.

Layout Calculations

Generating Spreadsheets with LibXL

YouTube recording of meeting

Level: Intermediate, Version: FM 20.3 or later

Layout Calculations in FileMaker 20.3

Kevin Frank

Update 12 Dec 2023: this article has been updated as per my exchanges with Fabrice Nordmann in the comments section below. Thank you Fabrice for the reality check.

This is a follow up to last month’s article, Layout Calculations in FileMaker 20.2, and will assume the reader is familiar with that material. Today we will be taking a look at some changes to Layout Calculation behavior in the newly-released FileMaker 20.3.

#1. Calculation Result Type Can Now Be Specified

We can now specify the result type for a layout calculation…

…whereas in FM 20.2, the result type could only be text.

Initially I was under the impression that this would yield benefits when applying, for example, date specific formatting options, but as Fabrice points out, the formatting behavior is identical regardless of what you do with the result type. Continue reading “Layout Calculations in FileMaker 20.3” →

Level: Intermediate, Version: FM 20.2 or later

Layout Calculations in FileMaker 20.2

Kevin Frank

The recent release of FileMaker 20.2 introduces a feature that developers have long been asking for: the ability to define calculations at the layout level without resorting to workarounds (such as those we saw in Button Bar Segment Fun, part 1 and part 2.)

You can read the official word(s) from Claris here…

…and today I just want to share a few observations. First, in case you haven’t yet gotten your feet wet with layout calculations:

  1. Make sure you’re using FileMaker Pro 20.2 or later
  2. Go into layout mode
  3. Choose “Layout Calculation…” from the Insert menu
  4. The Specify Calculation dialog will appear
  5. Enter the syntax for your calculation, e.g.,
    "Hello " & Get ( UserName )
  6. Click OK

You’ll see something like this in layout mode…

…and this in browse mode (if your user name happens to be Kevin).

Okay… let’s go back into layout mode for some experimentation. If you control-click on the layout calc block you can access the Specify Calculation dialog…

…but, if you prefer, you can also edit the layout calc directly on the layout.

Continue reading “Layout Calculations in FileMaker 20.2” →