Parse Data from one Filemaker Format to Another

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.