Jump to content
Sign in to follow this  
  • entries
    14
  • comments
    0
  • views
    339

Importing data in a FileMaker file from a “flat file”

Sign in to follow this  
Marina Syhanath

192 views

Importing data in a FileMaker database from a “flat file”

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?

1*cDsdTQXB5DSfchpt93XHig.png

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

1*ARi20zMmTdz_hNbalyGMqw.pngHere 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.

1*QjP-vUq0iorCXEgSRsPXtw.pngRelationship 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*UNNzwJf2trtw7KeWEWWogQ.png1 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.

1*L-HxFyazrA831It2ewHcPg.png1*qsu5dyJ3kb4dvb4dJ09njw.pngNormal 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.

1*wciw-wATPqU7rlMnyDmRpQ.png1*cVddTb6sI2RFxnvRFDKw_Q.pngleft : 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:

1*FZB9xvKJl_RpH64h0SiNvQ.pngScript 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.

1*kkDMvpRO46CtPWO9zKJhKQ.png

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!

1*QeBgI9y4y928CDq-FADJ-w.pngstat?event=post.clientViewed&referrerSource=full_rss&postId=63a08460cc70

Importing data in a FileMaker file from a “flat file” was originally published in Lesterius on Medium, where people are continuing the conversation by highlighting and responding to this story.


Afficher la totalité du billet

Sign in to follow this  


0 Comments


Recommended Comments

There are no comments to display.

Guest
Add a comment...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...