Delimited Scripting
by Bruce Robertson, bfr@nwlink.com, January 23, 2001
Using FileMaker script names to pass script parameters
This is the first in a series of AppleScript columns I hope to write for MacEdition, so let me give you a brief introduction, then get on to the technical bits. I’m currently a full-time FileMaker developer, but I spent 15 years as a mechanical engineer working in Manufacturing R&D at Boeing. Before that I did submarine vibration analysis, and before that I trained motorcycle mechanics. I also participated in the motorcycle sport of Observed Trials for about 20 years.
Okay, enough of that – let’s talk scripting:
FileMaker is a very popular application, with its own set of advantages and disadvantages. It is easy to create complex relational databases and to control database actions with native FileMaker scripts. These scripts are edited by bringing up the ScriptMaker dialog, which in turn may bring up several more layers of dialog boxes. Once you’re done editing a script, you may have to print it to see what it does. For instance, sort orders which have been stored within a script can’t be seen – even when you edit the script! Wouldn’t it be nice if you could tell what a script does, just by looking at the script title? Wouldn’t it be nice if you could avoid layers of dialog boxes, and control what a script does just by editing the title?
You can – with certain limitations – and I’ll show you how. ScriptMaker can create native FileMaker scripts, but it can also be greatly extended with AppleScript. Some of the techniques I will discuss work with standard ScriptMaker steps, and other examples will show how to pass entire AppleScripts or script parameters using the ScriptMaker script name.
Go modular
The key idea is to write modular scripts which take values from the script name. To create variations on your original script, you simply duplicate it and modify the script name.
A simple example: Contact databases are very common FileMaker applications.
And one of the common things people like to do with them is to search for
people, often by the first letter of the last name. There are lots of way
to approach this. One is to manually perform a find operation, write a new
script with the Perform Find [restore] script step, and call
it Find "A". To create the Find
"B" script, you have to manually perform the find, create and
name the new script, etc.
To make life easier, consider this alternative script:
enter find mode --do not check the Restore optionset field (LastName, right(status(currentscriptname), 1))perform find --do not check the Restore option
Name this script Find A. Now duplicate the script, changing the last character of the duplicate script’s name to B. Continue duplicating and renaming with the remaining letters of the alphabet. Then create buttons on your list layout, named A, B, C and so on, and attach the appropriate scripts to them.
You’ve now created your finds with much less effort.
This technique can be extended to find date ranges, numerical ranges and more.
Here’s how:
Let’s assume you have an invoice database with a field called InvoiceDate, and you wish to find dates for each quarter of the current year. Create a new global text field, and call it zScriptName. (I like my global fields to sort to the bottom when viewed by name in the Field Definitions dialog box.)
Create a script named Find 1/1/YYYY...3/31/YYYY
set field (zScriptName, middlewords ( status ( currentscriptname)), 2, 10)Trim(Substitute(Status(CurrentScriptName), "Find", ""))
enter find mode --do not check the Restore or Pause optionpaste result (InvoiceDate, zScriptname)perform find --do not check the Restore option
Duplicate the script three times, renaming each time so you end up with scripts:
Find 1/1/YYYY...3/31/YYYY
Find 4/1/YYYY...6/30/YYYY
Find 7/1/YYYY...9/30/YYYY
Find 10/1/YYYY...12/31/YYYY
So far, so good. But you can’t pass the name of the field you’d like to search to a standard ScriptMaker script this way. And it’s hard to handle multiple fields. For this, you’ll need AppleScript.
Extending with AppleScript
Let’s search our Invoice database for big spenders from Calfornia. Create a script titled Find|InvoiceTotal|>70,000|State|CA Note that there can be no extra spaces surrounding field names. To do the search, the script will contain the following:
set field (zScriptName, status ( currentscriptname)) perform applescript
try
copy cell "zScriptName" to scriptName
-- note that for global fields, we do not have to refer to the current record
set applescript’s text item delimiters to "|"
set paramList to text items 2 thru - 1 of scriptName
-- result {"InvoiceTotal", ">70,000", "State", "CA"}
set paramCount to count of paramList
if paramcount div 2 = 0 -- our list has matching field / value pairs
delete every request
set NR to create new request
repeat with num from 1 to paramCount by 2 set cellName to item num of paramList
set queryValue to item num + 1 of paramList copy queryValue to cell cellName of NR
end repeat
tell document 1 to find
else
error "Incorrect parameter list."
end if
on error errmsg number errnum
beep
display dialog errmsg buttons {"Oops"} default button 1 end try
Making it general
Now let’s get serious! How about a single script that will FIND or SORT across multiple fields? And the only thing you have to do to set it up is duplicate the original and change the script title!
We will be building scripts which work with parameters passed in the following format:
Sort|field Name|sort direction|field Name|sort direction| or
Find|fieldName|search criteria|field name|search criteria|
You will need to create three scripts: a universal script, a short script which passes its own name to the universal script, and a script titled Find.
First create the script Universal Sort or Find. It will start with a Perform applescript step, followed by some standard ScriptMaker steps.
Perform applescript
copy cell "zScriptName" to theParams
property actionType : ""
property listA : {}
property listB : {}
parseParams(theParams)
doit()
to parseParams(paramSource)
set AppleScript’s text item delimiters to {"|"} set paramList to text items of paramSource copy item 1 of paramList to actionType
copy (count of paramList) to paramCount
set {listA, listB} to {{}, {}}
repeat with num from 2 to paramCount - 1 by 2 copy item num of paramList to end of listA copy item (num + 1) of paramList to end of listB end repeat
end parseParams
on doit()
if actionType = "Sort" then
doSort()
else if actionType = "Find" then
doFind()
else
throwError("Invalid action type.")
end if
end doit
on doSort()
set itemCount to count of listA
copy {} to fieldList
copy {} to orderList
repeat with num from 1 to itemCount
set fieldName to item num of listA
set sortOrder to item num of listB
set end of fieldList to (a reference to field fieldName) if sortOrder = "A" then
set end of orderList to ascending
else if sortOrder = "D" then
set end of orderList to descending
else
throwError("Incorrect sort parameters.") end if
end repeat
sort by fieldList in order orderList
end doSort
on doFind()
try
set itemCount to count of listA
delete every request
set NR to (create new request)
repeat with num from 1 to itemCount
set fieldName to item num of listA
set criteria to item num of listB
copy criteria to cell fieldName of NR
end repeat
do script filemaker script "Find"
on error errmsg
throwError(errmsg)
end try
end doFind
on throwError(errmsg)
beep
activate me
if length of errmsg > 255 then
set errmsg to text 1 thru 255 of errmsg
end if
display dialog errmsg buttons "OK" default button 1 end throwError
Exit Record/Request
If ["Status(CurrentFoundCount) = 0"]
Beep
Show Message ["No matching records were found. All records will be displayed."]
Find All
End If
When you view it in ScriptMaker, the result should look like this:
Perform AppleScript [copy cell "zScriptName" to theParams....] Exit Record/Request
If ["Status(CurrentFoundCount) = 0"]
Beep
Show Message ["No matching records were found. All records will be displayed."]
Find All
End If
Next, create a script titled Find, with the following code:
Set Error Capture [On]
Perform Find [] do NOT select restoreIf ["Status(CurrentFoundCount) = 1"]
Perform script [Form View]
Else
Perform script [List View]
End If
"Find"
Finally, create a simple script titled
Find|LastName|R|City|Seattle|:
set field [zScriptName, status(currentScriptName)] perform script [Universal Sort or Find]
and duplicate this script, renaming the new script Sort|State|A|LastName|A|FirstName|A|. We now have a script for finding and a script for sorting.
How does this work? Let’s start with the "Find" script, titled Find|LastName|R|City|Seattle|.
- The AppleScript first breaks up this string this way: {"Find", "LastName", "R", "City", "Seattle"}.
- It places "Find" in the actionType property.
- It places {"LastName", "City"} in the listA property.
- It places {"R", "Seattle"} in the listB property.
- Then it deletes all previous Find requests, creates a new Find request, and populates the fields of the request.
- Finally it calls the "Find" script we created above to actually perform the find.
How about the Sort script titled Sort|State|A|LastName|A|FirstName|A|?
- The AppleScript breaks up this string this way: {"Sort", "State", "A", "LastName", "A", "FirstName", "A"}.
- It places "Sort" in the actionType property.
- It places {"State", "LastName", "FirstName"} in the listA property.
- It places {"A", "A", "A"} in the listB property.
- The general format for AppleScript sorting in FileMaker is: sort by {field "LastName", field "FirstName"} in order {ascending, ascending}, so the AppleScript take listA and create a series of field references, then takes listB and creates a new list where "A" is replaced by "ascending" and "D" is replaced by "descending".
- Finally, it issues the resulting sort command.
So you now have a single script that will FIND or SORT across multiple fields just by copying it and changing the copy’s title.
To help you along, I’ve provided an example file that demonstrates these techniques. Hmm ... since the universal script gets its data from a field, maybe there are some ways we could extend this technique even further? Next time...