Using ExecuteSQL to Clean up Imports in FileMaker

FileMaker developers are often tasked with cleaning up data, not just building databases. That said, we sometimes must reconcile how a user once tracked data in an Excel spreadsheet with a database’s ability to link data together.

So let’s talk data clean up using the example of Projects and Locations. Imagine having an Excel spreadsheet that tracks a list of projects. Each project takes place at a given site or location. Also, a given location can have multiple projects, making it a one-to-many relationship. In Excel, you don’t use a number to represent a location; you just use abbreviation making it easy to identify the location. In a FileMaker database, we would have a primary key in the Location table and a foreign key in the Project table to identify the location.

When I import the Excel spreadsheet, I don’t have that foreign key. I justhae  the short name of the location. In the past, I would put the short name in a temporary field and create a relationship to the Location table based on name (not keys) Then I would use Replace Field Contents to set the primary key. While that functionality still works, I tend to forget to delete the temporary relationship and temporary field. This makes them le less temporary than expected. And cue ExecuteSQL’s entrance!

ExecuteSQL

With ExecuteSQL, we can write a calculation that puts the location’s primary key in the project table’s foreign key for the location. No relationship needed. Let’s take a look at the pieces we have.

First, we have a Location table that has each location’s name (what the user used in Excel). We also have a primary key and miscellaneous data about the location.

Next, we have a Project table, which tells us project details, including a location foreign key to relate the two tables. The ztempLocation is the temporary field that came in from the Excel import. It’s the location name used in Excel as the “identifier” for a given location.

The project data that you see is the data that was imported straight from Excel. Next, we need to populate the foreign key for the location based on what’s in the ztempLocation field. This ExecuteSQL statement will solve all of our problems (err… well this one problem at least!):

ExecuteSQL(
"SELECT "__kp_Location"
FROM Location
WHERE Upper( "Location Name" )= ?" ;
"" ; "" ;
upper ( ztempLocation )
)

Let’s break that calculation down:

We’re trying to grab the primary key from the Location table. We know which primary key we want when the location’s name matches whatever is in the ztempLocation. Note that any of the backslash + quote characters is for the sake of the quite picky SQL statement. SQL doesn’t like field names that start with underscores, contain spaces, or are reserved words; putting quotes around the field(s) in question allows you to create the SQL statement.

For the sake of FileMaker, the quote has to be escaped with a backslash in order for FileMaker to understand you want to show a quote, not that you’re ending a literal string. In this case, I have field names that start with underscores and a field name that has a space in it (which I know makes purist developers very, very sad – my condolences).

The WHERE portion of the SQL statement is where the magic happens. We want the primary key from the Location table when the Location Name matches… question mark? The question mark acts like a placeholder for some value that FileMaker knows. Near the bottom of the calculation, you’ll see “upper ( ztempLocation )” and that’s what is replacing the question mark above.

Why the Upper? SQL is very touchy when it comes to case, meaning that Htown and HTOWN aren’t the same. So we can use either Upper or Lower to make the case all the same.

Moving Forward with Prepared Calculations

With these calculations ready to go, we will:

1. Click inside of the _kf_Location field.
2. Choose Scripts > Replace Field Contents… (or use the keyboard shortcut Control/Command + equal sign).
3. Paste the calculation written above and click Replace.
4. Cheer with excitement!
5. Victory dance (everyone has to have one).

So a calculation like this can be used to properly relate your data without having to create temporary and unnecessary relationships. The disclaimer? Data entry is rarely 100% perfect, so if someone misspells HTOWN as HTON, the calculation will leave that field blank. A quick search on all foreign keys that are empty should get you a short list of records that might need some manual help, but at least the bulk of the problem was solved!

Go ahead… do that victory dance again.

Have you used the ExecuteSQL statement in a similar manner? Tell me about in the comments!