FileMaker 12 Developers Reference

Bob Bowers is CEO of Soliant Consulting, a 50-person firm that specializes in custom database and web application development. He is a recipient of the FileMaker Excellence Award and has co-authored seven books on FileMaker Pro development. In 2008 he was inducted into the Chicago Area Entrepreneurship Hall of Fame. After graduating summa cum laude in Music and English from Wabash College, Bob earned a master’s degree in Musicology from the University of Chicago. Bob is also a proud dad of two young children, an avid banjo player, and an accomplished woodworker.   Dawn Heady, a Senior Technical Project Lead for Soliant Consulting, has 20-plus years of FileMaker solution architecting experience during which time she’s also been a successful business owner, served as a CIO in the credit restoration industry, and as Vice President of a company specializing in FileMaker plug-ins. She is an authorized FileMaker Training Series instructor, a regular speaker at the annual FileMaker Developer Conference and FileMaker webinars, and a contributor to the FileMaker Newsletter. In 2004 FileMaker selected Dawn as a FileMaker 7 Idol finalist for her Interface-RAD solution. Dawn is an avid fan of the Indianapolis Colts and Indiana Fever, loves to dote on her grandchildren, and is learning to sail her small wooden boat, Boxy Lady, which was handcrafted by her loving husband.   Steve Lane, Chief Technology Officer of Soliant Consulting, served as Special Projects Developer at Scott Foreman, a leading K–6 Educational publisher. He has written for FileMaker Advisor magazine, and co-authored six books on FileMaker Pro. He has led training classes in FileMaker technologies all over the country and is authorized to teach the FileMaker Training Series. He regularly speaks at the annual FileMaker Developer Conference where in 2003 he was awarded the FileMaker Excellence Award for “pushing the boundaries of FileMaker Pro.” When not writing about technology, Steve is likely to be found being chased about by his children or grumbling over the fingerings of J. S. Bach. Or just grumbling.   Scott Love, Chief Operating Officer of Soliant Consulting, served at Ziff-Davis Interactive as an online managing editor, served at Apple Computer as its web publishing technology evangelist, and led the technical marketing team at Macromedia. He has written dozens of feature and review articles on database and Internet/web topics for a wide range of computer publications including MacWorld magazine and FileMaker Advisor. He has coauthored four books: Special Edition Using FileMaker 7, Special Edition Using FileMaker 8, FileMaker 8 Functions and Scripts Desk Reference, and FileMaker 9 Developer Reference. He is also the producer for Grem Legends, an arcade game on the iOS App Store, and has written a technical brief on FileMaker Go. He is an authorized FileMaker Training Series instructor and a regular speaker at the FileMaker Developer Conference. In 2006 he was awarded the FileMaker Excellence Award “for developing outstanding technical and training resources for FileMaker.” Scott lives in the California Bay Area, where his penchant for gardening can be indulged year round.

Introduction: Welcome to FileMaker 12 Developer Reference . . . . . . . . . . . . . . . . . .1

How This Book Is Organized . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2

 Part I: FileMaker Specifications . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . .2

 Part II: Layout Tools & Objects . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2

 Part III: Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2

 Part IV: Script Steps & Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2

 Part V: FileMaker Go . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . .3

 Part VI: FileMaker Connectivity . . . . . . . . . .  . . . . . . . . . .3

 Part VII: Quick Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

 Part VIII: Other Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

Special Features . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . .4

Typographic Conventions Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

 Key Combinations and Menu Choices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

 Typographic Conventions Used for FileMaker Scripts . . . . . . . . . .  . . . . . . .5

Who Should Use This Book . . . . . . . . . .  . . . . . . . . . . . . . . . .5

 

Part I: FileMaker Specifications

 



1 FileMaker 12 Product Line . . . . . . . .  . . . . . . . .7

FileMaker Pro 12 . . . . . . . . . . . . . . . . . . . . . . . . . .7

FileMaker Pro 12 Advanced . . . . . . . . .. . . . . . . . . . . .7

FileMaker Server 12 . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8

FileMaker Server 12 Advanced . . . . . .  . . . . . . . . . .8

FileMaker Go 12 . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . .9

FileMaker Go 12 for iPad . . . . . . . . . . . . . . . . . . .9

 



2 FileMaker 12 New Features . . . . . . . . . . . . .11

New File Format . . . . . . . . . . . . . . . .. . . . . . . . .11

FileMaker Pro/Advanced 12 . . . . . . . . . . . . . . . . . . . . .12

 Themes . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . .12

 Enhanced Container Fields . . . . . . . . . . . . .  . . . . . . .13

 Enhanced Charting Features . . . . . . . . . .  . . . . . . . . . . . . . . . .14

 Advanced Window Styles . . . . . . . . . . . . . . . . . . . . .14

 Plug-in Updating . . . . . . . . . . . . . . . . . . . . . . . . .15

 ExecuteSQL . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . .15

 And More… . . . . . . . . . . . . . .  . . . . . . . . . . .16

FileMaker Server/Advanced 12 . . . . . . . . . . . . . . . . . . . . . . . .17

 Improved WAN Performance . . . . . . . . . . . . .17

 64-bit Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .17

 Rewritten Web Engine . . . . . . . . . . . . .. . . . . .17

 Backup Improvements . . . . . . . . . . . . .  . . . . . .18

 Managed Container Fields . . . . . . . . . . . . . . . . . . . . . . . . . .19

 Progressive Media Download . . . . . . . . . . . .  . . . . . . . . . . .20

 Group Start Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20

FileMaker Go/Go for iPad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

 Multitasking Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

 Improved Media Integration . . . . . . . . . . . .  . . . . . . . . . . . . .21

 Export Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22

 Location Functions . . . . . . . . . . . . . . . . . . . . . .22

 



3 Specifications and Storage Limits . . . . . . . .. . . . . . . . . . . . .23

Knowing Your Limits . . . . . . . . . . . . . . . . .  . . . . .23

External SQL Sources (ESS) Specifications . . . . . . . . . . . . . . . . .25

 



4 Field Types and Import/Export Formats . . . . . . .. . . . . . . . . . . . .27

FileMaker Field Types . . . . . . . . . . . . . . . . . . . . . . . .28

 Text . . . . . . . . . . . . .  . . . . . . . . . . . . . .28

 Number . . . . . . . .. . . . . . . . . .29

 Date . . . . . . . . . . . . . . . .  . . . . . . . . . .29

 Time . . . . . . . . . . . . . .. . . . . . . . . . .30

 Timestamp . . . . . . . . . . . . . . . . . . .31

 Container . . . . . . . . . . . . . . . . . . . . .31

 Calculation . . . . . . . . . . . . . . . . . . .34

 Summary . . . . . . . . . . . . . . . . . . . . . .34

 Additional ESS References . . . . . . .. . . . . . . . . . .35

Importing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35

 File-Based Data Formats . . . . . . . . . . . .  . . . . . . . . . .35

 Importing Multiple Files at Once . . . . . . . . .  . . . . . . . . . . . . . . . .36

 Importing from an ODBC Data Source . . . . . . . . . . . . . . .37

 Importing from an XML-Based Data Source . . . . . . . . . . . . . . .38

 Creating New Tables on Import . . . . . . .. . . . . . . . . .39

Exporting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . .39

 Exporting to HTML . . . . . . . . . . . . . . . . . . .39

 Exporting to FileMaker Pro . . . . . . . . . . . . . . . . . . . . . .39

 Exporting to Excel . . . . . . . . . . . . . . . . . . . . .40

 Exporting to XML . . . . . . . .. . . . . . . . . . . . .40

 Automatically Opening or Emailing Exported Files . . . . . . .. . . . . . . . . .40

 

Part II: Layout Tools & Objects

 



5 Layout Tools . . . . . .. . . . . . . . . . . .41

About Layouts . . . . . . . . . . . . . . . . . . . .41

 The Layout Setup Dialog . . . . . . .. . . . . . .41

 Layout Themes . . . . . . . . . . . . . . . . . .43

 Organizing Layouts . . . . . . . . . . . . . . . . . . .44

The Inspector . . . . . . . . . . . . . . . . . . . . . . . .45

 Position Tab . . . . . . . . . . . . . . . . . . . . . .45

 Appearance . . . . . . . . . .. . . . . . . . . .50

 Data . . . . . . . . . . . . . . . . . . . . . . . . . . . .53

Additional Object Formatting . . . . .. . . . . . . . . . . . . . .56

 Button Setup . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . .57

 Conditional Formatting . . . . . .  . . . . . . . . . .57

 Script Triggers . . . . . . . . . . . . . . . . . . . . . . .58

Accessibility Inspector . . . . . . . . . . . . . .58

 



6 Charting . . . . . . .  . . . . . . . . . . . . .59

Creating Charts . . . . . . . .. . . . . . . . . . . .59

 Quick Charts . . . . . . . . . . . . . . . . . . . . . . . . . . .60

 The Chart Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61

Specifying a Chart’s Data Source . . . . . . . .  . . . . . . . . . . . . . .61

 Current Found Set — Summarized Groups of Records . . . . . . . . . . . . . . . . . .62

 Current Found Set — Individual Record Data . . . . . . . . . . . . .62

 Current Record (Delimited Data) . . . . . . . . . . . . . . .64

 Related Records . . . . . . . . . . . . . . . . . . .64

Chart Types . . . . . . . . . . . . . . . . . . . . . . . . . . . .65

Chart Styles . . . . . . . . . . . . . . . . . . . . . . . . . . .66

Tips for Effective Charting . . . . . . . . . . . . .. . . . . . . . . . . . .67

 



7 Other Layout Objects . . . . .  . . . . . . . . .69

Tab Controls . . . . . . . . . . . . . . .  . . . . . . . .70

 Formatting a Tab Control . . . . . .. . . . . . .71

 Autosizing of Tab Controls . . . . . . . . . . . . . . . . . .72

 Other Tab Control Notes . . . . . . . . . . . . . . . .72

Portals . . . . . . . . .. . . . . . . . . . . . . . .73

 Portal Setup . . . . . . . . . . . . . . . . . . . . . . .73

 Working with Records in a Portal . . . . . . . . . . .75

 Autosizing of Portals . . . . . . . . . . . . . . .76

 Other Notes About Portals . . . . . . . . . . . . . .76

Web Viewer . . . . . . . . . .. . . . . .77

 Setting a Web Viewer via Script . . . . . . . . .79

 Other Web Viewer Notes . . . . . . . . . . . . . .  . . .79

 

Part III: Functions

 



8 Calculation Primer . . . . . . . . . . . . . . . . . . . . . .81

The Calculation Function Interface . . . . . . . . . . . . . . .81

Calculations: Things to Remember . . . . .. .82

 



9 Calculation Signatures . . . . . . . . . . .87

Aggregate Functions . . . . . . . . . . . . . . .87

Container Functions . . . . . . . . . . . . . . . .87

Date Functions . . . . . . . . . .. . . . . . . . . . .88

Design Functions . . . . . . . . . . . . . . . . . . . . . .88

External Functions . . . . . . . . . . . . . . . . . . . . .89

Financial Functions . . . . . . . .. . . . . . . . . . . .89

Get Functions . . . . . . . . . . .. . . . . . . . .90

Logical Functions . . . . . . . .. . . . . . .92

Mobile Functions . . . . . . . . . . .. . . . . . . . . . . .93

Number Functions . . . . . .  . . . . . . . . . . . . . . . . .94

Repeating Functions . . . . . . . . . .. . . . . . . .94

Summary Function . . . . . . . . . . . . . . . . . .95

Text Functions . . . . . . . . . . . . . . . . . . . . . .95

Text Formatting Functions . . . . . . .  . . . . . . . . . .96

Time Functions . . . . . . . . . . . . . . . . . . . . . . .97

Timestamp Function . . . . . . . .. . . . . .97

Trigonometric Functions . . . . . . . . . . . . .97

 



10 Calculation Functions . . .  . . . . . . . . . . . . . . . . . . . .99

 





11 Custom Function Primer . . . . . . .  . . . . . . . . . .273

The Custom Function Interface . . . . . . . . . . . . . . . . . . .274

Custom Functions: Things to Remember . . . . . . . . .  . . .274

Recursive Techniques . . . . . . . . . .. . . . . . . .276

 

Part IV: Script Step Reference

 



12 Scripting Primer . . . . . . . . . . . . . . . . . .279

The Manage Scripts Interface . . . . . . . . . . . . . . . . .279

Where Scripts Live . . . . . . . . . . . . . . . . . . .280

Editing or Creating Scripts . . . . . . . . .  . . . . . . .281

Organizing Scripts . . . . . . . . . . . . . . . . . .281

Filtering Scripts . . . . . . . . . .  . . . . . . . .282

Script Privileges . . . . . . . . . . . . .. . . . . . . . .282

Debugging Scripts . . . . . . . . . . . .. . . . . . . . . .282

 Scripting for the Web . . . .. . . . . . . . . .284

 Scripting for FileMaker Go . . . . . . . .  . . .285

 Server Scheduled Scripts . . . . . . .  . . . . . . .285

 



13 Script Triggers . . . . . . . . .. . . . . . . . . . . . . .287

Setting Script Triggers . . . .. . . . . . . . . . . . . .287

 File Events . . . . . . . . . . . . . .. . . . . . .287

 Layout Events . . . . . . . . . . . . . . . . . . .288

 Object Events . . . . . . . . . .. . . . . . . .289

Activating Script Triggers . . . . . . . . . . . . . . . . . . . .290

Timing of Script Triggers . . . . . . . . .. . . . . . .291

Scripting for Script Triggers . . . . . . . . . . . . . . . . . .293

Tab Control Objects . . . . . . . . . . . . .. . . . . . . . . . .294

Instant and Custom Web Publishing . . . . . . . . . . . .294

Script Trigger Reference . . . . . . . . . . . . . . . . .295

 



14 Script Step Reference . . . . . . . . . . . . . . .311

 

Part V: FileMaker Go

 



15 FileMaker Go Specifications . . . .. . . . . . . . . . . . . .431

Device Compatibility . . . . . . .  . . . . . . .431

 FileMaker Go 12 . . . . . . . . . . . . . . . . . . . . . .431

 FileMaker Go 12 for iPad . . . . . . . . . . . . . . . . . . . . .431

Differences Between FileMaker Go and Pro . . . . . . .. . . . . . . . . .432

 Limits . . . . . . . . . . . . . . .432

 Text Fields . . . . . . . . . . . . . . . . . . . . . . . . . .432

 Container Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . .433

 Interface Feature Differences . . . . . . . . . . . . .433

 Export Records . . . . . . . . . . . . . . . . . . . . .436

 Saving Records as PDF . . . . . . . . . .. . . . . . . .436

 Script Step Differences . .. . . . . . . . . . . . . . . . . . . .437

 Security . . . . . . . . . . .  . . . . . . . . . . . . . . .438

 Plug-ins . . . . . . . . . . . . . . . . . . . . . . . . . .439

 



16 Designing for FileMaker Go . . . . . . . . . . . . . . . . .441

File Architecture . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . .441

 Model 1: An Integrated File . . . . . . . . . . .. . . . . . . . . . . . . . . .442

 Model 2: A Separate Hosted File . . . . . . . . . . . . . . . .443

 Model 3: A Separate Local File . . . . . . . . . . . . . . . . .444

 Model 4: Hybrid . . . . . . . . . . . . . . . . . . . . . . . .444

Designing Layouts for FileMaker Go . . . . . . . .  . . . . . . . . . . . .444

 Disabling Pinch-to-Zoom . . . . . . .. . . . . . . . . . . . . . .446

 Object Sizes . . . . . . . . . . . . . .. . . . . . . . . . . . . .447

 Font Families . . . . . . . . . . . . . . . . . . . . .447

 Other Layout Tips . . . . . . . . . . . . .. . . . . . . .448

Optimizing Workflows . . . . . . . . . . . . . . . . . . .448

 Minimalist Approach . . . . . . . . . . . . . .. . . . . . . . . .449

 Server-side Procedures . . . . . . . . . . . . . . . . . . .451

 Export Procedures . . . . . . . . . . . . . . . . . . . . . .451

 Additional Performance Tips . . . . . . . . . . . . . . . . .452

Deploying Files . . . . . . . . . . .  . . . . . .452

 Email . . . . . . . . . . . . . . .. . . . . . . . . .452

 iTunes . . . . . . . . . . . . . . . . . . . . . . . .452

 Web Download . . . . . . .. . . . . . . . . . . . .453

 Container Field . . . . . . . . . .  . . . . . . . . .453

Sync Strategies . . . . . . . . . . . .. . . . . . . . . . . . . .453

 One-way Synchronization . . . . . . . . . . . . . . . .454

 Two-way Synchronization . . . . . .  . . . . .456

Transactional Model . . . . . . . . . . . . . . . . . .457

 Portal Transaction . . . . . . . . . . . . . . . . . . . .457

 Text Block Transfer . . . . . . . . . .  . . . . . . . . .458

Security Plan . . . . . . . . . . . . . .. . . . . . . . .459

 Secure Connections to FileMaker Server . . . . . . . . . . . . . .461

 Implications of the Background Behavior . . . . . . . . . . .461

 Security Tips . . . . . . . . . . . . .. . . . . . . . .462

Interacting with Other Apps . . . . . . .  . . . . . . . . . . .463

 FileMaker URL Scheme . . . . . . . . . . . .463

 Maps and Location . . . . . . .. . . . . .464

 Dial Phone . . . . . . . . . . . . . . . .466

 

Part VI: Connectivity

 



17 FileMaker XML Reference . . . . . . . . . . . . . . .467

URL Syntax for Web Access to FileMaker Data . . . . . . . . . . . . . . . . . .467

 URL Syntax for XML Access . . . . . . . . . . .  . . . . . . .467

 URL Syntax for Access to Container Objects . . . . . . . . . . . . .468

FileMaker XML Grammars . . . . . . . . . . . . . . . .. . . . . .469

Query Parameters for XML URL Requests . . . . . . . . . . . . .472

 Query Commands . . . . . . . . . .. . . . . . .473

 Query Parameters . . . . . . . . . . . . .474

FileMaker XML Namespaces . . . . . . . . . . . . . .478

 



18 FileMaker API for PHP . . . . . . . . . . . . .479

Class Overview . . . . . . . . . . . .  . . . . . . . . .479

Classes and Methods . . . . . . . . . . . . .480

 FileMaker . . . . . . . . . . . . . . . . . . . . . . . . . . . . .480

 FileMaker_Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .487

 FileMaker_Command_Add . . . . . . . . . .. . . . . . . . . . . . . . . . . .490

 FileMaker_Command_CompoundFind . . . . . . . . . .  . . . . . . . . . . . . . .490

 FileMaker_Command_Delete . . . . . . . .  . . . . . . . . . . . . . . .492

 FileMaker_Command_Duplicate . . . . . . . . . . . . . . . . . . . . . . . . . .492

 FileMaker_Command_Edit . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . .493

 FileMaker_Command_Find . . . . . . . .