Skip to: Site menu | Main content

Blog > FileMaker, you're wiggin me out, man

>> FileMaker, you're wiggin me out, man

Wed, Mar 14th 1:58pm 2007: Web Development

Way back in the day (a day well over a decade past, I might add) I spent a lot of time doing FileMaker Pro development. Most of what I did was hooking them up to the Internet using nasty little Applescript hacks and such, but then I saw the light and didn't look back. Until recently. I'm in the process of converting not one but two art dealerships / galleries from internally developed FMP stock management systems to SiteBuilder-based intranets. They both have thousands of stock records in FMP that I need to somehow liberate into a more accessible format, and along the way I've developed a bunch of little tricks like field-at-a-time conversion (more about that in a later post) but the one that has been bending my brain today is how to get images out of the DB into an actual image file. For background, FMP has a "container" field type that is basically a binary blob. You can stick anything in it you like, including raw images: with a container field in a layout you can copy and paste (or even just drag in) things like JPEGs. Sweet. But it's kinda one-way. In the first conversion, the client had stored stock images externally and included a text field in the DB that referenced the image file, so that was a piece of cake. The second client had all the images stored *inside* the DB. But the kicker is that there is no way (at least in FMP v7) to export container fields. It just flat-out refuses to do it. The only way to get them out is to go through every record, select the image, copy it, go to a document, paste it, and save the document. Manually. For every record. AAAGGHHHHHH!!!! A bit of quick Googling showed up this post by Marc Liyanage who went to the extreme of using FMP script to execute an internal calculation to dynamically generate AppleScript which then generated and executed Shell. Now *there's* an example of determination, and maybe just a little bit of insanity. It's right up there with my OSDC lightning talk last year demonstrating a PHP script that generated code in OSDcLang which is a 3-syntax-element Turing-complete language which was then executed by an interpreter written in Ruby. But back to the story. Marc's effort was heroic, but I just couldn't get it quite working in the way he wrote it. So I simplified it and actually got the same end result with one less AppleScript and no shell at all. If you don't mind losing a few braincells just follow the bouncing ball: 1. Create a directory called "picture_export" in your home directory for the images to go in. 2. Go to Scripts -> ScriptMaker -> New..., and make yourself a script called "ImageExport" or somesuch. 3. Create the basic script structure like this: Go to Record/Request/Page [First] Loop Copy [Select; STOCK LIST 1-9-04::PICTURE] Perform AppleScript[] Go to Record/Request/Page [Next; Exit after last] End Loop In this case the DB name is "STOCK LIST 1-9-04", and "PICTURE" is the name of the container field with the images that we want to liberate. 4. Now the funky bit: the calculated AppleScript. Edit the "Perform AppleScript" line and set it to "Calculated AppleScript", then "Specify..." to get a dialog box to put it into. Hold onto your pants (and put some on first if you need to) because this is going to get weird: "set thePic to the clipboard" & ¶ & "try" & ¶ & " set thePic to JPEG picture of thePic" & ¶ & "set thePath to (path to home folder as Unicode text) & "picture_export:" & "" & ${STOCK LIST 1-9-04}::Registration number & ".jpeg"" & ¶ & "set fileRef to open for access file thePath with write permission" & ¶ & "set eof fileRef to 0" & ¶ & "«event rdwrwrit» thePic given «class refn»:fileRef" & ¶ & "close access fileRef" & ¶ & "on error" & ¶ & "end try" & ¶ Basically the calculation is a simple text concatenation that dynamically generates the AppleScript on each execution. That's why there are bizarro Apple paragraph characters appended to each line: without them the AppleScript parser can't figure out where the lines end. Update the database name reference to suit, and in this case "Registration number" is a unique, auto-incrementing serial number field that we're using so the file names are unique and we can relate them back to DB records. 5. Run the script. Enjoy!

Bookmark and Share