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

A Value List Experiment

Kevin Frank

Demo file: a-value-list-experiment.zip

Note 1: today’s demo is recycled from the one that accompanied Virtual Portal, part 2. All we care about this time around is how we assign the parent company; the remainder can be ignored.

Note 2: if you’re unclear on value list basics, check out Thinking About Value Lists, part 1.

“I want it to work like it does in Quickbooks.” Based on a client request I recently implemented a Quickbooks-like picker… shown here enabling the user to easily assign a parent company via auto-complete as the user types the first few letters of the company name.

To be clear, we aren’t actually storing the parent company name, merely using it as a selection mechanism. When a name is chosen via this interface, the corresponding foreign key is immediately auto-entered into “id_parent”… Continue reading “A Value List Experiment” →

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

Render Found Set as JSON Object

Kevin Frank

Demo file: found-set-to-json-object.zip (40MB; requires FM 18 or later)

[Author’s note: the demo file is a work in progress. Modify it as you see fit.]

Today we’re going to compare various methods one might consider employing to render an arbitrary found set as a JSON object. The demo consists of a table of US customers with 250K records, and the JSON object will be structured like so…

{ 
   State Name: 
      { County : [ [ customer array ] , [ customer array ] ... ] , 
        County : [ [ customer array ] , [ customer array ] ... ] , 
        ...
      } , 
   State Name:  
      { County : [ [ customer array ] , [ customer array ] ... ] , 
        County : [ [ customer array ] , [ customer array ] ... ] , 
        ...
      } , 
   ...
}

…i.e., grouped by state name and county, with customer data represented as a two-dimensional array, for example: Continue reading “Render Found Set as JSON Object” →

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

JSON Currency Exchange Rates

Kevin Frank

Update 11 Apr 2021: as per comments below, demo has been updated to use a new service: openrates.io.

Welcome back for another exciting round of JSON exploration. Last time we discussed a JSON-related bug fix in FM 19, as well as a free package tracking service that returns results in JSON format. Today we’re going to look at a free currency exchange rate service.

Demo file: json-exchange-rates-via-openrates.zip

2020-08-24_09-12-59 Continue reading “JSON Currency Exchange Rates” →

JSON, Level: Intermediate

A JSON Miscellany

Kevin Frank

Today we’re going to look at a bug fix in FM 19, and a free web service that returns useful information in JSON format.

Fixed in 19: JSON validation bug

In FM 18 (but not in 16 or 17) JSONFormatElements and other JSON functions can mask mangled JSON errors, making them harder to detect and correct. I wrote about this in detail last year, but, briefly, a JSON bug that was introduced in FM 18 has been corrected in FM 19.

Steps to replicate:

Create some JSON, and then transform it, but intentionally botch the transformation, for example, attempt to update an array using object notation:

2020-07-05_125525 Continue reading “A JSON Miscellany” →

Level: Any, Version: FM 16 or later

FilterValues, part 2

Kevin Frank

Sometimes it happens that the real value of a blog posting emerges in the comments section. Such was the case last time, when Paul Jansen posted an elegant little workaround to remedy a performance issue with the FilterValues function. Paul’s workaround is deserving of its own demo, and that’s what we’re going to look at today.

Demo file: filtering-values-v2.zip

2020-06-02_230953

Continue reading “FilterValues, part 2” →

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

Ordering Elements within JSON Objects

Kevin Frank

Introduction

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…

2020-04-29_165223 Continue reading “Ordering Elements within JSON Objects” →

ExecuteSQL, Level: Intermediate, SQL, Version: FM 10 or later

GetFieldName Revisited

Kevin Frank

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

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

Virtual Portal, part 2

Kevin Frank

[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…

2019-12-26_154835

…via an array like this…

2020-02-16_18-29-04 Continue reading “Virtual Portal, part 2” →