FileMaker's import process has not changed in a long time and so it's ripe for an update. FileMaker, Inc. has shown it a lot of love in FileMaker 18!
FileMaker’s importing capabilities are pretty extensive but they're not always intuitive. The new import dialog interface features a mix of graphics and text so it should be very easy for both new and seasoned developers to choose the most appropriate actions. Let's explore the new import features and functions.
Before we dive in, let's review a few things in order to prepare for a seamless import process. Please note, these requirements have not changed at all in FileMaker 18.
- Verify the current layout is connected to the appropriate destination table through a Table Occurrence.
- The only exception is if you change the default Target from "Current Table" to "New Table." Since the goal is to create a new table, you're essentially creating a new Context as well.
- Found Set
- When selecting an import type of either Update or Replace, only your current found set of records will be affected so you need to either show all records or establish your found set before starting the import process.
- If you're importing from another FileMaker database and that database is open, you will need to control the found set in that database as only the found records will be imported.
There are no changes to file types that can be imported, but once you select a file to be imported the changes start!
New and Improved
The "Import Field Mapping" dialog is now named "Specify Import Order" and the first thing you're prompted to do is select the type of import action you want. The options used to be:
And now the dialog looks like this:
I’m glad they changed the original middle option of "Update existing records in found set" to "Replace" (with an explanation). In fact, adding explanations to each of the three options is a huge improvement. You don't have to select one of the options at this point. Once this selection popup goes away you can just click between the Source and Target (blue highlighted area in the screenshot) to reopen the popup.
Another major improvement – field names no longer need to be in the first row! When importing from Excel or other text-based source files you can now specify which row to use for the field names. I can’t tell you how often I've had to set up some post-processing after an import just to remove records created from extra header rows – but no longer! In this example, we've specified that row 2 stores the column headers that should be used as the field names. As before, once you select "Use as Field Names" there is the same option to auto-map the source to target fields based on matching names.
How you map the source to target values has been completely redesigned and may take some time for us seasoned developers to get used to. First, no more drag and drop to map the target fields to the respective source fields. As you can see in the screenshots, you can easily decide whether or not a field should be imported. Then you use a field list to map the target field. The target field selector now supports type-ahead so that you can quickly find or narrow the list of fields. This may feel more cumbersome than the old drag and drop feature, but when you're working with a large number of fields, it can be a life saver! Fields that have already been mapped will have a green arrow icon to the left of them.
Previously, other dialogs were presented after you clicked the "Import" button. Now those options are right in the main window so all the options can be set at one time. One of those dialogs asked how you wanted to handle auto-enter options. You had an all or nothing option to fire them or not and now you have that same option but also the ability to control it field by field. If any auto-enter options have been set on the target field you will see an orange gear icon to the right of it where you can control auto-enter options individually.
There are new "Import Options" settings. Depending on your import source, you will have different options available. In this example, the import source was a FileMaker database so you see additional options for working with container storage paths and repeating fields, along with another way to quickly set auto-enter options by field.
If the "Preserve external container storage" box is checked, FileMaker will just import the existing path to your external documents. This is a great addition to the import process if you frequently import data from offline/external files and you want to preserve the path once you host the file. The "Repeating Fields" option has also been moved out of a separate dialog to the main window - another nice update!
When importing text files, you have options to change the character set of the source file and the delimiter.
Even better, you can specify a custom delimiter.
The other nice thing about this feature is that this new interface is available on the desktop using FileMaker 18 Pro Advanced when manually importing records and also in the "Import Records" script step. Remember that when scripting for FileMaker Go and WebDirect to set your Compatibility correctly in your Script Workspace – this has not changed!
See the new import dialog interface in action.
Bonus: File Conversion
If you're not aware that you can just drop a file onto the FileMaker application and FileMaker will automatically create a new database, table, fields, etc., and import that data in one fell swoop, then you've been missing out on some great functionality! I’m happy to share that FileMaker 18 continues to support this feature and depending on the path you choose, you may have more options available.
In the past, if you dragged a .mer (a classic .csv that includes column headers) or .dbf file onto FileMaker, it only prompted you to name the file and select where to save it. For .xlsx, .csv and .tab files you would also be asked if the first row contained column headers (field names) or not. The table that is auto-created would be named the same as the file you dropped onto FileMaker. In FileMaker 18 when you drag any of those file types into the application, you will go through the new import workflow. Not only can you select which columns to import, but you can also change the name of the default table that is created so it can be different from the original name.
Incidentally, if you use the File Menu option (File Menu → Create New) and then select Convert, you will be prompted to select your source file and you will go through the old import process. So if you want to take advantage of the new, streamlined interface, drop your source file onto the application.
This article is also published on FileMakerProGurus.com.