Level: Intermediate, Version: FM 13 or later

A Fresh Approach to Deduplication

Editor’s Note: Today I’m pleased to present a guest article by Jon Rosen featuring a creative and performant approach to removing duplicate records.

Recently, I had a situation where I had a found set of more than 500,000 records, but over ⅔ of them were duplicates. To remove the dupes, I initially went with the time-honored method I’ve been using for the last twenty years. I’ve been using it so long, that the original version used global fields because script variables hadn’t been created, yet. But now it seemed to run glacially slow on this large file.

After giving it some thought, I came up with a new method of deleting duplicates that turned out to be simpler and many times faster than the older method. So, let’s start with a review of the original method. There’s a good chance you may be using it yourself.

Eliminate Duplicates (Old Method)

Continue reading “A Fresh Approach to Deduplication”

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

JSON – Force Standard Notation

Introduction

This is a follow up to a behavior I mentioned last month where FileMaker’s JSON functions can transform a number into scientific notation when you might prefer to have that number remain in standard notation.

Demo file: json-force-standard-notation.zip

Note: demo file uses JSONGetElementType so requires FM 19.5 or later.

The overall functionality of the demo was covered last time. This time we’re going to focus on working around the behavior, followed by some related observations… but first let’s review (recycling some screen shots and text from last month’s article). Continue reading “JSON – Force Standard Notation”

JSON, Level: Intermediate, Version: FM 18 or later, Virtual List

JSON Currency Exchange Rates revisited

Demo file: json-rates-via-api-exchangerate-host.zip

Today we’re going to take a fresh look at pulling currency exchange rates into FileMaker, and this article is directly based on its predecessor. Why the re-visitation? Two reasons actually:

  1. The “free” endpoints I relied on back in 2020 (and in 2021 when I revised the original demo) have been monetized, and apart from any cost considerations, I want this demo to work out of the box without requiring an API token.
  2.  The check box set I used for symbol selection last time was designed to accommodate a fixed and relatively small number of entries. That was a short-sighted decision that could not possibly scale gracefully. This time around I’m using an approach that will automatically accommodate any number of symbols.

Continue reading “JSON Currency Exchange Rates revisited”

Beverly Voth, Claris Platform, Claris Pro, Claris Studio, Level: Intermediate

Claris Studio (part 4): Forms the easy way!

About the author: Beverly Voth has been in the Claris FileMaker community many years. In addition to FileMaker Pro and its integrated products, she is a Full Stack Web developer & SQL database administrator. The only recipient of the FileMaker Excellence Award for Outstanding Contribution to the FileMaker Web Publishing Community (DevCon 2003), she’s been advocating Claris FileMaker and web since they could work together.

This article covers more Views, a little errata from past articles (Parts 1, 2, & 3), and where we go from here. This is the final article of the planned four. But if something really inspiring materializes, there may be followup article(s). Some famous “trilogies” (films and books, especially) have gotten additional content, so why not?

Welcome back! The Spreadsheet View and the Dashboard View are not the only features of Claris Studio. I chose them to discuss first because the Spreadsheet has more information about the tables and fields. That information may be helpful on all Views. But we have other Views: anonymous Form submission, a List-Detail View (suitable for searching, adding, editing), a Kanban View (just another way to present the data & edit it), and some new ones since the last article. Remember that Dashboards are created from the Spreadsheet View only, so they do not appear on this graphic:

Click on the Create New View button to see these options.

We can start with one of these views to create the “table” or we can start with existing data (from a Spreadsheet setup or migrated from Claris Pro). Let’s get started with a review of the Forms. Continue reading “Claris Studio (part 4): Forms the easy way!”

Level: Intermediate, Version: FM 19.5 or later, Virtual List

A Summary Field Bug and Workaround

Introduction

Recently a client asked me to implement a virtual list reporting framework similar to the one I wrote about a few years ago in Virtual List Simplified. I added the framework to the client’s hosted file, and things went smoothly until I ran a report similar to the one shown below… and observed that the repeating summary field at the bottom wasn’t rendering. The same report worked flawlessly a) offline, and b) when hosted on FMS 19.4 and earlier, but when hosted via FMS 19.5 or 19.6, the summary repeater was malfunctioning.

Continue reading “A Summary Field Bug and Workaround”

Beverly Voth, Claris Platform, Claris Pro, Claris Studio, Level: Intermediate

Claris Studio (part 3) – Spreadsheet: Details, please!

About the author: Beverly Voth has been in the Claris FileMaker community many years. In addition to FileMaker Pro and its integrated products, she is a Full Stack Web developer & SQL database administrator. The only recipient of the FileMaker Excellence Award for Outstanding Contribution to the FileMaker Web Publishing Community (DevCon 2003), she’s been advocating Claris FileMaker and web since they could work together.

This is part three of a series on the new Claris platform. We introduced the overview, Claris Studio, Tell Me More! on 29 September 2022, and Claris Studio (part 2) – Integration with Claris Pro on 12 October 2022. This article gets into more details on the different types of views in Claris Studio. Since the spreadsheet view will likely be a most common usage for many, this article will be devoted to it. The spreadsheet view in Claris Studio has plenty of goodies & features, and can be used to create Dashboard views (charts and summary text).

A new spreadsheet view – with default fields, table name, & view name

Claris Studio, Spreadsheet View

Of all the views available in Claris Studio, the spreadsheet is a very flexible table for data entry & editing, sorting & grouping. These web-based data grids (tables) are more robust than FileMaker/Claris Pro table view, better than web viewer data tables (easier at least!), but probably not as full-featured as Excel or Numbers spreadsheets. There are no “cell” formulas or references as in some spreadsheets, but there is a way to present a grid of rows & columns for data entry, viewing, and reporting. Web-based data grids may use methods that allow drag-and-drop re-arrangement of rows and/or columns, as you will find in Claris Studio’s spreadsheet.

There are several JavaScript & CSS techniques & frameworks or libraries used for tables on the web. Many of them have been integrated into FileMaker Pro (and now Claris Pro) though Web Viewers.

But they all look like coding down and dirty. That’s great if that’s what you want to do. Would you like something easier? Would you like to send this data to the other Claris Studio view types, including dashboards (charting)? And would you like to share the data collected with Claris Pro? Let’s get started with the finer points of spreadsheets in Claris Studio! Continue reading “Claris Studio (part 3) – Spreadsheet: Details, please!”

Beverly Voth, Claris Platform, Claris Pro, Claris Studio, Level: Intermediate

Claris Studio (part 2) – Integration with Claris Pro

About the author: Beverly Voth has been in the Claris FileMaker community many years. In addition to FileMaker Pro & its integrated products, she is a Full Stack Web developer & SQL database administrator. The only recipient of the FileMaker Excellence Award for Outstanding Contribution to the FileMaker Web Publishing Community (DevCon 2003), she’s been advocating Claris FileMaker and web since they could work together.

Welcome back to part two of our Claris Studio & Claris Pro exploration. This article will cover Claris Pro and how it integrates with Claris Studio. Since we started with Claris Studio, Tell Me More!, there was a major update to the features in Claris Studio. You were advised to be alert! Check What’s New for the latest Claris Studio updates. This article is about conversion of Claris FileMaker Pro database files to Claris Pro files and how Claris Pro works with Claris Studio tables (two way interaction!)

What is Claris Pro?

Claris Pro is an application toolbox full of features that allow you to create user interfaces for data collection, display, and reporting. It can be used to create everything from a collection of Grandma’s recipes to full mission-critical business applications. It’s a cool and R.A.D. (pun intended!) toolbox for use by those starting out to full-stack relational database developers. Claris Pro uses a graphical interface for most creation, but easily works with external data exchanges. Once created with Claris Pro, these apps can be used on iPhones, iPads, or viewed in web browsers, as well as on the desktop/laptop by one user or thousands of users. You can use Claris Pro with Windows and macOS. Continue reading “Claris Studio (part 2) – Integration with Claris Pro”

Beverly Voth, Claris Platform, Claris Studio

Claris Studio, Tell Me More!

About the author: Beverly Voth has been in the Claris FileMaker community many years. In addition to FileMaker Pro & its integrated products, she is a Full Stack Web developer & SQL database administrator. The only recipient of the FileMaker Excellence Award for Outstanding Contribution to the FileMaker Web Publishing Community (DevCon 2003), she’s been advocating Claris FileMaker and web since they could work together.

This is the first in a series of articles exploring portions of the new Claris Platform. Launched in September 2022, this is just the beginning of the journey! Curious souls that we are, many question arise, and these articles are meant to supplement what we’ve seen so far, and perhaps give some different perspectives, along with plenty of screen shots. Concentration in these articles will be on Claris Studio and its relationship with Claris Pro.

Disclaimer: This platform may have rapid changes and this article may not be current after it is published. Always check for the latest information & videos.

What is Claris Studio?

Claris Studio is a cloud-based (access through a web browser) editor and data browser. A Claris ID is used to login and teams can be invited to join (by Claris ID). Access to view or edit can be constrained by inviting team members to Hubs with shared Views. The editor can create: Forms, Spreadsheets (tables), List-Detail Views, Dashboards (with charts and summary data), and Kanban Views. Some basic Views are preset for you to use as examples when you first start. There are many tooltip hints and popover & other dialogs to assist you along the way.

Continue reading “Claris Studio, Tell Me More!”

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

JSON Custom Functions for FM 19.5, part 2

Demo Files

Note: some of the CFs have been revised and/or renamed since part 1, so if you plan to use these CFs, make sure to download today’s “part 2” file.

Introduction

Welcome back to JSON Custom Functions for FM 19.5, round two. In part 1 we explored these CFs…

…and today we’ll be taking a look at the ones we didn’t cover last time. As before, a key ingredient in some of these CFs is JSON.GetValueType, which auto-determines the type of an element that is being added to an object or an array.

Today’s Custom Functions

Disclaimer: use at your own risk, these CFs may contain bugs, these CFs may not be performant when processing large amounts of data, always test carefully before deployment, etc., etc., etc.

JSON.FromTextArray ( array_2d ; colDelimiter ; rowDelimiter ; optionalKeyList )

Purpose: generates either a 2-dimensional JSON array or an array of JSON objects from a delimited 2-dimensional input array (for example, the response from a SQL query). The absence or presence of optionalKeyList determines whether you get back this…

[ [ value, value, value... ], [ value, value, value... ], etc ]

…or this…

[ { key:value, key:value, key:value... }, { key:value, key:value, key:value... }, etc ]

For example, assume the following result of a SQL query is sitting in a $$sql.array variable…

9035|New Receivable|1|010.003.001.001
9036|Review & Post Receivables|1|010.003.001.002
9037|Unpost Receivables|1|010.003.001.003
9038|New Direct Receipt|1|010.003.001.004
9039|Apply Receipts|1|010.003.001.005
9040|Review & Post Receipts|1|010.003.001.006
9041|Apply Credits & Prepayments|1|010.003.001.007
9042|Unpost Bank Deposits|1|010.003.001.008

What we get back from the custom function depends on the final argument.

Ex. 1 — the final argument is blank so the CF returns an array of arrays.

Ex. 2 — the final argument contains a list of key names so the CF returns an array of objects.

(If you’re curious about JSON.Format2D, that was discussed in part 1.)

JSON.MapJsonFromTextArray ( array_2d ; map ; colDelimiter ; rowDelimiter )

This one comes from Cristos Lianides-Chin, and is used with his kind permission. It came about after I showed him the preceding CF and he replied, “You know what would be a logical next step? To implement a map.”

Purpose: Given a 2d text array, such as would be returned from ExecuteSQL, maps each array row into the schema of a JSON object and returns a JSON array of objects.

Example — using the same $$sql.array variable as above, and a $$map variable populated like so…

…this statement…

JSONFormatElements ( 
    JSON.MapJsonFromTextArray ( $$sql.array ; $$map ; "|" ; ¶ ) 
)

…produces this.

JSON.DedupeArray ( array )

Purpose: removes duplicate entries from a JSON array.

Example — given this input…

…here is the corresponding output.

JSON.ObjectFromRecord ( tableOccurrenceName ; fieldToExclude )

Purpose: creates a correctly-typed JSON object for the specified record.

Sometimes you want to create an object representation of a given record. You don’t want to be bothered with specifying fields, you just want it to work with a minimum of thought and effort on your part.

Note 1: if you use this CF in a calculated field do not use Get ( LayoutTableName ) as the first argument… reason: when you look at the field across a relationship, you will get the json object for the parent, not the child.

Note 2: if you use this CF in a calculated field, to avoid a circular reference make sure to pass that field name in as fieldToExclude, otherwise pass in "" for the second argument.

IMPORTANT: unfortunately GetFieldName ( Self ) cannot be hard-coded into a custom function but we can pass it in as a parameter.

Ex. 1 — a calculated field in the customers table defined like so…

…will return something like this.

Ex. 2 — this similarly-defined calculated field in the sales table…

…returns this.

In the above example, since the CF doesn’t pick and choose, the result includes the output from two other “as_json” fields. If we wanted to, we could modify the definition of as_json_record like so…

…to get those other two out of there.

JSON.OptionalObjectFromFieldList ( objectName ; fieldList ; customNameList )

Purpose: creates a correctly typed JSON object based on field list, ignoring empty fields.

Ex. 1 — without custom names (assuming affiliation and birthPlace are empty).

Ex. 2 — as above, but with custom names.

Note: as per the above example, customNameList can be sparsely populated to allow a combination of custom and native field names.

JSON.CountElements ( json )

Purpose: returns count of non-null elements in a JSON object or array.

Note: Counts each entry, excluding nulls. If you want to count *all* entries (including nulls) use JSON.CountKeys instead

This CF came about when I needed to count non-null entries in a JSON object and some of those entries included embedded returns. The following examples reference this $$json variable which includes a two-line “address” entry.

Ex. 1 — the typical “count values” approach will count one value for “company” and two values for “address”.

Ex. 2 — the custom function disregards \r and \n so each element is only counted once.

JSON.CountKeys ( json )

Purpose: as above, but counts keys instead of elements.

Note: although rarely encountered, it is possible for object keys to contain “vertical white space” characters such as hard returns, line feeds, "\n", "\r", etc.

Assuming a variable, $$wonkyKeys, populated like so…

…resulting in a key list that looks like this:

Ex. 1 — the typical approach will count each key twice.

Ex. 2 — the custom function gets it right.

JSON.ListAllValues ( json )

Purpose: behaves like JSONListValues but returns empty rows for nulls.

Note: works with both objects and arrays.

Ex. 1 — FileMaker’s JSONListValues function swallows the nulls.

Ex. 2 — the CF renders nulls as empty rows, thereby preserving the position of subsequent items in the list.

JSON.KeyExists ( json ; keyOrIndexOrPath )

Purpose: returns a 1 if specified key is present, otherwise returns 0.

Ex. 1 — does key exist within an object?

Ex. 2 — does key exist within an array?

JSON.Deflate ( json )

Purpose: unformats formatted JSON, with no harm done if the JSON isn’t formatted to begin with.

Example: given this formatted array…

…the custom function returns this:

Note: you can achieve the same result by doing this (which, in fact, is what the CF does under the hood):

JSONGetElement ( $$small.array ; "" )

JSON.IsValid ( input )

Purpose: returns a 1 when input is valid JSON, otherwise returns 0.

Ex. 1 — input is JSON.

Ex. 2 — input isn’t JSON.

Interlude

The remaining custom functions are featured in today’s second demo file, JSON Insert and Update for FM 19.5.

Back in 2019 I did a pair of articles regarding certain limitations of JSONSetElement, which you can read here if you are so inclined.

In a nutshell, the “update” CF was created to allow a multi-dimensional auto-generated array to overwrite a non-empty (i.e., existing) array address. And the “insert” CF was created to allow one to insert an element into an array, shifting all downstream elements one position rightward to accommodate.

So why write new CFs for FM 19.5? Because the demo files and accompanying CFs worked fine when FM 18 was the current version, but when FM 19 was released something broke, and rather than fix old code, I decided to update the CFs to take advantage of JSONGetElementType, and combine both of them into a single demo file.

JSON.UpdateElement ( source ; address ; element )

Purpose: works like JSONSetElement but doesn’t balk when overwriting an existing element with an autogenerated array.

Ex. 1 — given this source array…

…we can push an auto-generated array into address [1] (which currently contains null) using JSONSetElement.

However, if we instead target [2] (which currently contains an object) with JSONSetElement, chaos ensues.

Ex. 2 — JSON.UpdateElement to the rescue.

JSON.InsertArrayElement ( source ; address ; element )

Purpose: inserts an element into an array, with any existing array elements sliding one position rightward to accommodate.

Ex. 1 — FileMaker enables us to overwrite an existing value via JSONSetElement…

…but does not provide us with a corresponding function to insert a value into an array so that existing elements shift one position rightward.

Ex. 2 — JSON.InsertArrayElement remedies this shortcoming.

JSON.GetArrayElementPosition ( source ; address )

This is a helper CF used by JSON.InsertArrayElement.

Purpose: translates a JSON array address into the corresponding FileMaker position value.

Note: this CF disregards JSON formatting (if any) and always returns the position you would get if the source were unformatted.

Ex. 1 — given this source json…

…we can derive the corresponding FileMaker position for address [1] like so…

…or, if we want to save a few keystrokes, since this is a single-dimension address, like so:

Ex. 2 — this time with a more complex source and address.

JSON.ValidAddressSegment ( source ; address )

This is a helper CF used by JSON.UpdateElement and JSON.InsertArrayElement.

Purpose: identifies the portion of a target address that already exists in the source JSON.

Example — given this source and address…

…the “valid” address segment (the portion that already exists) can be determined like so.

Conclusion

I use some of these CFs once in a while, and others on a daily basis. If you’ve made it this far chances are you’ll find uses for one or more of them in your own work, and if you find any bugs, or have suggestions for improvement, I’d appreciate it if you would let me know by posting a comment here.

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

JSON Custom Functions for FM 19.5, part 1

9/13/2022: Demo file has been updated to fix a bug in JSON.MergeArrays.

Demo File

Introduction

Today we have some custom functions (CFs) that can help you accomplish various JSON-related tasks in FileMaker. Back in 2018 I had this to say about JSON custom functions…

My inclination is to really understand something before I use a custom function to simplify things, but that’s a matter of personal choice… and one which can vary depending on the situation.

And four years later I find myself using JSON custom functions on a daily basis, to save time and to boost productivity — for example, to merge two objects into a single object, or to deduplicate an array. Continue reading “JSON Custom Functions for FM 19.5, part 1”