FileMaker as an ETL Solution

Posted on 2012-04-05
Last Modified: 2012-04-05
Specifically what i'm looking to do:

1. Check for the existence of a file(s)
2. if the files exist copy them to a directory (staging)
3. Import the files (they might be input by date, or may always have the same name, i haven't been given that directive specifically (still working on the requirements)
4. As in any ETL (or massage process, as you stated) i want to first to Empty all of the staging tables. With SSIS/Informatica i can simply issue a truncate table statement within the orchestration.
5. Import the files into the staging tables
6. Transform (massage) the data based on conditions say, 1. if the row exists update or delete the record. or 2. insert a new row
5. Move the data from those staging tables to production tables
6. move the files into an archive
Question by:basile
  • 2
  • 2
LVL 25

Expert Comment

by:Will Loving
ID: 37811667

1. Check for existence of files:

You can test for a files existence - assuming you know or can calculate the name of the file - using 1) some techniques described here:  that use native FileMaker capabilities, of 2) a plugin such as the Troi File Plugin. Because of your second item, I think you'll want to consider a plugin.

2. Copy Files

To copy/move/rename files via a script in FM you will need a third-party FileMaker plugin such as the Troi File Plugin. Because file manipulation operations are so common and easy to implement in a Plugin, many other plugins include file move/copy/rename commands in addition to whatever else they do. There are free plugins that do this but my experience with the free ones is that they are sometimes unstable and are not well supported over time such that you have to eventually replace them with something else. I've had a developer license to Troi File for 15 years and while I don't use it as much as I used to because of FileMaker's improved Import and Export options, I still do use it when I need to move, copy or rename files. It may be that you don't really need the "staging" step at all, just import directly from the source directory and then delete or archive.

3. Importing files

File imported can be scripted to use a calculated or hard coded name. The example you gave before had the file name the same each time except for the date in the name. A calculation can determine the file name for a particular date. Alternately, if you know the source directory, you can get a list of all files in the directory and then import each one.

4. Emptying tables

A scripted function that goes to a layout for each table, performs a "Show All Records" and then "Delete All Records" should take care of this.

5. Import to staging tables

Again, a scripted import can import the contents of the files into the appropriate staging table. If you are working with flat files that contains both parent and child data, you can import different sets of information into different tables, it's just a matter of setting it up. FileMaker supports Match by Field name where it looks at the first row of the import data and attempts to match columns to field names.

5. Transform data

Using script commands and perhaps other tables beyond the staging tables you should be able to manipulate your data as required. In addition to the import fields, your staging tables might also have calculation or summary fields, including calculations that reference or compare staged data to production data, and  that produces values based on the imported data that you can then, query or do further manipulation on.

7. Moving data

Depending on what your goals is, you may simply be able to run looped scripts that directly updates related records in the production data, OR, you may want to Import with Matching into your production data. You might even have steps that delete irrelevant records in the staged data before Import into Production.

8. Archive Files

Not sure which files you are wanting to archive, but I'm assuming it's the original files that were imported. Just use the Plugins "Move File" function to put them wherever you want.

Author Comment

ID: 37812084
This all sounds great, i'll check the plug in. Now, i guess there isn't a corresponding orchestration tool, so my last part of this, is how do i put it all together, in a "Job" sorta speak ? Does filemaker have the ability to execute outside executable file (for a lack of a better word, i'm really not familiar with how macs do things, in a windows/sql world, you could schedule a sql job, that can call the command line utility to accomplish alot of this, or create a .net program that can be executed from SQL). Thanks!
LVL 25

Accepted Solution

Will Loving earned 500 total points
ID: 37812214
Just so you know, while FileMaker, Inc. IS a wholly owned subsidiary of Apple - a fact which many people don't know - FileMaker has also been available on Windows since 1993 and a majority of FileMaker users are on Windows.

The way you "put it all together" in FileMaker is to create a "script" - essentially a macro or series of steps/operations to execute - and then either trigger that script manually, set it to trigger under certain circumstances, or schedule it to run at regular intervals from FM Server. Almost anything you can do manually in FileMaker can be scripted and you can include conditional statements, temporary variables ($) and persistent variables ($$). If I were to go about what you are describing above I would create a script that would work through each part of the sequence and I would test it manually. The example file I included for your previous question contains a script in it with notes on what I did where and why.

If after you have the sequence fully working with a manually launch, you can then look at setting it to run automatically as a server-side script. When running scripts on the server, certain script steps such as "Save as PDF" are not supported and there are certain restrictions such as a limited number of location where files can be imported from, but assuming those are taken into account, you should be able to automate it.

Sounds like an interesting project and you're likely to post more questions and learn a lot about how FM works, but it sounds quite doable.

Author Closing Comment

ID: 37812319
Well, thanks again for all your help with this project. You are definately sending me in the right direction! Cheers.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question