Importing Records in FileMaker 18

Import Records Command

The Import Records command provides one of the most fundamental integration features of the FileMaker Platform. It allows you to import data into your FileMaker solution from other data sources such as text files, Excel files, and other FileMaker databases. Without this feature, you would likely have to perform a great deal of data entry when deploying a solution that replaces existing business processes.

In FileMaker 18, a number of changes make the Import Records command significantly easier to use and more flexible. I’ve chosen the following features to discuss in this article:

• Import actions: Your import can take three distinct approaches: adding a new record for each one imported, simply overwriting the records in your found set with what you are importing, or updating records more strategically based on matching criteria that you provide.
• Data mapping visualization: The data mapping interface helps you to visualize the process of pairing together source fields (the ones you are importing) with target fields (the ones that will receive the incoming data).
• Display of incoming data: Seeing the actual data that you are importing helps you to decide which fields to pair together in your data map. The field names alone are often not enough, and in some cases, you don’t even have field names to work with.
• Auto-enter settings for target fields: Your target fields may have been set up to auto-populate according to various rules. You have the option of skipping this auto-population to preserve your original data.
• Field delimiters: When you are importing a FileMaker file or Excel file, it’s easy for the import process to recognize each of the fields being imported because they are part of the file structure. However, if you are importing a text file, special characters such as tabs or commas are used to separate, or “delimit”, the fields. In order to perform text-based imports, FileMaker Pro needs to know what delimiter is being used.

I’ll describe how each of the above features has changed between FileMaker 17 and FileMaker 18 and discuss the advantages (and a few disadvantages) that result from this.

The Overall Interface

Here’s the interface in FileMaker 17:

You can see that FileMaker 18 introduces significant changes:

Import Actions

Before starting on your data map, you need to decide which import action to perform. Do you want to add records, update records, or overwrite your found set? (By the way, I have never chosen to overwrite a found set. Just the idea of it makes me squirm a little, but perhaps I’m missing out on something special.)

In FileMaker 17, the import action choices appear in the lower left of the window, which is not a location that gets your immediate attention. Since this is likely the first choice you will make, it ought to be more prominent. FileMaker 18 improves on the situation by placing the Import Action at the top of the screen above the actual data map. This communicates the order of actions more clearly.

However, in FileMaker 17, it’s much clearer that you have something to do. You see a box labeled “Import Action” with three radio button choices:

In contrast, the FileMaker 18 interface doesn’t communicate that you have a choice to make; you simply see the blue label “Add” with an arrow below it. Unless you have geeky psychic powers, you have to learn somehow that this is actually a button that causes a popover to appear. I didn’t find it obvious myself, and even after using it a number of times, it still takes me a moment to remember that it’s hidden there.

That said, what I do like about the new popover is that it describes the three actions in greater detail and with helpful illustrations. If you are still learning the differences among them, you’ll likely find this information useful.

Data Mapping Visualization

Now that you’ve chosen your import action, you’re ready to start the process of data mapping, where you pair incoming fields from your source table with the existing fields that they will populate in your target table. This often-straightforward process becomes more challenging when you have many fields to pair or aren’t sure how to pair up your fields. To make things easier, FileMaker Pro provides a drag and drop interface that helps you to visualize the process.

Data Mapping Visualization in FileMaker 17

The FileMaker 17 version of the data mapping interface shows all the source fields on the left (with names, when available) and all the target fields on the right, as in Figure 6:

On principle, the task is simple, since you see every matching possibility right there in front of you. A handle appears to the left of each of the target fields. You simply use your mouse to grab that handle and drag each target field until it lines up with the appropriate source field. You continue pairing fields together until the data map meets your requirements.

A little arrow connects each source field to its target counterpart, indicating the flow of data. If you don’t want to import a given source field, you click this arrow to make it disappear. If you change your mind, you can click it again to restore it.

As it turns out, in practice the process can be challenging. As you drag each target field to a new position, it takes the place of the field that was already in that position. When there are many fields, this starts to feel like those puzzles where you slide tiles around to create a picture. The order of the fields becomes quite haphazard to the extent that it can become very difficult to locate a given field that you wish to map. If you don’t know which field you want to map, the interface makes it very difficult to assess your options. Finally, the actual task of dragging and dropping doesn’t work well on large tables: it’s hard to scroll long distances to the location where you want to drop the field in question, and you end up needing to drop it several times along the way just to get there.

Another challenge involves source fields that you’ve chosen not to import. This interface still requires you to make a match for every source field, even if you don’t choose to import it (you can see an example of this in the screenshot where Title is matched to Modified By, but there is no arrow between them). These non-matches have the same visual weight as the matches, causing them to compete for your attention.

Finally, there is minimal visual feedback associated with the action of sliding the fields, which makes it hard for the eye to track whether you dragged the field to the right place. This, combined with the fact that it’s difficult to drag them precisely, means that creating a data map for many fields can require a great deal of patience.

Data Mapping Visualization in FileMaker 18

The new data mapping interface doesn’t involve sliding at all. On the left-hand side of the interface, you still see a comprehensive list of all columns coming from the data source. If the data source contains many fields, this list will still be quite long.

The word “Import” clearly indicates which source fields are being imported. The green color gives further emphasis to the pairings that are being imported, and the black color of the target field name extends the emphasis further. All of this makes the map much easier to read.

Furthermore, the entire list of target table fields no longer appears on the right, occupying the user’s attention. Instead, only actual matches to each source field are shown. The target fields are selected from pop-up menus as shown in Figure 8.

If there are many target fields to choose among, the list is scrollable, or you can search progressively on the field names.

Those fields that haven’t yet been mapped are grouped at the top of the list, then sorted alphabetically, while those that have been mapped are grouped at the bottom, also sorted alphabetically. These changes make it much more convenient to locate a specific field by name.

Matching Options

If you have chosen to update records based on matching criteria, your data map gets a little more sophisticated, as you need a way to designate those matches.

In FileMaker Pro 17, matching criteria are indicated by a two-way arrow, which looks almost the same as the one-way arrow (and both of those are similar to the no-arrow option as well). There is no other designation (color, weight, etc.) to help you see the difference between the three possibilities.

The action to select the matching criteria is simple enough. Instead of a toggle between the right-arrow and the no-arrow, each click cycles you through the three options (right-arrow, two-way-arrow, no arrow). However, it’s awfully easy to make an extra click and get a different arrow than you intended.

In this example, the import process would update (that is, overwrite) records where data in the First and Last fields of a source record match the data in the NameFirst and NameLast fields of a target record:

FileMaker Pro 18 distinguishes things much more clearly using both words and colors:

You can see at a glance which pairings are used for matching, which will cause data updates, and which will be ignored.

Display of Incoming Data

It’s important to see examples of incoming data as you make choices about your data map, especially when your source data doesn’t include field names.

In FileMaker 17, you view the incoming data in the same place where the field names appear. In the screenshots below, you see the field names first, then the source data:

An advantage of this approach is that you see the data values closely paired with their target fields. No effort is needed to associate them with one another.

However, you have to remember the source field names while you are viewing the source field data. That can require a significant cognitive effort. Typically it means you end up thinking about just a few pairings at a time unless the contents of the field remind you which field it is.

In FileMaker 18, the incoming data is presented independently, off to the left of the data map, but with the values in alignment with their fields in question:

It’s so much more relaxing to see both the field names and data at the same time. There’s no effort to remember or infer which fields are being mapped.

When I first published this post, I thought you couldn’t reduce the whitespace between the values and the field names. I learned from Mark Scott (see comment below) that the divider to the right of the “Source Fields” column can be moved, reducing this whitespace. Only that one divider can be moved and somehow it wasn’t intuitive for me to try it, even though this is basic expectation from Excel and other spreadsheet-like interfaces.

Choose your header row

Here’s something else I missed when I first published this post. It’s also due to the interface not quite making sense to me at first glance. As part of reviewing the incoming data, you can choose any row as a header row by selecting “Use as field names” from the dropdown just above where the data is displayed. I assumed that this option only applied to the first row. Regardless of the row you choose, the import starts with the rows below it, which means that all rows above it are skipped. I expect to find this handy when I want to work with formatted reports where the initial lines are used for titles, comments, and other information, all of which FileMaker 17 requires you to delete before you can attempt an import.

Auto-enter Settings for Target Fields

If your table includes auto-enter options that insert values (such as the current date) or that perform calculations (such as stripping surrounding whitespace from a person’s name), you will need to decide whether to apply these when importing data.

FileMaker 17 asks you about this almost as an afterthought. Once you finalize your data map and are ready to perform your import, a second dialog box appears, asking the following:

You are given a single decision that applies to all the fields in the target table: do you want to perform auto-enter options or not?

FileMaker 18 improves on this in two ways. First, the choice is presented in the lower right of the main dialog window, as one of your final decisions before performing the import:

Second, you can make the decision at the field level. For example, you might want to enable auto-enter on system fields that are not mapped, (such as primary key or creation timestamp), but leave the data alone in fields that are mapped (such as NameFirst and NameLast).

You can also make the same auto-enter choices field-by-field on the map itself by clicking the gear on the right of the target field. The gear turns orange when auto-enter options are being performed, and gray when they are not.

Field Delimiters

Importing character-delimited text files is a standard practice in database administration. People use a variety of delimiters: tabs, commas, semicolons, spaces, vertical lines — whatever works best for the data in question. Typically you choose a given delimiter because it doesn’t appear in the actual data and hence won’t interfere with the parsing.

In FileMaker 17, you have two basic options: tab-delimited and comma-delimited. The software decides what delimiter to use based on the file extension of the source file. For example, a “.csv” file uses commas, and a “.tab” file uses tabs. But what about a “.txt” file? In that case, FileMaker makes its best guess and is usually right.

But what if you want to import a semicolon-delimited file? Either you have to substitute commas or tabs for the semicolons before importing, or else import the entire file into a field and then script a process to parse the data.

FileMaker 18 addresses this issue head-on. You can now specify any delimiter that you like. You have a dropdown choice of four common options (tab, comma, semicolon, space), and also the option of entering any other single character.

In the future, I hope we will have the option of entering multiple characters. A combination of characters such as tilde-pipe-tilde ( “~|~” ) is especially unlikely to appear in your text data. It would be great to be able to specify the characters that separate rows of data from one another as well.

The Verdict

Here’s my final assessment of the various changes I’ve discussed. I hope you’ve found this information useful!

Import Actions: MIXED

Actions are more prominently displayed, and in a more workflow-appropriate location. However, they are hard to discover. Clearer descriptions and illustrations make them easier to understand.

Data Mapping Visualization: WIN

Using dropdown menus of target fields rather than a mix-and-match, drag-and-drop approach solves many of the worst problems posed by the FileMaker 17 interface. The interface presents less cognitive load through the strategic use of color and text labels. Dropdowns sort target fields to present unmapped fields first, making fields easier to locate. A search filter by field name assists with this as well.

Display of Incoming Data: WIN

Presenting source field names and source field data simultaneously frees up the mind to think about mapping rather than remembering. The whitespace between them can be reduced (hooray) though this option wasn’t obvious to me.

Choose Your Header Row: WIN

The option of choosing any row from your incoming data as the header – and skipping all rows before it – will simplify the task of importing more complex spreadsheets. The interface didn’t make it immediately obvious to me that this was an option, however.

Auto-enter Settings for Target Fields: WIN

Auto-enter options are now incorporated into the main interface rather than as an afterthought in an intrusive dialog. Enabling auto-enter options on a field-by-field basis allows for greater precision in determining how “purely” an import should be performed.

Field delimiters: WIN

Custom field delimiters greatly widen the options for text data sources and do away with programmatic workarounds.

What do you think?

How do you feel about these new options? Share your thoughts in a comment below.