BlogFileMaker

Importing Records in FileMaker 18

By May 22, 2019 May 30th, 2019 10 Comments

Import Records Command

Screenshot of Import Records

Figure 1 – Import Records

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:

Overall interface in FileMaker 17

Figure 2 – Overall interface in FIleMaker 17

You can see that FileMaker 18 introduces significant changes:

New import interface in FileMaker 18

Figure 3 – New interface in FileMaker 18

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:

Import Action interface in FileMaker 17

Figure 4 – Import Action in FileMaker 17

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.

New Import Actions interface in FileMaker 18

Figure 5 – Import Actions in FileMaker 18

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:

Data mapping UI in FileMaker 17

Figure 6 – Data mapping interface in FileMaker 17

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.

New data mapping UI in FileMaker 18

Figure 7 – New data mapping interface in FileMaker 18

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.

Popup menu is used to select target fields

Figure 8 – Popup menu to select target fields

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:

Records updated where source and target field match

Figure 9 – Records updated where source and target field match

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

Color and text show the source and target fields that match

Figure 10 – Color and text show the source and target fields that match

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:

Data mapping in FileMaker 17

Figure 11 – Data mapping in FileMaker 17

Example of data mapping in FileMaker 17

Figure 12 – Data mapping in FileMaker 17

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:

Incoming data is shown to the left of the data map

FIgure 13 – Incoming data is shown to the left of the data map

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.

However, the significant whitespace between the values and the field names creates an unfortunate separation. It requires some effort to track which data value belongs to which field.

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?

Import options

Figure 14 – Import options

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:

Auto-enter options for importing data

Figure 15 – Auto-enter options for importing data

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.

Auto-enter options

Figure 16 – Auto-enter options

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.

Dropdown menu for specifying the delimiter

Figure 17 – Specify the delimiter in the dropdown

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

Presenting source field names and source field data simultaneously frees up the mind to think about mapping rather than remembering. However, the distance between the source field names and data makes it harder to track them together.

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.

Mark Baum

Mark Baum

Mark is a Senior Application Developer based in Soliant's California office. Prior to joining Soliant, he was the Technical Marketing Evangelist at FileMaker, Inc., where he helped to produce beginner tutorials, FileMaker DevCon, and FileMaker Certification. Mark has 20 years of experience as a FileMaker developer in the areas of cloud integration (Jive, Marketo, and SalesForce), hospital systems, B2B telemarketing, and trade publishing.

10 Comments

  • Avatar Daniel Farnan says:

    Hi, Mark.

    This is a great overview of the changes – however I must point out that in Figure 9 you have inadvertently shown just how difficult the import mapping can be to work with in its historical form. Your text says that you’re matching on the NameFirst and NameLast fields but the graphic shows matching from First = NameLast and Last = AddressStreet.

    Here’s hoping that the new system prevents these sorts of errors that all of us have encountered. 🙂

    • Avatar Daniel Farnan says:

      And this comment system has stripped out the symbols I used to show the matching between the field names. It’s not obvious that such a process will be applied.

    • Mark Baum Mark Baum says:

      Hi Daniel, your comment made me laugh. I guess I’ll leave the screenshot as-is since it does in fact embody the challenges that we encountered in the past. Thanks for sharing! Mark

  • Avatar Dominick Capobianco says:

    Great article Mark. And ONLY once did I “overwrite a found set” AND the result — I paid dearly for it and will probably never use it again but I digress. . . ( lol )

  • Avatar Mark Scott says:

    Hi Mark,

    Great overview! One thing I’ll add is that the columns (Source Fields, Mapping, and Target Fields) are resizable. Therefore, you can overcome one of your quibbles by dragging the divider between “Source Fields” and “Mapping” to the left. That’ll get rid of that gulf between source field names and data.

    • Mark Baum Mark Baum says:

      Hi Mark, thank you for that information! It makes all the difference. I may edit this article to cut that complaint. I realize that there is a general interface expectation that dividers like that are moveable — but interestingly, only the one to the right of the “Source Fields” column is… and somehow it just wasn’t intuitive to me to even try it. Are you satisfied with it as-is, or how would you make that affordance more obvious?

  • Avatar Wayne C says:

    Thanks for this important review of the new features.
    The only complaint I have about the new format is the requirement to choose a delimiter field for my client users.
    Most of them are data entry people and do not know or need to know what type of delimiter is required.
    The script they currently use requires them only to choose the required file from a browse screen then the script performs the remaining steps. Do you know if there is a way to select a delimiter as default and not have the popup appear?

    • Mark Baum Mark Baum says:

      Hi Wayne, I may be misunderstanding you, but the delimiter choice is one of the options saved when you choose “With Dialog: Off”. In addition to determining the import order and so on, you can store which delimiter is used. You just need to have a sample text file to use when setting up the import appropriately. Does that answer your question, or are you looking for something else?

Leave a Reply