Using FileMaker Pro

The script shows 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 below.

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

Contact me at hyperscripter@gmail.com or http://twitter.com/hyperscripter or to subscribe, click the By Email link at the top of the page.

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.

Contact me with any questions or comments you may have and I will be glad to help (where I can): hyperscripter@gmail.com or http://twitter.com/hyperscripter or to subscribe, click the By Email link at the top of the page.

AppleScript to Password-Protect FileMaker Records

Create a global field named ‘accessPass’ to store your password.

Create a script in the 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 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"
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

Questions or comments are always welcome. Contact me at hyperscripter@gmail.com or http://twitter.com/hyperscripter or to subscribe, click the By Email link at the top of the page.

A Script to Create a New Record

This is a script, which combines FileMaker Pro script steps with some AppleScript Code statements. 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"})
end if
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’.

Questions or comments are always welcome. Contact me at hyperscripter@gmail.com or http://twitter.com/hyperscripter or to subscribe, click the By Email link at the top of the page.

A little scriptlet for formatting fields to upper case:After placing the ‘Freeze Window’ script step, enter any number of ‘Replace Contents [No Dialog,"Name","Upper(Name)"]‘ script steps to convert text fields to upper case text (where both instances of Name are replaced with the actual field name that you are using in your database). This is very useful when you forget to hit the Caps Lock key before you start typing.

Alphabetical List Dialog of Records

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

Type this into the ‘Perform AppleScript’ script step window:

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≠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 steps at the beginning and end 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.

If you have comments or advice on this or any of my other posts, or would like to suggest another topic, you can contact me at:

hyperscripter@gmail.com or http://twitter.com/hyperscripter or to subscribe,
click the By Email link at the top of the page.

FileMaker Pro 6 Custom Dialog Script

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 ends 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: hyperscripter@gmail.com or http://twitter.com/hyperscripter or to subscribe, click the By Email link at the top of the page.

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:

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.

If you have comments or need advice on this or any of my other posts, you can contact me at hyperscripter@gmail.com or http://twitter.com/hyperscripter or to subscribe, click the By Email link at the top of the page.

Find Request FM Pro 6 | Find Request FM Pro 9 | Password-Protect FM Records
| Create a New Record
| Alphabetical List Dialog of Records
| Custom Dialog
| Show Message Dialog | Back to Top