Filemaker: Containers galore

I’ve used Filemaker for a while, mostly for various organization tasks. I had an interesting experience with it recently.

A part of a project for someone I’m working for called for a robust file management methodology. In addition to tracking the songs used by a music publisher, there needed to be a way to store the associated files, such as sheet music, mastered tracks, etc. Basically, I needed Filemaker to handle the storage and naming of files. One cannot rely on the end-user to maintain a static file system and naming convention with 100% accuracy.

As many Filemaker users know, the two ways for Filemaker to interact with files: One can store them in the database, the second can reference them from the database, recording only a file name and path to a field.

The problems with each method quickly become apparent in a media environment:

Storing very large files in (what is usually) a local database causes it to drag. Badly. Stored files are also just that: files. There is no way to take advantage of Filemaker’s ability to handle media…No embedded audio, no pictures that display…Just <<random.file>>. This, quite frankly, sucks. Filemaker: You need to do something about it.

Storing references is also problematic, because when files move and folders are renamed, the reference is then useless. However, at least with a referenced file, it can be played or displayed within Filemaker.

I guess I should mention that the client works on a Mac, which is my preferred platform, and typical of many media companies. However, what follows would work just fine on a PC.


My kludgy (Because of Filemaker’s limitations) solution: Note that the table we are using is called ‘Songs’, and that we are storing the full track in a folder called ‘Container Master Full Songs.’ The rest of the fields should be self-explanatory.

FullTrack Load
Clear [ Songs::TempContainer ]
[ Select ]
Insert File [ Songs::TempContainer ]
Set Variable [ $file; Value:(Songs::Song SKU & (Right ( Songs::TempContainer ; 4 ))) ]
Go to Layout [ “Songs” (Songs) ]
Export Field Contents [ Songs::TempContainer; “file:Container Master Full Songs/$file” ]
Clear [ Songs::Full Track ]
[ Select ]
Insert QuickTime [ “movie:Container Master Full Songs/$file” ]
Clear [ Songs::TempContainer ]
[ Select ]

In English:

Copy the file to a ‘swap space’ in the database, generate a file name, export it with that specific file name to a specific (non-user accessible place), and use that specific name to reference the file. The one interesting tidbit is:

(Right ( Songs::TempContainer ; 4 ))

This little string grabs a 3 character file extension (plus the period), since Filemaker relies on the extension, rather than the creator code to decide how to handle a file. This is a bad way to implement anything on OS X, since the rest of the OS, and many programs rely on creator codes, rather than relying on extensions. The problem with this method is that it only works with 3 character file extensions. Not a big deal, but still annoying.

Exports happen similarly: Copy the file to the database temp field, export and prompt user to name the file and location.

Is this ideal? No. Does it work? Yes.

Comments are closed.