Sunday, December 17, 2017

#1 2006-04-10 05:00:31 am

Diana Simonson
Member
Registered: 2005-11-30
Posts: 5

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".

Applescript:


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.

Applescript:


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.

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)