AppleScript and FileMaker

Free AppleScripts for FileMaker Pro and FileMaker Pro Advanced

This site is for those who want to write scripts for FileMaker Pro or FileMaker Pro Advanced, but aren't ready to delve deeply into the complexities of FileMaker scripting.

While some of the scripts presented here use some of FileMaker's built-in scripting capabilities, some of the scripts you will see here are written almost entirely with AppleScript.
These scripts can easily be used as is or (with a few changes and/or additions) adapted to your own specific requirements. Most are pretty easy to understand and to implement.

Getting Started with Custom Functions

If you are new to using Scriptmaker to write FileMaker scripts, this article may be too advanced for you. If, however, you are fairly comfortable with using functions (you know where you are), this can open up a whole new world of possibilities for writing scripts for your databases.

The real power of custom functions is in that once you work out all the details, they can be used over and over again. You will always know when writing a custom function is in order too. It will dawn on you, when you find yourself writing the same script over and over again.

The script I have presented below is one such script involving the calculation of dates. If you have never written a custom function before, though, please get acquainted first with the custom function window from my function 'getShortDate (longDate)' :

Source

getShortDate ( longDate )

Eg: getShortDate ( Appointments::Date )
where 'Date' is a field containing something like: Tuesday, January 5, 2010

This function would return 1/5/2010 for the date above:

The code :

Let ( [
$theMonth = MiddleWords ( longDate ; 2 ; 1 ) ;
$monthNumeric = Case ( $theMonth = "January" ; 1 ; $theMonth = "February" ; 2 ; $theMonth = "March" ; 3 ; $theMonth = "April" ; 4 ; $theMonth = "May" ; 5 ; $theMonth = "June" ; 6 ; $theMonth = "July" ; 7 ; $theMonth = "August" ; 8 ; $theMonth = "September" ; 9 ; $theMonth = "October" ; 10 ; $theMonth = "November" ; 11 ; $theMonth = "December" ; 12 ) ;
$monthDay = MiddleWords ( longDate ; 3 ; 1 ) ;
$theYear = RightWords ( longDate ; 1 )] ; 
$monthNumeric & "/" & $monthDay & "/" & $theYear )

The first line

This gets the second word of the long date string :

$theMonth = MiddleWords ( longDate ; 2 ; 1 ) ;

The next line

This takes the variable from the first line and, using the case function, assigns the corresponding numeric value to the variable $monthNumeric

$monthNumeric = Case ( $theMonth = "January" ; 1 ; $theMonth = "February" ; 2 ; $theMonth = "March" ; 3 ; $theMonth = "April" ; 4 ; $theMonth = "May" ; 5 ; $theMonth = "June" ; 6 ; $theMonth = "July" ; 7 ; $theMonth = "August" ; 8 ; $theMonth = "September" ; 9 ; $theMonth = "October" ; 10 ; $theMonth = "November" ; 11 ; $theMonth = "December" ; 12 ) ;

Then we get the third word of the long date string, which is the day of the month :

$monthDay = MiddleWords ( longDate ; 3 ; 1 ) ;

Then the year

This gets the last word of our long date string

$theYear = RightWords ( longDate ; 1 )] ;

Finally, the calculation :

Now we concatenate all of our variables with a couple of forward slashes to get the numeric date string :

$monthNumeric & "/" & $monthDay & "/" & $theYear

Get Map from Google, Yahoo or Mapquest

This is a script, which concatenates the text from a database's fields with the required punctuation in between each text item and combines it with the appropriate map URL and executes an 'Open URL' script step to find an address with Google, Yahoo, or Mapquest. To use this, you need to have the following fields:

Address, City and Zip Code

In mine, I have a preferences layout with a 'Map Preference' radio group including three choices:

Google, Yahoo and Mapquest

The radio button chosen determines which part of the script is executed. You'll notice that the 'Else If' for Mapquest is a little different than the other two. This is because, the Google and Yahoo parts of the script can be executed in a single step.

For Mapquest, on the other hand, you need to have FileMaker copy the info for the address to the clipboard, so that it can be pasted into the required text field in your browser. For that, I have a calculation field named 'Address Info', which concatenates the Address, City and Zip Code fields above into a string with a space in between each item.

Here is the script:

Go to Layout ["Main"]
If [Your_Database::Map Preference = "Google"]
    Set Variable [$mapURL; Value:"https://www.google.com/maps/place/"]
Else If [Your_Database::Map Preference = "Yahoo"]
    Set Variable [$mapURL; Value:"http://maps.yahoo.com/maps_result?addr="]
Else If [Your_Database::Map Preference = "Mapquest"]
    Set Variable [$theURLAddress; Value:"http://www.mapquest.com"]
    Perform AppleScript ["set targetAddress to (get data cell "Address Info" of current record)
set the clipboard to targetAddress"]
End If
If [$theURLAddress = ""]
    Set Variable [$theURLAddress; Value:Let (
[ 
$theAddress = Your_Database::Address;
$theCity = Your_Database::City;
$theState = Your_Database::State;
$theZipCode = Your_Database::Zip Code;
$theCountry = "US";
$theAddressURL =
Case ( IsEmpty ($theAddress) ; "" ; Substitute ($theAddress;" ";"+") )& "+" &
Case ( IsEmpty ($theCity ); "" ; Substitute ($theCity;" ";"+") )& "+" & 
Case ( IsEmpty ($theState) ; "" ; Substitute ($theState;" ";"+") )& "+" & 
Case ( IsEmpty ($theZipCode) ; "" ; Substitute ($theZipCode;" ";"+") )& "+" & 
Case ( IsEmpty ($theCountry) ; "" ; Substitute ($theCountry; " " ; "+") )
];
$mapURL & $theAddressURL 
)]
End If
Open URL [No Dialog; $theURLAddress]
Go to Layout ["Main Edit"]

A lot of this should be easy to put together, even if you are not familiar with the 'Let' function above, since I put this together so that you can 'cut and paste' the scripting details where required into the various script steps. The basic premise of the 'Case' functions is that if a given field is empty, then the corresponding variable gets an empty value as well. However, if the field is not empty, then variable is assigned the fields value and a '+' is placed between it and the next item.

Now back to Mapquest:

Since Mapquest will not accept a URL with plus sighs, we first set the variable '$theURLAddress' to the value "http://www.mapquest.com" to go to its site first, then when the page has loaded, we paste in the value that was copied to the clipboard.

For Mapquest, you'll need to paste the address in the text field and click 'Get Directions'
For Mapquest, you'll need to paste the address in the text field and click 'Get Directions'

Text Attribute Crash Course

A great thing about FileMaker which has been around, I think, since v 8.5, is the ability to script text font, style, size and color. This capability is great for things as simple as just improving the overall appearance of a database to the more important conditional formatting that serves an even greater purpose for the user interface.

Below is a screen shot of a database that I designed when I was learning about all of these neat text functions. In this article, I will pull some code directly from this database to show some of the various text attribute manipulations that are possible with these new design functions.

FileMaker Pro Advanced 9
FileMaker Pro Advanced 9 | Source

TextFont Function

One of the simplest is in changing a fields text font (see image below).

The basic syntax for this is:

TextFont ( text ; fontName {; fontScript} )

This can be something as simple as:

TextFont ( your table::your field ; "Lucida Grande") 

where 'your table::your field' is the field to be set to 'Lucida Grande' (the 'fontScript' part is for a script that you could attach to this, and is optional) Alternately, to remove a text setting, you can use:

For more on the fontScript parameter, go to: http://www.filemaker.com/help/12/fmp/html/func_ref3.33.98.html

 TextFontRemove ( your table::your field )

Its just that easy.

*By the way, instead of targeting a field, you could use literal text or a calculation formula which resolves to text. Also, note that the font name must be enclosed within quotes and must be spelled exactly right and is case-sensitive.



FileMaker Pro Advanced 9
FileMaker Pro Advanced 9 | Source

TextSize Function

The syntax for this is very easy and similar to the TextFont function above

TextSize ( text ; fontSize )

Where text is a field or anything else that will resolve to text and fontSize is an integer without quotes.

Eg.

TextSize ( your table::your field ; 12 )

TextColor Function

The TextColor function is a little more complicated. It's basic syntax is:

TextColor ( text ; RGB ( red ; green ; blue ) )


The text part is the same as above

The 'red ; green ; blue' parameters are a series of numbers, each separated by a semi-colon and enclosed in parentheses. Each value is a number between 0 and 255, which represent the amount of red, green and blue respectively. The higher the number, the greater amount of the given color.

Eg:

TextColor ( your table::your field ; RGB ( 0 ; 0 ; 255 ) )



Something else you could do, which I did with my database (see above) is to assign 3 text fields for the red, green, blue values (I named mine RGB1,RGB2,RGB3)

With that done, you can have a script like this:

TextColor (your table::your field; RGB ( Text Attributes::RGB1 ; 
Text Attributes::RGB2 ; Text Attributes::RGB3 ) )


This sets the color relative to the values in the color fields. With that, you could set up a script where the color changes whenever you change the field values as I did in mine.


Put this all together like this:

TextSize( TextFont( TextColor( your table::your field;
RGB( 128 ; 0 ; 128 ) ); "Monaco" ) ; 24)


This is a nested statement which makes the text in 'your table::your field' Monaco font, size 12 and with a color which is like a sort of plum purple.

FileMaker Pro Advanced
FileMaker Pro Advanced | Source

FYI: Object Info Window

Did you know that the object info window is not only useful for seeing the dimensions of an object and its location on the grid, but also for editing its dimensions and its relationship with other objects and more with just the press of the enter key ?

If you look at the info window for a given object, you'll probably see a blank field at the top. This is the name field for the object, and, by default it is left blank. All you need to do to name the object is type something in and hit the enter key. That's it !

But, of course, there's more...

All of those text fields, they determine the location of the selected object and its left, right, top and bottom dimensions. As above, to change a setting, just type in a number and hit the enter key.

You may know that you can shift-click 2 or more objects and move them together. Well you can also shift-click objects and change their dimensions etc all at once. This is also a useful method for aligning two objects precisely. Just enter a number as before, enter key, and done !

Finally, those little anchor icons. Use anchors to anchor an object to a specific part of the window, just click the anchor you want.


Import Image to FileMaker Pro Advanced 9

Although this script was written specifically for FileMaker Pro Advanced 9, as it was written entirely in AppleScript, it should work with versions as far back as FileMaker 6. Note that, as part of the script uses System Events, GUI scripting must be enabled.


At the end, using this script directly through FileMaker.

on run
	set saveDelimiters to text item delimiters of AppleScript
	set desktopLocation to path to desktop as alias
	--Choose a destination folder
	set moveLocation to ((desktopLocation as string) & "Screen Shots") as alias
	tell application "Finder"
		activate
		open desktopLocation
		set targetImage to (choose file "Select an image file:" default location desktopLocation)
				set text item delimiters of AppleScript to ":"
		set folderLocation to (targetImage as string)
		set folderLocation to (text items 1 thru -2 of folderLocation as string) as alias
		open folderLocation
		set imageName to text item -1 of (targetImage as string)
		select item imageName of front Finder window
		move selection to moveLocation
		open moveLocation
		select item imageName of front Finder window
		open document file imageName of front Finder window
		tell application "Finder" to activate
		set newName to text returned of (display dialog "Enter a new name for image file '" & imageName & "' :" default answer imageName buttons {"Cancel", "Set"} default button 2 with title "Edit image name" with icon 2)
		set name of document file imageName of front Finder window to newName
		tell application "Preview" to activate
		tell application "System Events"
			tell process "Preview"
				tell menu bar 1
					tell menu "Edit" of menu bar item "Edit"
						click menu item "Select All"
						click menu item "Copy"
					end tell
					tell menu "File" of menu bar item "File"
						click menu item "Close Window"
					end tell
				end tell
			end tell
		end tell
	end tell

tell application "FileMaker Pro Advanced"
		activate
		try
			if newName ≠ "" then
				go to (create record)
				set data of cell "GraphicName" of current record to newName
				set initChar to text 1 of newName
				set data of cell "Alpha Pointer" of current record to initChar
				set data of cell "imageInfo" of current record to imageInfoSheet
				go to cell "GraphicField" of current record
				paste clipboard
			else
				error
			end if
		on error errorMsg
			display dialog errorMsg
		end try
		set text item delimiters of AppleScript to saveDelimiters
	end tell
end run
	
tell application "FileMaker Pro Advanced"
		activate
		try
			if newName ≠ "" then
				go to (create record)
				set data of cell "GraphicName" of current record to newName
				set initChar to text 1 of newName
				set data of cell "Alpha Pointer" of current record to initChar
				set data of cell "imageInfo" of current record to imageInfoSheet
				go to cell "GraphicField" of current record
				paste clipboard
			else
				error
			end if
		on error errorMsg
			display dialog errorMsg
		end try
		set text item delimiters of AppleScript to saveDelimiters
	end tell
end run

To run this script from FileMaker, link this script to a button in FileMaker with a 'Perform AppleScript' script step. Also, be sure the name of the AppleScript external matches the name in the 'Perform AppleScript' script step, 'Change Name Loc of Image 9.scpt' below:

set desktopLocation to path to desktop as alias
set fileLocation to ((desktopLocation as string) & "Screen Shots Folder:Change Name Loc of Image 9.scpt") as alias
tell application "Finder" to open fileLocation
delay 5
tell application "AppleScript Editor" to activate
tell application "System Events"
	tell process "AppleScript Editor"
		tell menu "Script" of menu bar item "Script" of menu bar 1
			click menu item "Run"
		end tell
	end tell
end tell
The FileMaker Tooltip which appears as result of script below
The FileMaker Tooltip which appears as result of script below

Create Tooltip for Image Attributes

This tooltip script is from an image database I created in which I wanted to have certain 'attributes' saved in various cells for the purpose of ''fine tuning searches. It takes the data from those cells and concatenates them into a more ineligible table form like in the image at the right. This is entirely written using FileMaker, although it could be done entirely in AppleScript as well.

First, you 'control-click' the object (button, graphic or field) to which you want to assign the tooltip and select 'Set Tooltip…' and then click the 'Specifiy…' button at the right. For this tooltip it will bring up a 'Specify Calculation' window like the one below. Below that, I explain what all of that in the edit window means.

Tooltip 'Specify Calculation' Window
Tooltip 'Specify Calculation' Window

First Line:

"Set or Edit attributes for" & ¶ & "image '" & ( Picture File::GraphicName ) & "'" & ¶ & ¶ &

This first part concatenates the phrase "Set or Edit attributes for" & ¶ & "image '" to the phrase resulting from ( Picture File::GraphicName ) & "'"

1. & ¶ & --> This places a return

2. ( Picture File::GraphicName ) & "'" & ¶ & ¶ & --> This evaluates the data contained in the data cell 'GraphicName' which I have assigned to contain data of type text, and concatenates it with the phrase "Set or Edit attributes for" & ¶ & "image '

3. & ¶ & ¶ & --> Two returns before the next line is added

Next, I add:

"Current Attributes: " & ¶ & ¶ & --> This is simply the phrase "Current Attributes: " followed by two more returns before the last part is added.

Finally, and the most complicated part:

If ( Picture File::ThemeGroup = "" ; "Category: none" ; Picture File::ThemeGroup) & ¶ &
If ( Picture File::ThemeSubGroup = "" ; "Subcategory: none" ; Picture File::ThemeSubGroup) & ¶ &
If ( Picture File::Favorites = "" ; "Favorite: no" ; "Favorite: yes" ) & ¶ &
If ( Picture File::starRating = "" ; "Star Rating: none" ; "Star Rating: " & Picture File::starRating )

All of these 4 lines that display the attributes are conditionals

Taking the first one (note the semicolons):

If ( Picture File::ThemeGroup = "" ; "Category: none" ; Picture File::ThemeGroup) & ¶ &

This says that if the data cell 'ThemeGroup' is empty then return the text "Category: none" or if it is not empty, return the text value found in the data cell 'ThemeGroup' instead.

The remaining three conditionals work on the same basic principle, but take note though that the third one is a little different. It says that if data cell 'Favorites' is empty, return the text "Favorite: no" and if data cell 'Favorites' is not empty, return the text "Favorite: yes"

Note that the last line does not contain a return (& ¶ & ) since it ends the concatenation.

FileMaker,Pro 6, startup
FileMaker,Pro 6, startup | Source

New Database Record

A Script to Create a New Record

This script assumes that you have a database with these cells: Name,Address,City,State,Zip,Phone, and Email


In the ScriptMaker Window:

Freeze Window
Flush Cache to Disk
Perform Script [Sub-scripts, "returnRecordReference"]
New Record/Request

Where returnRecordReference is an AppleScript:

In the AppleScript Window:

try
	set newRecordReference to (ID of current record)
	set data of cell "globalReturn" to newRecordReference
	--A global cell to store the ID of the current record before we create a new one
on error errormsg
	display dialog errormsg
end try

In the ScriptMaker Window:

Perform AppleScript ["try set newRecord to text returned....] (see below):

In the AppleScript Window:

try
	set newRecord to text returned of (display dialog "Enter name reference for new record:" default answer "" with icon note buttons {"Cancel", "Done"} default button {"Done"})
	set newAddress to text returned of (display dialog "Enter address for new record:" default answer "" with icon note buttons {"Done"} default button {"Done"})
	set defaultCityState to button returned of (display dialog "Set city and state to 'Columbus, OH' ?" buttons {"Other...", "OK"} default button {"OK"})
	--Change this to your default City and State:
	if defaultCityState = "OK" then
		set newCity to "Columbus"
		set newState to "OH"
	else
		set newCity to text returned of (display dialog "Enter city for new record:" default answer "" with icon note buttons {"Done"} default button {"Done"})
		set newState to text returned of (display dialog "Enter state for new record:" default answer "" with icon note buttons {"Done"} default button {"Done"})
		set newZip to text returned of (display dialog "Enter zip code:" with icon note default answer "00000" buttons {"Done"} default button {"Done"})
	end if
	set newPhone to text returned of (display dialog "Enter phone number:" default answer "" with icon note buttons {"Done"} default button {"Done"})
	set newEmail to text returned of (display dialog "Enter new emails and/or web addresses:" default answer "" with icon note buttons {"Done"} default button {"Done"})
	set recordDatalist to {newRecord, newAddress, newCity, newState, newZip, newPhone, newEmail} as list
	set cellList to {"Name", "Address", "City", "State", "Zip", "Phone", "Email"} as list
	repeat with x from 1 to 7
		if not (item x of recordDatalist = "") then set data of cell (item x of cellList) of current record to (item x of recordDatalist)
	end repeat
	show every record --this makes sure that no records are omitted
	sort layout 0 by field "Name" in order ascending --sort by default layout, in case there is more than one
on error errormsg
	display dialog errormsg
end try

You may want to have a button named 'New...' for this for convenience. Note that I have placed an 'on error' handler in this script. This is useful for catching any typos etc that you may have made while writing your script. I put this in most of my scripts (at least while testing) to be sure that there are no 'anomalies'.

Alphabetical List Dialog of Records

Display a List Dialog to Select a Specific file to Open

A script to list all entries beginning with the same alphabetical character. In this case records beginning with 'A'. Your ScriptMaker window should have the following script steps (complete Perform AppleScript step shown below). Copy this into the 'Perform AppleScript' script step window:

Show All Records

Perform AppleScript()

try
	sort layout 0 by field "Name" in order ascending
	set targetAlpha to "A"
	set alphaArray to {}
	set alphaArray to (ID of every record whose cell "Name" begins with targetAlpha)
	set convertArray to alphaArray
	set AppleScript's text item delimiters to return
	set arrayCount to count items in convertArray
	set convertedArray to {}
	repeat with x from 1 to arrayCount
		set convertedID to (item x of convertArray as integer)
		set convertedArray to convertedArray & convertedID
	end repeat
	set convertedArrayText to {}
	repeat with x from 1 to arrayCount
		set convertedName to (get cell "Name" of record ID (item x of convertedArray))
		set convertedArrayText to convertedArrayText & convertedName
	end repeat
	
	-->This will filter out erroneous records:
	set filterArrayText to {}
	repeat with x from 1 to arrayCount
		if (character 1 of word 1 of item x of convertedArrayText = targetAlpha) then set filterArrayText to filterArrayText & (item x of convertedArrayText)
	end repeat
	set targetRecord to ""
	set arrayCount to count text items in filterArrayText
	set arrayCount to arrayCount as text
	set targetRecord to (choose from list filterArrayText with prompt arrayCount & " search results found for '" & targetAlpha & "':")
	show (every record whose cell "Name" = targetRecord)
on error theError
	if theError contains "Object" and targetRecord = false then
	else if theError contains "Object" and targetRecord is not equal to false then
		display dialog "No items found for: '" & targetAlpha & "' !" with icon stop buttons {"Done"} default button {"Done"} giving up after 5
	else
		display dialog theError with icon stop buttons {"OK"} default button {"OK"}
	end if
end try

The Show All Records script step at the beginning could ordinarily be replaced by 'show every record' within the 'Perform AppleScript' script step, but for some unknown reason, I have been unable get this to work that way. As shown though, this should work without any problems.

AppleScript to Password-Protect FileMaker Records

Protect Your Data from Intruders

First, create a global field named 'accessPass' to store your password.

Create a script in ScriptMaker named "Password..." or something similar to place a password in the global field 'accessPass', with the following 'Perform AppleScript' script step:

set data of cell "accessPass" to text returned of (display dialog "Please enter a global password:" default answer "" buttons "OK" default button "OK")

Assign this script to a button such as 'Delete...' or whatever name you like:

tell application "FileMaker Pro Advanced"
	try
		set recordName to (get cell "Name" of current record)
		set purgeRecord to text returned of (display dialog "Please enter password to remove this record:" with icon caution default answer "" buttons {"Delete", "Cancel"} default button {"Cancel"} giving up after 10)
		set passText to (get cell "accessPass" of current record) as text
		if purgeRecord = passText then
			set theRecord to (get ID of current record) as integer
			set confirmPurge to button returned of (display dialog "Are you sure you want to delete record '" & recordName & "' ?" with icon stop buttons {"Delete", "Cancel"} default button {"Cancel"} giving up after 10)
			if confirmPurge = "Delete" then
				delete record ID theRecord
			end if
		end if
	on error
		return
	end try
end tell

FileMaker's Show Message Dialog

This Post gives you the basics on FileMaker's 'Show Message' dialog and how to handle user feedback based on the button chosen. As you'll notice from the actual text of the dialog below, clicking on a button returns a numerical value corresponding to the button clicked.

But first, the show message edit window:

And now the ScriptMaker script:

Go to Record/Request/Page [First]
Show Message ["This is a simple dialog with an 'OK' and a 'Cancel' button. OK returns a value of 1 and Cancel, a value of 2."]
If ["Status(CurrentMessageChoice) = 1"]
Go to Record/Request/Page [Next]
Else
Go to Record/Request/Page [By Number...]
End If

The numerical value corresponding to the button clicked, is used by 'Status(CurrentMessageChoice)' to determine a course of action. If the user clicks OK (value=1), then it goes to the next record, otherwise you are prompted to enter the record number you want.
This is the 'Show Message' dialog (or sheet):

Note: The numerical values of the buttons are right to left, i.e. OK is 1 and Cancel is 2.

FileMaker Pro 6 Custom Dialog Script

Perform a Search with Multiple Criteria

This Script shows how to use FileMaker's Custom Dialog to write a script with optional text entry fields with the Perform Find Script Step. For this you will first need to create global text fields: 'gName', 'gAddress', and 'gPhone',

Show Custom Dialog ["Find by name, address or phone", "Enter any combination of name, address or phone. Click OK to continue.", "gName"], "gAddress", "gPhone"
If ["Status(CurrentMessageChoice = 2)"]
Exit Script
End if
Enter Find Mode []
If ["not IsEmpty(gName)"]
set field ["Name", "gName"]
End if
If ["not IsEmpty(gAddress)"]
set field ["Address", "gAddress"]
End if
If ["not IsEmpty(gPhone)"]
set field ["Phone", "gPhone"]
End if
Perform Find [Replace Found Set]
If ["Status(CurrentError )= 401"]
Show Message ["Sorry, no records found!"]
Show All Records
set field ["gName", """"]
set field ["gAddress", """"]
set field ["gPhone", """"]
End if

In the first conditional, 'If ["Status(CurrentMessageChoice = 2)"]', we test for the 'Cancel' button (value 2) which would end the script with no further execution.


After we go to find mode, we enter the text, if any, into the appropriate fields to prepare for our search. Where there is a value entered it is placed in the appropriate field. Note that since we are in find mode, we are not replacing the existing text of the current record.

Contact me if you have any questions or comments at my comments section at the bottom of the page.

FileMaker Pro Instructional

Find Request Script for FileMaker Pro 9

This is a script that allows the user to perform a search. It shows how to handle the 'not found' error and using the get keyword to retrieve a result value and to handle the result of the find request.

This script uses FileMaker scripting exclusively. Here is how it would appear in the ScriptMaker Window:


Set Error Capture [On]
Perform Find [Restore]
If [Get (LastError) > 0]
Show Custom Dialog ["No records found. Click OK to modify
your request or Cancel for Browse mode."]
If [Get (LastMessageChoice) = 1]
Modify Last Find
Else
Enter Browse Mode []
End If
End If

Set Error Capture [On] - this tells FileMaker to capture the value (true or false, ie boolean) of whether or not script execution was successful. 0 is false, 1 is true - see '[Get (LastError) > 0]' below:

[Get (LastError) > 0]
- tells FileMaker that, if no records were found that a 'not found' error has occurred

[Get (LastMessageChoice) = 1]
-tells FileMaker that if the user chooses the 'OK' button to allow the user to attempt another find.

FileMaker Tip: Global Field to Store Current Date

This script checks the global field 'Current Date' for the current date, if it finds it, it leaves it as it is, if not it uses the Replace Contents script step below to parse all of the date elements into a single string and puts the result into the 'Current Date' container:

Freeze Window
If ["GetField(Current Date) <> Status(CurrentDate)"]
Replace Contents [No dialog, "Current Date","DayName(Today) & ", " & MonthName(Today) & " " & Day(Today) & ", " & Year(Today)"]
End If


This will yield something in the form of: Sunday, July 22, 2012

Find Request Script for FileMaker Pro 6

This script shows the basics of using FileMaker dialogs and handling errors using conditional clauses. This script is specific to FileMaker 6. A script serving the same purpose for FileMaker Pro 9 appears in the next post.

User enters search text in desired fields and initiates search by hitting the enter key.

Sort [Restore, No Dialog]
Show Message ["Enter search criteria in available fields, then hit the 'Enter' key for result"]
Set Error Capture [On]
Enter Find Mode [Restore, Pause]
Perform Find [Replace found Set]
If ("Status(CurrentError) > 0")
Show Custom Dialog ["Error: 'No records found.'", No records were found. Click OK to modify your request or click Cancel to return to Browse mode.]
If ("Status(CurrentMessageChoice) = 1")
Modify Last Find
Else
Enter Browse Mode []
Show All Records
Sort [Restore, No dialog]
End If
Show All Records
Sort [Restore, No dialog]
End If

A More Specific Search Script A Pop-Up that displays the names of every record in a database with a 'Go' button, which initiates a search for the chosen record.

Before using this script, you will need to create some fields and a button as follows:

1) Three global text fields: "globalName", "globalAddress", and "globalReturn"

2) A global text field named "NameSearch" (Set 'Specify Button' to 'Do Nothing', be sure to select the pop-up style and check the 'Allow Entry into Field' check box)

3) A text field named "Name" and a text field named "Address"

4) A button named "Go", to which you will assign the Perform Script script step below (you can name it whatever you wish). Remember that the text below in italics is to be place within a Perform AppleScript script step (without the outer quotes, which are placed there by FileMaker).

Show All Records

Sort [Restore, No dialog]

Perform AppleScript [] -SEE BELOW

try
	set recordReference to (ID of current record)
	set data of cell "globalReturn" to recordReference
	set targetSearch to "Name"
	set targetText to (get cell "NameSearch" of current record)
	--This is key, gets values across entire database for 
	Name field
	set data of cell "gName" to targetText
	set data of cell "gAddress" to targetSearch
end try

Enter Find Mode []

If [globalAddress" = "Name""]

Set Field ["Name","globalName"]

End If

If [globalAddress" = "Address""]

Set Field ["Address","globalName"]

End If

Set Error Capture [On]

Perform Find [Replace Found Set]

If ["Status(CurrentError)=401"] --not found

Perform AppleScript [] -SEE BELOW


display dialog "Sorry, no records found!" with icon stop buttons "Abort" default button 1 giving up after 10

Show All Records

Sort [Restore, No Dialog]

Set Field ["globalName", """"]

Set Field ["globalAddress", """"]

End If

To use this, first select a record name from the pop-up (or type in first couple of characters to jump to the area and scroll down to the desired record). Next choose the record by clicking on it. Then click the 'Go' button to go to the record.

Compile Address Book Entry from FileMaker Database

This is a script that gets the contents of text fields from a FileMaker database to create an entry in Address Book. It illustrates pretty thoroughly the AppleScript syntax for the objects in Address Book and data transfer from FileMaker Pro.

The first part collects all of the data from the text fields of the Filemaker database into variables. The second, creates the entry with a first and last name and sets the variable theEntry to a reference to the new entry and sets the other values for the new entry.

tell application "FileMaker Pro"
	activate
	set fullName to (get data of cell "Name" of current record)
	set nameCount to (count words of fullName)
	set entryAddress to (get data of cell "Address" of current record)
	set entryCity to (get data of cell "City" of current record)
	set entryState to (get data of cell "State" of current record)
	set entryZip to (get data of cell "Zip Code" of current record)
	set entryPhone to (get data of cell "Phone" of current record)
	set entryEmail to (get data of cell "Email" of current record)
	set entryURL to (get data of cell "Internet" of current record)
	delay 2
end tell
tell application "Address Book"
	activate
	set firstName to (get word 1 of fullName)
	if nameCount is not equal to 1 then
		set lastName to (get word 2 of fullName)
	else
		set lastName to ""
	end if
	set theEntry to make new person with properties {first name:firstName, last name:lastName}
	tell theEntry
		make new address at end of addresses with properties {label:"Home", street:entryAddress, city:entryCity, state:entryState, zip:entryZip}
		make new phone at end of phones with properties {label:"Home", value:entryPhone}
		make new email at end of emails with properties {label:"Home", value:entryEmail}
		make new url at end of urls with properties {label:"Work", value:entryURL}
	end tell
	save
end tell

The rather wordy section at the beginning of the script which gathers the data could be expressed more simply, as it is below:

tell current record
	set fullName to (get cell "Name")
	set nameCount to (count words of fullName)
	set entryAddress to (get cell "Address")
	set entryCity to (get cell "City")
	set entryState to (get cell "State")
	set entryZip to (get cell "Zip Code")
	set entryPhone to (get cell "Phone")
	set entryEmail to (get cell "Email")
	set entryURL to (get cell "Internet")
end tell

Or even more ambitious, albeit maybe a little confusing:

tell current record
	set {fullName, entryAddress, entryCity, entryState, entryZip, entryPhone, entryEmail, entryURL} to {(get cell "Name"), (get cell "Address"), (get cell "City"), (get cell "State"), (get cell "Zip Code"), (get cell "Phone"), (get cell "Email"), (get cell "Internet")}
	set nameCount to (count words of fullName)
end tell

For a related script, go to: Create New Entry in Address Book

Copy and Save FileMaker Image to Preview

Using GUI to Script the Un-AppleScriptable

Sometimes you have a great idea for a script, but you run into a problem with one of the applications that you want to use to implement the script; it does not have an AppleScript dictionary, ie: it is unscriptable.

What do you do ?

When I encounter this problem, the first thing I try, is to see if you can use GUI to do the job. That is exactly what I did below with Preview.

This script takes an image copied from a FileMaker database, pastes it into a Preview document and then saves it to your hard drive under a default name:

Freeze Window
Copy [Select, "GraphicField"]
Perform AppleScript [see below:]
Refresh Window[]

set currentImage to (get cell "GraphicName" of current record)
tell application "System Events"
	try
		if UI elements enabled then --be sure that GUI Scripting is enabled
			tell application "Preview" to activate
			tell process "Preview"
				tell menu bar 1
					tell menu 3 --File menu
						click menu item "New from Clipboard"
					end tell
					tell menu 5 --View menu
						click menu item "Zoom In"
					end tell
					tell menu 9 --Window menu
						click menu item "Zoom"
					end tell
					tell application "Preview" to activate
					tell menu 3 --File menu
						click menu item "Save As..."
						set newImage to currentImage & "_Large"
						keystroke newImage
						keystroke return --this is optional, use this only, if you want to automatically save the image with a default name
					end tell
					tell application "FileMaker Pro" to activate
				end tell
			end tell
		else
			tell application "System Preferences"
				activate
				display alert "Universal Access error number -1719" message "UI element scripting is not enabled. Check \"Enable access for assistive devices\" to continue." as critical
				reveal pane "com.apple.preference.universalaccess"
				error
			end tell
		end if
	on error
		set UI elements enabled to true --proceeds or prompts for a password if one is set
	end try
end tell

This script pretty much explains itself. To test it create a FileMaker database with a cell that can contain an image and give it the script above.

FileMaker Pro: Encrypt Record Data of Database

A script to encrypt data using the reverse keyword

To use this script you’ll need to create a database with global fields named: "Password", "User ID" and "Email Password". This script works like a sort of toggle, because if you run it a second time, it will restore the data back to its original state. It should be easy to see how it could be enhanced to make for a more thorough encryption. A few notes follow the script below:

tell application "FileMaker Pro"
	try
		set encryptCells to {"Password", "User ID", "Email Password"}
		set recordCount to (count record) as integer
		go to first record
		repeat recordCount times
			repeat with x from 1 to 3
				set encryptCell to (get data of cell (item x of encryptCells) of current record)
				if not (encryptCell = "") then
					tell application "Finder"
						display dialog encryptCell
						set Encrypted to (reverse of every character of encryptCell) as string
					end tell
					tell application "FileMaker Pro" to set data of cell (item x of encryptCells) of current record to Encrypted
				end if
			end repeat
			go to record after current record
		end repeat
		display dialog "Data encryption complete" with icon 1 buttons "OK" default button 1
	on error errorMsg number errorNumber
		if not (errorNumber = -128) then display alert "Error number " & errorNumber message errorMsg
		return
	end try
end tell

The important part of this script contains two repeats:

repeat recordCount times
			repeat with x from 1 to 3

The first, repeats for the count of the number of records and the second is for the number of data cells in each record that are to be encrypted.

Also note:

on error errorMsg number errorNumber
		if not (errorNumber = -128) then display alert "Error number " & errorNumber message errorMsg
		return
	end try

This traps for error number -128 (user cancelled) and for all other errors, gives the error number and an explanation of the type of error that occurred which can be very useful when you wish to trap for more specific errors when testing a script.

© 2013 AppleScripter

More by this Author

  • Free AppleScript Code Examples
    1

    Write AppleScript Code,free AppleScripts of display dialog,display alert,choose from list,finder,choose file,choose file name,choose folder,choose application,choose color,Finder,TextEdit

  • Advanced AppleScript Code
    0

    Advanced AppleScripts. For reasons of logic, because the original site was becoming rather large, some of the posts you find here were moved from the primer site for those who are beyond the basics

  • AppleScripts for Graphical User Interface (GUI)
    0

    Graphical User Interface with AppleScript,Free AppleScript code for Apps with no support,System Events,click menu item,click text field,click radio button,click pop up button 1,tell checkbox 1 to


Comments, Suggestions and Bugs 4 comments

Peter Oscars profile image

Peter Oscars 2 years ago

Nice hub, I want to let all Filemaker database users know that if you face corruption in FileMaker databse on Mac, then run Stellar Phoenix Database Repair For FileMaker http://goo.gl/Ho3hG

Do try its free version to preview repaired filemaker files on OS X.


Johnd511 2 years ago

My brother recommended I might like this website. He was totally right. This post truly made my day. You cann't imagine simply how much time I had spent for this info! Thanks! dcgdfekdaafe


Candice Harding 2 years ago

Really helpful! I really appreciate people who take time to help those of us who are a little less technologically literate (like myself) figure things out. I love the concept of Filemaker. I think it's going to prove itself really useful.

Candice Harding

http://www.zerobluetech.com/custom-filemaker-devel...


Radion1 3 years ago

Hi, I'm an Australian FileMaker developer with little AppleScript knowledge. Could you help me with a script ?

david.radion@gmail.com

I would be of course happy to pay a fee.. Thanks David

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working