Piyomaru Software Books #015
Version 1.3-English Edition
macOS 10.14.6/10.15.7/11.1〜
FileMaker Pro v19

SAMPLER
macOS Script-able application guide book series①

FileMaker Pro
Scripting Book

TM

Supplements:①Sample Scripts in this book②Contexetual menu helper scripts"Piyomaru Script Assistant" (Japaneseversion)with AppleScript

TM

Author: Takaaki Naganoya
Technical Supervisor: Masayuki Nii

A scripting guide for working with FileMaker Pro & AppleScript

Trademarks and Copyrights notice

FileMaker, FileMaker Pro and FileMaker Pro Advanced are the registered trademark of Claris International Inc. in UnitedStates and other countries in the world.
Mac, Macintosh, Mac OS X, OS X, macOS, AppleScript and Objective-C are the registered trademark of Apple, Inc. in UnitedStates and other countries in the world.
Used textures（From Vecteezy）：
I use these textures under licencing agreement. Each copyrights are belongs to each authors.

Kirsty Pargeter

channelart

Zein Republic Studio

wngstdSome illustrations and textures are from the book "Girl’s Design Materials" by Mitsuko haino and her web site (http://
girlysozai.com). I bought her book and use materials included in the supplement CD.
Copyrights of this book is holded by Takaaki Naganoya, Piyomaru Software.

It's a FileMaker Pro + AppleScripting time!
FileMaker Pro provides a unique scripting language for automating your dayto-day operations. If your day-to-day work is done only within FileMaker Pro, it
may be sufficient to use FileMaker Pro's own scripts.
However, if you are using other applications with FileMaker Pro (such as
M a i l . a p p , S a f a r i , Tex t E d i t , o r y o u r o w n a p p l i c a t i o n ) , y o u s h o u l d a d dAppleScript as a great partner for FileMaker Pro. That's it!
Your future isn't just about adding FileMaker Pro to one of the applications
called by AppleScript.
Surprisingly, you can also call the macOS Cocoa API from within FileMaker
Pro via AppleScript.
This fact means that you have the option of distributing a ".fmp12" file
packed with everything you can do on your Mac.

Preface"AppleScript" is a scripting language that operates
GUI applications for macOS, and assumes theexistence of GUI applications.
So in this book, I explain how to operate the target
application from AppleScript in an easy-to-understandmanner, using a lot of screen snapshots.
First of all, run the GUI application from AppleScript,
grasp the operation image with your own eyes, andhave fun.
The contents of this book have been confirmed on
macOS 10.15.7 (English user environment) +FileMaker Pro v19. We have also verified as much as
possible in macOS 10.14.6 and 11.2 environments.
▲ AppleScript's lexial color & font formattingin this book (Author's environment)About this book
This book is a comprehensive explanation of the description of controlling Claris' database application"FileMaker Pro" from AppleScript, with an emphasis on the basics.
The difficulty level of each article is labeled as "Beginner", "Intermediate", and "Advanced" so that thecontent does not suddenly become difficult, but there are some exceptions difficulty level is increasing.
Please refer to the difficulty level display of each article.
This book is intended for the client version of FileMaker Pro and assumes that you have a basic
understanding of the operation. It include only the minimum part of cooperation with Server and Cloud.
For an explanation of AppleScript itself and detailed usage of the script editor, refer to Script Editor's help
or AppleScript Language Guide.
I'm a Macintosh solution developper in Japan. My main programming language is AppleScript. Do you
know the first Siri-like system on Mac was written in AppleScript? Yes, we wrote it in 2002. I wrote various
systems by AppleScript and sell various AppleScript written applications on Mac App Store now.
The first edition of this e-book is written in Japanese language, my mother tongue. Oneday, some user on
twitter told to me "Is there English version?" So, I translated my book to say "Yes". Though some sentenses
may be strange for native speakers, AppleScript itself is a kind of Universal Language. It runs everyhere.
Enjoy!

Beginner

LevelBeginner

Intermediate

Advanced

About
FileMaker Pro
The function of the database software FileMaker Pro is to collect data and organize it inan easy-to-use manner. By associating more data than just one type of data, you cancreate a more powerful and easy-to-use database and use it immediately!
Here's a quick introduction to FileMaker Pro. It's a basic content that is unnecessary for
those who know it, but it also contains important content, so please take a look.

FileMaker Pro, a database application
Flexible layout and styled text handling RDB

A database is a collection of information (data) that can be shared,
updated, sorted, searched, printed etc.
FileMaker Pro is a cross-platform relational database whose databse
contain multiple tables in one database document file.
It have flexible layout making function and its text filed can store multistyled and colored text like a word processors.
FileMaker Pro database can be shared with iOS device, Windows PC
and Mac.
Originally, FileMaker Pro is made for classic MacOS. It is the most maclike application, I think and I love it.
Product Name

FileMaker Pro

FileMaker Go

FileMaker Server

FileMaker CloudSupport OS
platforms

macOS, Windows

iOS, iPadOS

macOS, Windows,
Linux(Unknown)

LevelBeginner

Intermediate

Advanced

LaunchScript Editor
The program that writes, saves, and executes AppleScript is the "Script Editor". It is
included on all Macs. It's kind of like a quirky text editor, where you can save AppleScript
documents or even save them as an application.

Launch Script Editor!
/Applications/Utilities①Move to /Applications/Utilities folder

Type in your AppleScript
here

Launch Script Editor

Script Editor displays result
and so on③This is the AppleScript editor & executer application, "Script Editor".
Until macOS 10.15②Launch Script Editor

macOS 11.0 or later

Detailed information is available in "Script Editor Help" in help menu.

LevelBeginner

Intermediate

Advanced

Security Settings
in macOS
The function to control applications on macOS from AppleScript requires authentication
only for the first time using by "Security & Privacy" in System Preferences in macOS
10.12 or later.
FileMaker Pro v19 is not a "sandboxed" application. So, controlling FileMaker Pro does
not require authentication, but if you want to control other applications, you need toknow about authentication mechanism.

"Security & Privacy" pane
System Preferences has AppleScript related privacy settings
macOS 11.2

macOS 10.15.7"Privacy" settings on recent macOS versions
10.14

10.15

11.0

Automation

●

●

●

Full Disk Access

●

●

●

●

●

●

●

●

●

Files and Folders
Accessibility●

Developer

macOS 10.14.6

Bluetooth●

HomeKit●Media & Apple Music●

Screen Recording●

Input Monitoring●●

Speech Recognition●●

Microphone

●

●

●

Camera●●●Photos●●●Reminders●●●

Calendars●●●Contacts●●●

Location Services

●

●

●

Analytics & Improvements

●

●

●

Advertizing / Apple Advertizing

●

●

●

Important privacy settings
These items are AppleScript related settings

Important privacy settings
Privacy

Description

Automation

Allow control other apps from AppleEvent. If you control
other applications, this setting is required at the first time

Full Disk
AccessAllow to apps to access data like Mail, Messages, Safari
and certain administrativesettings for all users. If yourAppleScript within FileMaker Pro access these resources,
you have to add FileMaker Pro to this setting

Allow the apps to access files and folders. If yourAppleScript within your FileMaker Pro want to access
Files & Folders
local files or folders, you have to register FileMaker Pro to
this.

Set this right now

1. Select
3. Add "Script Editor"

Mainly for disabilities. AppleScript has functions tocontrol from menus and buttons. Very important.
Accessibility

Register Script Editor andFileMaker Pro, at first.

2. UnlockLevelBeginner

Intermediate

Advanced

Set-up "Extended
Privileges"
In order to control FileMaker Pro from AppleScript, you need to allow the extended accessright "Allow Apple Event and ActiveX to perform FileMaker operations (fmextscriptaccess)" on
FileMaker Pro. Please make this setting first for each database document.

Add Extended Privileges to FMDB①
By default, we can not control FileMaker Pro application from AppleScript
File > Manage > Security...

You have to set up with each database document to control FileMaker Pro from AppleScript

LevelBeginner

Intermediate

Advanced

Launch FileMaker Pro
From AppleScript
The easiest exercise to control FileMaker Pro from AppleScript is to start and stop
FileMaker Pro application. It's easy, but you can feel of the conversation between
application and AppleScript.

Two control styles
You can control FileMaker Pro by using AppleScript from inside or from external program①Control from external program such as Script Editor②Control from internal script step "Execute AppleScript"

This style is easier
I'll recommend this at the first time

This style is more practical.
In most of case, we use this.

Caution 1:

Caution 2a:

Caution 2b:

In macOS 10.14 or later, you will be requested Security &Privacy authentication to control another application at
the first time. Script Editor is a kind of wildcard
application to control another. It never requested auth.
But if you export your script to application or run from
Script Menu, you'll have a experience to auth.

FileMaker Pro's scripting
keywords may conflict yourscript's variables and properties.
If you meet such error, you haveto sandwitch the confliced
keyword by "|". For example,"ascending" goes "|ascending|".

FileMaker Pro v18 or earlier
version has "Advanced" version.
It requires to another application
name. So youhave to identify
current FileMaker application

LevelBeginner

Intermediate

Advanced

Open Database
This is the story of opening a FileMaker Pro database document to get information. You
will experience the ability to retrieve a variety of information that cannot be seen on theGUI.

Name and extension of document
Get the name of opening database to close unnecessary database, mainly
Returns name of all opening documents

1

If there is no document,
this script returns nullarray, { }.

23every documentname extension is included

Some application get eﬀect from Finder setting"Show all filename extensions". If Finder display
The result of getting name of FileMaker Pro
database is list (array). You can check databaseexistence by counting this result items array.

filename extension, iWork applications return
document name with filename extension and viseversa.
FileMaker Pro is *not* aﬀected by this Finder
setting.

LevelBeginner

Intermediate

Advanced

Open Remote
Database
FileMaker Pro v19 has a "peer-to-peer sharing" database sharing feature between
clients, but it has been announced that this feature will be deprecated. AppleScript canalso open databases on FileMaker Server.

3 types of servers
FileMaker Pro has three database sharing methods
Server① (FileMaker Pro Client)Peer to peer database sharing

Server② (FileMaker Server)

Client/Server (With SSL encryption)

Server③ (FileMaker Cloud)Client/Server (With SSL encryption)

IntermediateLevelBeginner

Intermediate

Advanced

Get info fromtables
You can store multiple tables in a FileMaker Pro database document.
Though, you can't create a new table from AppleScript, you can get information fromvarious type of field in a table.

Get table info from document
FileMaker Pro document contains multiple tables
Get name of every tables of current databaseGet name of current displaying table

The first opened database is the target
The first opened database is the target
The target is the table associatedto the current layout

name of every table

synSub_2

synSubC

LevelBeginner

Intermediate

Advanced

Change layout
Layout switching is a very important operation in the operation of FileMaker Pro.
Originally, the layout switching itself shouldn't make much sense.
Fields accessed from AppleScript must exist on the layout being displayed.
So, you need to switch to the layout where the fields you want to access.

Check the existence of the layout
Specifying a non existing layout cause an error. You have to check the existence of the layout before.
Get the name of current layout

Check the existence of the layout

LevelBeginner

Intermediate

Advanced

Get current
recordIf you want to work on a table in the database, the "identify the currently selected record"
method and the "target a specific record" method are very important.

Get current record
About the definition of "currently selected record" that can be confirmed on the screen of FileMaker Pro
Number of records currently displayed / selected
CASE: View as Form

Currently selected record number

Contents of the currently selectedrecord

CASE: View as Table
Currently selected record number
Currently selected record
Only the selected record (row) has changed color in gray

LevelBeginner

Intermediate

Advanced

Get / set info
from various fields
This chapter describes how to populate and retrieve data from fields on FileMaker Pro
documents.
You will learn how to specify field names and how to handle different types of fields.

Kinds of field
FileMaker Pro's database's field types
Types

Data

Text

Text (plain text, styled text)

NumberAccess from AppleScript
Read/Write, Styled Text have to be set via
clipboard

NumberRead (result is text) /Write

Date

Date Text (YYYY/MM/DD)

Read/Write

Time

Time Text (hh:mm:ss)

Read/Write

Time stamp

Time stamp format text (YYYY/MM/DD hh:mm:ss)

Read/Write

Container

Image, PDF, Movie, CoreML model, fmp12

Read/Write

Calculation

Formula string

Read Only

If you put an image in the container field, there are two patterns, one is the image itself
and the other is the reference to the image file.
You can specify global storage, repeating field (array storage), etc. for each field. You will
be manipulating field definitions from the FileMaker Pro GUI side. From the AppleScript
side, fields cannot be defined.

LevelBeginner

Intermediate

Advanced

Get current records
in current view
If the records in the database are conditionally selected by a search command, etc., therange of processing will be expanded if the target records in the selected state can beacquired.

Current records in the current view
About the definition of current selected records
All = 10 records

Number of refinements = 4
records

record 1
record 2
record 3

Number of refinements = 4
records

record 4
record 5
record 6
record 7
record 8
record 9record 10

View

All = 14 records

LevelBeginner

Intermediate

Advanced

Make new recordand set data
You create a new record and populate a table in your FileMaker Pro database.

Create new record
Create new record on the table which is binded with current layouttell application "FileMaker Pro"

Precondition for deletiontell window 1
set nRec to create new record--> record id 21.0 of window "repetition" of application

At first, you have to open the target
database with FileMaker Pro"FileMaker Pro"end tellView mode must be "Browse Mode"end tellCreate new record and get the record id of it

tell application "FileMaker Pro"
tell window 1
set nRec to create new record--> record id 21.0 of window "repetition" of application"FileMaker Pro"set anID to ID of nRec--> 21.0
end tellend tellLevelBeginner

Intermediate

Advanced

Sort a table
You perform a sort on a table in a FileMaker Pro database with a reference field and sort
direction.

Table sorting (from GUI)
Sorting the target table by some sorting key field
Precondition for sorting

Target database document is already opened by FileMaker
Pro

Sorting key fields exist on the current layout in the
database
Records in the database exist at least one or moteView mode is "Browse Mode"
Sorting is eﬀect with records in filtered view
You can check sorting or find status on GUI

LevelBeginner

Intermediate

Advanced

Simple find
You do a data search. You don't just have to do the search, you do the search after
you're ready to see all the records.
Also, if there is no record that matches the search conditions, an error will occur, andyou need to use an "error trap" that ignores the error.

Easy find command = show
Simple srarch by using AppleScript's filter reference①Display the target layout ( 's associatedtable)②Show every record in the target DB

document

window
document③Execute find with "show" command
Error trap (try.....on error.....end try)
An error will occur if the corresponding record does not exist in ③. So,
it is very important to prepare the error

window

If the target database is extremely large or the network connection isvery slow, the request does not end within 180 seconds. In such a
condition, you have to prepare the error by extending timeout④Get the result

windowtell application "FileMaker Pro"
tell document 1
show layout "repetition" --①
end telltell window 1
show every record --②
end telltell document 1
tryshow (every record whose cell "num2" = 2) --③
on error
return false
end try
end telltell window 1
set idList to ID of every record --④
if class of idList is not equal to list thenreturn {idList}
else
return idList
end ifend tellend tell

If the result of ④ is one record, it returns as one id number.

If the result of ④ is two or more record, it returns in array.

Technical Supervisor's Afterword
The functionality of FileMaker is improving year by year, and FileMaker can cover a broad range of your demands. Now FileMaker Inc.
moved to the Claris Inc. and they offers Internet-to-Internet integration solutions with Claris Connect. On the other hand, althoughAppleScript got started on 90's which Mac OS was the main stream, recently it has become less of a hot topic in the current macOS.

What can you realize with the combination of FileMaker & AppleScript? The answer is that you can do what FileMaker can't do. In otherwords, AppleScript widely expands the possibility of FileMaker. AppleScript can effectively control the user environment of the OS. The
current Claris Connect can integrate over the Internet, while AppleScript is a technology that has long been able to work together withmultiple applications within a single Mac. Why don't you leverage such a technology?

We hope that the information in this book and AppleScript-enabled applications is going to help you do things we have never thoughtpossible.
Masayuki Nii,

February 3rd, 2021

Author's Postscript
I'm a solution developer who combines Apple products to provide solutions. Above all, I use AppleScript to build the system. The
latest AppleScript is an environment where you can write powerful programs by combining local applications, Cocoa API, andREST API on the Web. I develop a number of applications with AppleScript and sell them on the Mac App Store.
The other day, a FileMaker Pro program contest was held in Japan, and after a long time I installed FileMaker Pro and tried tomake a contest entry work. WebViewer, CoreML....I was surprised at how easy it was to use incredibly flashy technology.
This is FileMaker Pro v19 with 3 dimensionalrotate menu. This menu displays users colortext set bookmarks. This menu uses three.js
JavaScript Library on CDN.
This DB's search function uses thesaurus
database converted to FileMaker Pro format
and its entry words pairs are over 90
thousands. Thesaurus search function works
with AppleScript.
Watch Demo Movie
on YouTube

This DB has 14 thousands of color set & keyword pairs. Users cansearch by favorite keywords.
It has color preview function which use SVG data to preview color
set and has auto color scheming function by calculating color
brightness data and fit color set's data to SVG template
considering original graphic's color charactaristic. This auto color
scheming function is full written in AppleScript.
Though I omitted from the contest entries, I also created the
Adobe Swatch Exchange file export function with AppleScript. So,
this database enables that users search and examine color sets byfavorite keyword, apply color set to SVG template and export it as
Adobe Illustrator's palette immediately.

At randomcolor

Auto coloring
by AppleScript

Technical supervisor Dr. Masayuki Nii is a very well-known IT architect in Japan and uses FileMaker Pro products for various
usage. He gave me valuable advices about latest FileMaker product information.
I want to give this word with gratitude, "This is it! My contest entry work is the "things they never thought possible".
Takaaki Naganoya

February 3rd, 2021

macOS Script-able application guide book series①“FileMaker Pro Scripting Book with AppleScript” English Edition
Publish date: Feb. 8th, 2021
Author:First Edition

Takaaki Naganoya@Piyomaru Software (Twitter: @Piyomaru)

Technical Supervisor:

Masayuki Nii

Discussion:

Akiyasu Takamori

Translator:

Takaaki Naganoya

Designer:

Takaaki Naganoya

Publisher:

Piyomaru Software

Email about this book:maro@piyocast.com

Authoring application:

Keynote v10.1, Double PDF v2.0

Hex figs making app:

Kamenoko v1.1

Used AppleScript:"PDF with TOC generator from Keynote document v3.6","English text item on Keynote ratio calculator", "Font replacer for Keynote v2""AppleScript RTF exporter", "Keynote Font Replacer" ,"Keynote Font Finder","Keynote slide title gatherer", "Keynote Font explacer v2 with table interface"
Support Page:
http://piyocast.com/as/asinyaye/books/

Author's Blog
http://piyocast.com/as/

Author's YouTube Channel:
https://www.youtube.com/channel/UCxeYR7OkI3dn6nKnu1_5oPA

Piyomaru Software Book Storehttps://piyomarusoft.booth.pm