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: 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

New in FM 18: While, part 2

Today we’re going to dig a little deeper into the new-in-18 While function, and to avoid repetition, will assume readers are familiar with last month’s article on SetRecursion and While. We’ll look at some new examples ranging from basic to advanced, investigate the circumstances under which While can use variables previously declared via Let, and check out some benchmark results comparing While vs. CustomList.

Demo Files

Continue reading “New in FM 18: While, part 2”

ExecuteSQL, Level: Intermediate, SQL, Virtual List

Bill of Materials

29 Mar 2019: Demo file has been updated to v2. See comments for details.

Editor’s note: Geoff Gerhard of Creative Solutions is one of those “quiet” developers who has been helpfully sharing his knowledge in online FileMaker forums for more than 20 years. I personally have benefited enormously from his expertise, and am pleased to feature his first appearance as a guest author here on FileMaker Hacks.

Demo file: BillOfMaterialsDemo-v2.zip

Continue reading “Bill of Materials”

Level: Advanced, Version: FM 13 or later, Virtual List

Virtual List Reporting, part 1

Self-plagiarism alert: to avoid repeatedly referring the reader back to earlier virtual list articles, portions of text from those earlier articles are incorporated here.

Welcome to the first installment of a multi-part series on producing reports using the virtual list technique, or more properly, collection of techniques. Demo file: VLR-part-1.zip

demo

Invented and popularized by Bruce Robertson, virtual lists are incredibly flexible, and have made a number of appearances here in the past, including… Continue reading “Virtual List Reporting, part 1”

Chart, Level: Intermediate

Blurring the Distinction between Schema and Data, part 2

Welcome back for part 2 of my recap of the session I presented last week at the 2014 Portland PauseOnError un-conference.

Recreating ScriptMaker

A recurring theme, as we saw in part 1 and will see again today here in part 2, is that you can store “runtime code” as data. The most extreme example I’ve encountered is a proof of concept created and presented by Dr. Ray Cologon at DevCon 2006, the Text Script Interpreter, a.k.a. TSI.

11-1-2014 3-44-59 PM

The idea is that the entirety of FileMaker scripting can (not should, but can) be represented and interpreted textually, either as records in a table…

11-1-2014 4-12-02 PM Continue reading “Blurring the Distinction between Schema and Data, part 2”

Level: Advanced

Blurring the Distinction between Schema and Data, part 1

The other day I had the privilege and the pleasure to give a POE presentation entitled Runtime Code, a.k.a. Blurring the Distinction between Schema and Data, in the room behind this window at the Ace Hotel in Portland, Oregon.

poe-600

The overall goal of the presentation was to explore various ways one might move business or presentation logic out of its normal location in the schema layer, and into either a) the data layer, or b) some other, non-standard, schematic realm. An example of the former might be to store object names or calculation syntax as data in a table; an example of the latter might be to change the behavior of an object (e.g., a field or script) simply by renaming the object itself. Continue reading “Blurring the Distinction between Schema and Data, part 1”

Level: Advanced, Version: FM 12 or later

Radical Separation, part 4

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

FYI: In March at the Portland PauseOnError un-conference, Matt Navarre and I had a freewheeling Separation Model discussion, a podcast of which has just been posted as episode 85 at FileMaker Talk.

Welcome to the fourth and final installment in our series on Radical Separation. Today’s article assumes familiarity with part 1, part 2 & part 3, and continues in the direction we were headed at the end of part 3. Specifically, we will finish up our exploration of “virtual calculations” by examing an intriguing approach suggested by Barry Isakson to a) reduce the field count, b) solve the “define more fields than you’ll ever need” problem, and c) accommodate summary fields, and I invite you to follow along in today’s demo file, Virtual Calculations, Part 4, if you are so inclined.

6-9-2013 4-10-10 PM

Continue reading “Radical Separation, part 4”

Level: Advanced, Version: FM 12 or later

Radical Separation, part 3

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

Today we’re going to delve further into the concept of virtual calculations, picking up where we left off last time, and with the assumption that readers are familiar with the material in part 1 and part 2 . We’ll look at some ways to make this technique less brittle (prone to breakage if objects are renamed), and also less opaque to DDR analysis tools such as BaseElements and Inspector. We’ll also see if the technique can be applied to auto-enter calc fields, and finally, we’ll explore some ways to make the technique easier to implement.

5-10-2013 10-41-51 AM

Demo Files:

Continue reading “Radical Separation, part 3”

Level: Advanced, Version: FM 12 or later

Radical Separation, part 2

Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.

In part 1 of this series, we defined radical separation as a separation model scenario in which the developer no longer has access to a data file once a solution had been deployed. Updates to the solution are delivered in the standard separation model manner: by swapping in a new interface file.

We explored the concept of “virtual calculations”, where certain (unstored) calculated fields in a data file derive their definitions from syntax stored as data in a special table in the interface file. The advantage of this being that calculation logic can be redefined programatically by the simple expedient of replacing the interface file.

5-2-2013 9-09 PM

In the six weeks that have gone by since I posted part 1, I have made a couple improvements to the technique, one of which which we’ll examine in today’s demo file: Virtual Calcs, Part 2

Continue reading “Radical Separation, part 2”