Importing data can be easy when doing it table after table, with well structured data; but what about importing a “flat file”, where data destined to be put in different tables are mixed?

Let’s take a simple FileMaker database, where companies are linked to multiple contacts.

Here is a simple “company” record, linked to a contact in a portal

In order to have this kind of layouts, the relationship between the companies and the contacts is set up so that the foreign key IdCompany in the Contacts table is linked to the primary key IdCompany in the Companies table.

Relationship between companies and contacts in the FileMaker graph

We now need to import in this database an Excel file structured like the following one:

1 column with data from the company, the 2 following ones with data from the contact

In this file, data from the companies AND from the contacts have been written on the same lines; however, we need to separate these data in our relational database.It could still be possible to import the file table by table; but in that case, it would also create duplicate companies (when the same company is linked to different contacts), and the link between the companies and the contacts would be lost.A solution to that would be to create a new table then, only used for the import, that would have the exact same fields as in the original Excel flat file. A simple import (File > Import Records > File… ) can then be used.

Normal import process

We can now create a new point of view in our relationships graph, using an occurrence based on this import table.As we plan to create companies and contacts records from this point of view, we will create a new global text field in the current “Import” table (that we will call “GlobalUniqueText” here); this field will be reset each time that we will handle a new record. On the other end, in both the Company and Contact tables, we will create a “Creation” field; this field will only be used for the creation by link. The links between the Import table and the 2 tables where records will be created have to be set between the GlobalUniqueText field and the Creation field, and the creation by link has to be checked.For each record, a new random, unique UUID value will be set into the GlobalUniqueText field; by doing this, the link between the Import table and the 2 other tables will be reset. When values will be set in the 2 other tables, the records will then automatically be created, thanks to the checked option to create records by link.

left : The new “import” point of view | right : “Allow creation of records in this table via this relationship”

The script used to create data from the Import table in the 2 other tables can now be created. The detail of this process is described in the comments of the script:

Script used to distribute data from the “flat file” import table into the tables used in the database

In the end, the companies and contacts records are created, and the link between these new records is conserved.

This method can be applied to a greater scale, with a lot of different columns going to a lot of different tables in you FileMaker database, but the principle stays the same: create your temporary import table that matches to your Excel flat file, and re-process, adapt and distribute your data, field by field, in your own tables.Leave a comment below if you have any question!

Written by Vincent Miano.