Level: Advanced

FRAP-Map: A Flexible, Robust, Accessible and Portable Data Mapping Technique

Editor’s note: today we have a guest article by long-time FileMaker developer, Peter Doern, who has come up with an elegant, outside-the-box methodology for managing imports. This is one of the coolest techniques I’ve seen in a long time. Enjoy.

FRAP-Map Overview
Flat data goes in; related data goes out.

FileMaker includes a powerful and fast method for importing data from a source table to a target table. FileMaker 18 introduced an overhauled file import dialog which allows us to easily specify source data character set and delimiter, target fields, and auto-enter options on import.

But what if you want to pull a source field into multiple target fields, manipulate data during import, or split a flat source table into multiple related records? What if you want to import data from multiple different sources with different structures into a single, consistent, file? What if the structure of the source file changes frequently?

Using the traditional FileMaker import method, any of these scenarios will require multiple imports using hard-coded import script steps, or a solid understanding of XML and XSLT transformations. This Flexible, Robust, Accessible and Portal Mapping technique, aka FRAP-Map, provides a powerful alternative.

Continue reading “FRAP-Map: A Flexible, Robust, Accessible and Portable Data Mapping Technique”

Level: Intermediate

When 2 is greater than 10

Today we’re going to take a quick look at a potential “gotcha” that can occur when performing numeric comparisons. Jason DeLooze and I discussed the general behavior back in 2011 (Space Is The Place), but this time around I want to focus on one particular set of circumstances where the problem can arise.

At first glance this may appear to apply to beginner-level developers only, but the problem is nuanced, and I have occasionally seen it crop up in solutions written by experienced developers as well as those created by newbies.

Specifically, when comparing two variables, for example…

ExitLoopIf [ $counter >= $rows ]

…FileMaker must determine whether to do a text comparison or a number comparison, and under certain circumstances, contrary to developer expectations, may perform the comparison in “text space” rather than in “number space”.

Of course we can explicitly ensure that all comparisons take place in “number space” like so…

ExitLoopIf [ GetAsNumber ( $counter ) >= GetAsNumber ( $rows ) ]

…but let’s dig a little deeper to see the circumstances under which GetAsNumber is actually necessary. Continue reading “When 2 is greater than 10”

ExecuteSQL, Level: Advanced, SQL, Version: FM 18 or later

Fun with SQL Joins and Currency Formatting

Demo file: sql-join-fun-etc.zip requires FM 18 or later.

Recently I was asked to create a mini-report combining data from a pair of related tables via ExecuteSQL.

This provided an opportunity to think about SQL joins, and also to come up with a way to apply currency formatting to dollar amounts in the SQL query result, given that FileMaker’s SQL implementation does not support the standard SQL way of accomplishing this (e.g., CAST AS NUMERIC or CAST AS DECIMAL) . Continue reading “Fun with SQL Joins and Currency Formatting”

ExecuteSQL, Level: Advanced, SQL, Version: FM 18 or later

File IDs, Table IDs and Field-Level Schema

21 Jan 2021: demo has been updated to fix a couple bugs; article has been updated to reflect these changes.

Demo file:  file ids, table ids and field schema, v4.zip (requires FM 18 or later)

INTRODUCTION

Recently I was asked to help document a FileMaker solution by creating and populating a “schema” table as per the above screen shot.

One of the requirements was to programmatically determine internal ID numbers for the file and for the tables (as opposed to table occurrences) within that file, and this was accomplished by leveraging some script steps that were introduced in FileMaker 18.

(Thank you Rob Russell for pointing me in the right direction, i.e., to Save A Copy As XML.)

RUNNING THE DEMO

Clicking this button…

Continue reading “File IDs, Table IDs and Field-Level Schema”

Level: Advanced, Version: FM 16 or later

Set Variable By Name Revisited

Demo file: set-variable-by-name-v3.zip (requires FM 16 or later)

[Demo has been upgraded to v3 to fix a bug in the cfZap custom function].

INTRODUCTION

You’ve probably heard the old joke that goes…

Patient: Doc, it hurts when I do this.

Doctor: Don’t do that.

Perhaps you’re also familiar with the FileMaker equivalent?

Developer: It hurts when I try to assign certain variable names.

FileMaker: Don’t do that.

Continue reading “Set Variable By Name Revisited”

Level: Intermediate, Version: FM 18 or later

A Value List Experiment

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

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

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

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”