The cross-platform Filemaker program has its own built in scripting language, and includes calculation commands that can parse text; however, in a recent project, when converting from one file design to another, I felt it was much more efficient to use applescript to accomplish this task. Filemaker is very scriptable on the Mac platform and has been for years. The dictionary has been highly backwards compatible through all of its version upgrades. These scripts were written to work with version 7 so there are some terms, such as table, that address new structures in 7 that were not previously a part of earlier versions.
With this project, I was converting the client from a survey database with a rigid format where the questions and answers were hard coded as fields to a more flexible format where questions were records and answers were related. Filemaker makes this possible by not only allowing you to script the data that goes into fields, but allowing you to get information about the structure of the database, itself, through scripting.
I used two scripts to accomplish my goal. The first script created the questions by pulling the fieldnames out of their original database and putting them in a new table named “questions”.
set mydata to "TheirDatabase.fp7"
set dbname to "NewDatabase.fp7"
tell application "FileMaker Developer"
set myfields to name of every field of database mydata
repeat with everyfield in myfields
set newrec to create new record of table "questions" of database dbname
set cell "question" of newrec to everyfield
end repeat
end tell
The second script queried their database and entered the answers into a new table called “answers”. For this script, I refer to “field” instead of “cell” so I get a whole list of items that can then be cycled through.
set mydata to "TheirDatabase.fp7"
set dbname to "NewDatabase.fp7"
tell application "FileMaker Developer"
--Make sure this is only counting the found count and that all found records are from the same survey.
set totalrecs to count record of table "questions" of database dbname
repeat with everyrecord from 1 to totalrecs
set myname to cell "questionid" of record everyrecord of table "questions" of database dbname
set myfields to field myname of table mydata of database mydata
set myIDs to field "UID" of table mydata of database mydata
set totalsites to count myIDs
repeat with everyid from 1 to totalsites
set newrec to create new record of table "answers" of database dbname
set cell "UID" of newrec to item everyid of myIDs
set cell "answer" of newrec to item everyid of myfields
set cell "questionid" of newrec to myname
end repeat
end repeat
end tell
With these two simple scripts, applescript helped us to convert a rigid database design to a more flexible one without sacrificing all of the work that the data entry people had already done.