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.

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.

Background

During the otherwise unremarkable summer of 2020 I encountered two different challenges that lent themselves toward the invention of this technique. The first was how to move data from one table to another on a file that is stored locally on an iPhone. FileMaker Go does not support import/export to .fmp12 or XML, my two go-to formats for preserving data types.

The second challenge was how to consistently split a flat-file spreadsheet into related records, except that the spreadsheet could “grow” over time; ie the provider adds columns for each related record. In other words, the structure of the source file could change as time went on. Moreso, the user who would be performing the conversion has limited FileMaker experience; mapping the import using FileMaker’s Import Records dialog would be onerous.

FRAP-Map

FRAP-Map is a single file that acts as a bridge between your source and target tables, and the technique is designed so that you shouldn’t have to make changes to your source or target files.

The intent of this article is to provide a reasonably thorough explanation of how the components of this technique works. However, a working demonstration file with instructions can be found here (the-bridge-data-mapping-tool-v3.zip).

Core FileMaker Components

FRAP-Map uses merge fields, the GetLayoutObjectAttribute() function, the Set Field By Name script step, and the Evaluate() function. I’ll dive into each of these components individually and then describe how FRAP-Map puts them to work together.

Merge Fields

FRAP-Map relies on text objects that I call “data map objects”. I like to group target fields together in their own text object for legibility.

Each row in a data map object follows this pattern…

<<targetTable::targetField>> = value

…where value can be a merge field from the source table, a FileMaker function, or static text.

Merge fields are ideal for several reasons: they’re easy to embed in layouts because they use FileMaker’s Specify Field selector. Merge fields also survive if the table or field name changes. If a relationship is broken, a field is deleted, or a file goes missing, then broken merge fields become evident even in browse mode, making broken links relatively easy to spot.

For example, these are all valid possibilities:

<<targetTable::targetField>> = <<sourceTable::sourceField>>

<<targetTable::targetField>> = Get ( CurrentDate )

<<targetTable::targetField>> = Left ( <<sourceTable::sourceField>> ; 3 )

Data map objects must be named in order for the next component, GetLayoutObjectAttribute(), to work.

GetLayoutObjectAttribute()

GetLayoutObjectAttribute ( data map object name ; "source" ) returns the data map object exactly as it appears in Layout mode. FRAP-Map loops through each row in the object source for Set Field By Name to do its thing.

Set Field By Name

Since each row returned by GetLayoutObjectAttribute() follows the <<targetTable::targetField>> = value pattern, we can get the fully-qualified target field name from the left side of the equation and the target value from the right side of the equation.

Technically, the right side of the equation isn’t a value per se but a parameter that FRAP-Map passes to the Evaluate() function. For example, the first row of our example data map object would look like Set Field By Name [ map::NAME ; Evaluate ( SalesOrders::Region ) ] during script execution.

Evaluate()

Evaluate() lets us manipulate data as we migrate it. A common (simplified) example in our data map object would be…

<<targetTable::first_name>> = LeftWords ( <<sourceTable::full_name>> ; 1 )
<<targetTable::last_name>> = RightWords ( <<sourceTable::full_name>> ; 1 )

…which would split the first and last names from a single full name field.

Putting It Together

The Map table

The Map table provides the context for moving data from one table to another by acting as a universal join table between source and target tables. This table doesn’t need to store data in records; we use the venerable Magic Key technique to get and set the data we need. I recommend reading up on Magic Key first if you’re not already familiar with the technique. The upshot is that we will use fields with global storage to create and populate related records.

Connecting Source and Target Tables

FRAP-Map can be embedded in your own file, although you lose a degree of portability when you do this. The sample file is designed to work without needing to modify your source or target files. You only need to add external data sources to the FRAP-Map file.

Whether you build the Map table into your file or keep it separate, we join the source and target table occurrences in such a way that source tables can create records in the Map table, and so that the Map table can create records in the target tables.

The Layout

Since our data flows from the source table, through the Map table, and into the target tables, our main layout is set to show records from our source table.

And here it is in layout mode.

The Script

Here’s the main script at its simplest (no error capture):

# Get the list of instructions from the data map object
Set Variable [ $objects ; Value: GetLayoutObjectAttribute ( $mapObject ; "source" ) ]
# 
Set Variable [ $count ; Value: ValueCount ( $objects ) ] 
Set Variable [ $i ; Value: 0 ] 
# 
# Loop through each instruction
Loop
	Exit Loop If [ Let ( $i = $i + 1 ; $i > $count ) ] 
	# 
	# Store the instruction as ¶-delimited values in $object
	Set Variable [ $object ; Value: Substitute ( GetValue ( $objects ; $i ) ; [ "=" ; "¶" ] ; [ "<<" ; "" ] ; [ ">>" ; "" ] ) ] 
	# 
	# first (left) value is  the name of the target field
	Set Variable [ $fieldname ; Value: Trim ( GetValue ( $object ; 1 ) ) ] 
	# 
	# Set the value 
	Set Field By Name [ $fieldname ; Evaluate ( Trim ( GetValue ( $object ; 2 ) ) ) ] 
	# 
	Commit Records/Requests [ With dialog: Off ] 
	#  
End Loop

Conclusion

This article demonstrates how we can use long-standing FileMaker features and techniques to solve a problem in a novel way. The reader is invited to apply FRAP-Map to other scenarios such as synchronizing data between tables, comparing and merging duplicate records, or data warehouses. If you find this technique useful or have ideas for improvement and application, please let me know in the comments.

---

About the Author

Peter Doern lives and works in a lakeside farming and tourist community in central Ontario, Canada, with his family.

Peter’s career has spanned copywriting, graphic design, video game design, marketing, web design and FileMaker Pro development. He has been working with FileMaker Pro since 1992, founded The Bridge in 2000, and has been a Claris Partner since 2016. Peter has built and supported FileMaker Pro systems for talent and travel agencies, commercial kitchens, field technicians, and many other industries and clients.