FileMaker Pro 10: The Missing Manual

Table of Contents......Page 6
About the Creative Team......Page 18
The Missing Manual Series......Page 19
Introduction......Page 22
Why FileMaker Pro?......Page 24
What’s New in FileMaker Pro 10......Page 25
The Very Basics......Page 29
About the Outline......Page 30
Living Examples......Page 31
About › These › Arrows......Page 32
Safari® Books Online......Page 33
A Very Quick Database Tour......Page 36
Scroll Bars......Page 37
Understanding Modes......Page 39
Opening and Closing Database Files......Page 40
Closing a Database......Page 42
Entering Data......Page 43
Fields for Lots of Text......Page 44
Reverting a record......Page 45
Deleting Records......Page 46
No records present......Page 48
Navigating Your Database......Page 49
Navigating Record by Record......Page 50
Keyboard Shortcuts......Page 51
Finding Records......Page 52
Performing a find......Page 53
FastMatch......Page 54
Accidental data entry in Find mode......Page 55
Sorting Records......Page 56
Understanding Sorting......Page 57
Multiple Sort Fields......Page 58
Same Database, Different Views......Page 59
Viewing a Table......Page 60
Hiding Windows......Page 61
Automatically Arranging Windows......Page 62
Saving Your Database......Page 64
Saving a Copy of Your Database......Page 65
Using FileMaker’s Help Menu......Page 66
Views......Page 68
Table View......Page 69
Modifying Table view......Page 71
Advanced Find Techniques......Page 73
Searching with AND conditions......Page 74
Finding by Omitting......Page 75
Refining a found set with Omit commands......Page 76
Constraining and Extending the Found Set......Page 77
Constraining the found set......Page 78
Refining Searches with Find Operators......Page 79
Exact Match (=), Literal Text (\" \"), and Field Content Match (= =)......Page 80
Today’s Date (//) and Invalid Date or Time (?)......Page 81
One Character (@), One Digit (#), and Zero or More Characters (*)......Page 82
Saving finds......Page 83
Editing saved finds......Page 84
Editing What’s in Your Fields......Page 86
Drag-and-drop Editing......Page 87
Using the Replace Command......Page 88
Find and Replace......Page 89
Changing Text Appearance......Page 92
Text Formatting......Page 93
Paragraph Formatting......Page 94
Formatting Tabs......Page 95
Setting tabs in the Tabs dialog box......Page 96
Spell Checking with Menu Commands......Page 97
Managing Spelling Dictionaries......Page 99
Printing and Preview Mode......Page 100
Beyond Text: Container Fields......Page 103
Copy and paste......Page 104
QuickTime......Page 105
Sound......Page 106
Recording sound......Page 107
File......Page 108
Exporting data from container fields......Page 109
Tables and Fields......Page 110
Figuring Out What Fields You Need......Page 111
Defining Fields......Page 114
Changing a Field’s Name......Page 117
Reordering the Field List......Page 118
Field Types......Page 120
Number......Page 121
Date......Page 122
Time......Page 123
Calculation and summary......Page 124
Auto-Enter Options......Page 125
Creation values......Page 126
Modification values......Page 128
Value from last visited record......Page 129
Validation Options......Page 130
Making validation stricter......Page 132
Validation Requirements......Page 133
Not empty......Page 134
Member of value list......Page 135
In range......Page 136
Global storage......Page 137
Indexing......Page 138
Controlling indexing manually......Page 140
Indexing language......Page 141
Seeing the index......Page 142
Understanding Layouts......Page 146
Types of Layouts......Page 147
Making a New Layout......Page 149
Views......Page 152
The Layout Bar......Page 153
Renaming a Layout......Page 154
Found Sets and Layouts......Page 156
Part Types......Page 157
Parts in Form view......Page 158
When to use them......Page 159
Layout Objects......Page 160
Images......Page 161
The Look......Page 162
Layout Mode......Page 164
Arranging Parts......Page 165
Moving objects......Page 168
Resizing objects......Page 173
Working with text......Page 175
Working with fields......Page 179
Autoresize......Page 189
Decorating a Layout......Page 194
Coloring a part......Page 195
Adding lines and shapes......Page 197
Adding pictures......Page 203
Customizing the Tab Order......Page 205
Fixing the Tab Order in the People Database......Page 206
Preserving the Automatic Order......Page 207
The Lowdown on Layout Types......Page 208
Envelope......Page 209
Adding a New Layout......Page 211
Matching part sizes with the Object Info palette......Page 213
Matching part sizes with the T-squares......Page 214
Finishing the header......Page 215
Making data flow with merge fields......Page 216
Matching styles......Page 218
Adding fields to a layout......Page 220
Using symbols to show important info......Page 222
Rotating objects......Page 223
Setting Layout View......Page 224
Setting Anchors in List Layouts......Page 225
Conditional Formatting......Page 227
Conditional Formatting of Fields......Page 228
Conditional Formatting of Text Objects......Page 229
Web Viewer Objects......Page 232
Putting a Web Viewer on a Layout......Page 233
Web Viewer Options......Page 235
Allow interaction with web viewer content......Page 236
Display progress bar......Page 237
Adding Tab Controls......Page 238
Creating a Tab Control......Page 239
Appearance......Page 242
Tab Width......Page 243
Deleting a Tab Control......Page 244
Visualize the Result in Preview Mode......Page 245
Print Margins......Page 246
Columns......Page 248
Sliding Layout Objects......Page 250
When to use sliding......Page 251
Choosing the objects to slide......Page 252
Setting sliding options......Page 253
More Sliding/Printing options......Page 254
Setting Layout Order......Page 256
Layout Setup......Page 257
Value Lists......Page 258
Creating Value Lists......Page 259
Values from a field......Page 260
Editing Value Lists......Page 262
Edit Box......Page 263
Drop-Down List......Page 264
Pop-Up Menu......Page 266
Checkbox Set......Page 267
Drop-Down Calendar......Page 268
Auto-Complete in Edit Boxes......Page 271
Auto-Complete in drop-down lists......Page 272
Repetitions......Page 273
Display Data From......Page 274
General format......Page 275
Format as Boolean......Page 276
Format as decimal......Page 277
Date Formatting......Page 280
Custom......Page 281
Time Formatting......Page 283
Format as......Page 284
Graphic Formatting......Page 285
Alignment......Page 286
Adding Buttons......Page 288
Creating Buttons with the Button Tool......Page 289
Turning an Existing Layout Object into a Button......Page 291
Making a Button Not a Button......Page 293
Go to Field......Page 294
Editing actions......Page 295
Record actions......Page 296
Found set actions......Page 297
Window actions......Page 298
Open Menu Item actions......Page 299
Summary Fields......Page 300
Using Sub-Summary Parts......Page 302
Multiple sub-summary parts on one layout......Page 303
Leading and Trailing Grand Summaries......Page 305
Sub-Summary Parts and Printing......Page 307
Total of......Page 308
Average of......Page 309
Count of......Page 310
Fraction of Total......Page 311
Summarizing repetitions......Page 312
Relational Databases Explained......Page 316
Choosing Entities......Page 318
Finding Relationships......Page 321
One-to-one relationships......Page 324
Many-to-many relationships......Page 325
Creating an ER diagram......Page 326
Keys......Page 331
Choosing a good key field......Page 332
Primary and foreign keys......Page 333
Join Tables......Page 334
Creating a Relational Database......Page 336
Creating the Tables......Page 337
Adding primary keys......Page 338
Adding foreign keys......Page 341
Defining the Relationships......Page 342
Cleaning Up the Relationship Graph......Page 345
Selection tools......Page 347
Arrange the graph......Page 348
Printing the graph......Page 349
Finishing the Field Definitions......Page 350
Timeslips table fields......Page 351
Payments table fields......Page 352
One Table Occurrence, One Found Set......Page 353
Viewing Related Fields......Page 354
Adding or Editing Data with Relationships......Page 355
Editing Related Data......Page 356
Portals......Page 357
Editing data through a portal......Page 359
Creating Related Records......Page 362
Value Lists Based on Fields......Page 363
Preparing the relationship......Page 366
Adding a portal to a layout......Page 368
Adding records in a portal......Page 371
Go to Related Record......Page 372
GTRR with a found set......Page 373
Understanding Table Occurrences......Page 377
Adding a new key field to a table......Page 379
Adding a table occurrence......Page 380
Adding fields for new table occurrences......Page 382
Managing Data with Data Tunneling......Page 383
What you need in your data tunneling interface......Page 384
Understanding Table Occurrence Groups......Page 385
Managing the Relationships Graph......Page 388
Creating a Global Field......Page 390
Creating a New Table Occurrence Group......Page 391
Building a Global Field Layout......Page 393
Using a global pop-up field to change the display in a value list......Page 395
Connecting Database Files......Page 396
Adding a Table Occurrence from Another File......Page 397
Defining Data Sources Using a Path......Page 398
Lookups......Page 401
Creating Lookups......Page 403
Using a Relookup......Page 405
Relationships with More than One Criterion......Page 406
Other Relationship Operators......Page 408
Creating the Invoice Finder......Page 409
Understanding Calculations......Page 416
Creating a Calculation......Page 418
The Structure of a Calculation......Page 420
Using Fields in Calculations......Page 422
Text constants......Page 423
Using Operators in Calculations......Page 424
The concatenation operator......Page 425
Logical operators......Page 426
Parentheses......Page 427
Text functions......Page 428
Aggregate functions......Page 429
Financial functions......Page 430
Custom functions......Page 431
Expressions......Page 432
Creating a Calculation Field......Page 433
Field list......Page 436
Function list......Page 437
Calculation box......Page 438
Repetitions......Page 439
Auto-Enter Calculations......Page 441
Validate Data Entry with a Calculation......Page 442
Replacing Data Using a Calculation......Page 444
Comments......Page 446
Number Crunching Calculations......Page 448
Using Number Functions......Page 449
Count()......Page 450
List()......Page 451
SetPrecision()......Page 452
Preparing the Data......Page 453
Constructing the Calculation......Page 454
Text Calculations......Page 455
Text Functions......Page 457
Text function types......Page 458
Character functions......Page 459
Word functions......Page 460
Text editing functions......Page 461
Text value functions......Page 463
Text counting functions......Page 465
TextFont()......Page 466
TextStyleAdd() and TextStyleRemove()......Page 468
Date and Time Calculations......Page 469
Dates......Page 470
Times......Page 471
Parsing Dates and Times......Page 472
Calculations that Create Dates and Times......Page 473
The secret powers of Date()......Page 474
Containers in Calculations......Page 475
Calculations with References......Page 476
Conditional Formatting Formulas......Page 478
Understanding Boolean Functions......Page 479
Using the Self() Function......Page 481
Stored and Unstored Calculation Fields......Page 483
Field dependencies......Page 484
Deciding when to store......Page 485
Indexing calculations......Page 486
Conditional Functions......Page 487
The If() function......Page 488
The Case() function......Page 489
The Choose() function......Page 490
Total Due calculation #1: Using the If() function......Page 491
Total Due calculation #2: Using the Case() function......Page 492
Defining Calculation Variables......Page 494
Total Due calculation #3: Using the Let() function......Page 495
Local variables......Page 496
Global variables......Page 497
Nesting Let() Functions......Page 498
Custom Functions......Page 500
Defining a Custom Function......Page 502
Editing Custom Functions......Page 504
Step 1: Solve the first case......Page 505
Step 2: Assume your function already works, and use it......Page 506
Step 3: Find a stopping point......Page 507
Plug-Ins......Page 508
Old and New Plug-ins......Page 509
Finding Plug-ins......Page 510
Creating Your Own Plug-ins......Page 511
Understanding Scripts......Page 514
Preparing the Database......Page 515
Creating Your Script......Page 516
Improving Your Script......Page 521
The Importance of the Layout......Page 524
When to Switch......Page 525
Running Scripts......Page 526
The Scripts Menu......Page 527
Script Triggers......Page 528
Opening and Closing Scripts......Page 529
Timer Scripts......Page 530
Creating Script Folders......Page 531
Branching and Looping in Scripts......Page 533
Branching with If, Else If, Else, and End If......Page 534
Testing Multiple Conditions......Page 540
Looping......Page 541
Script Triggers......Page 542
OnRecordLoad......Page 543
OnModeEnter and OnModeExit......Page 544
OnObjectEnter......Page 545
OnObjectSave......Page 546
A Simple Script Trigger......Page 547
Go to Layout......Page 550
Scripting for Fields......Page 551
Go to Next Field and Go to Previous Field......Page 552
Selecting Text......Page 553
Avoiding Cut, Copy, and Paste......Page 554
Insert Calculated Result......Page 555
Inserting other values......Page 556
Set Field by Name......Page 557
A Field Script in Action......Page 559
Building the script......Page 560
Perform Find/Replace......Page 562
Replace Field Contents and Relookup Field Contents......Page 564
Working with Records......Page 565
Creating, Duplicating, and Deleting Records......Page 566
Go to Record/Request/Page......Page 567
Go to Related Record......Page 568
Commit Record/Request......Page 570
Revert Record/Request......Page 571
Copy All Records/Requests......Page 573
Go to Portal Row......Page 574
Performing People’s Find Requests......Page 575
Triggering a Find script......Page 578
Static Find Requests......Page 579
Dynamic Find Requests......Page 581
Constraining and Extending the Found Set......Page 582
Omitting Records......Page 583
Sorting Records Automatically......Page 584
Creating Windows......Page 585
Bringing a Window to the Front......Page 586
Arrange All Windows......Page 587
Move/Resize Window......Page 588
Other Window-Related Script Steps......Page 589
Working with Files......Page 590
Save a Copy As......Page 591
Printing......Page 592
Set Web Viewer......Page 593
Execute SQL......Page 594
Exit Application......Page 595
Commenting Scripts......Page 596
Communicating with Database Users......Page 598
Asking a Simple Question......Page 599
Asking a More Complex Question......Page 601
The Perform Script Script Step......Page 603
Exit Script (Result)......Page 604
Organizing scripts that use Halt or Exit Script......Page 605
Script Parameters......Page 607
Script Variables......Page 612
Handling Errors......Page 613
The Set Error Capture Script Step......Page 614
The Get ( LastError ) Function......Page 615
Putting a Complex Script Together......Page 617
Considering Exceptions......Page 618
Creating Subscripts......Page 620
Creating skeleton subscripts......Page 622
Finishing the Subscripts......Page 623
The Find Unbilled Activity subscript......Page 624
Copying and editing existing script steps......Page 628
The Create Invoice Record subscript......Page 630
The Process Timeslips subscript......Page 633
The Process Expenses subscript......Page 636
Finishing the main script......Page 639
Testing Scripts......Page 641
Install OnTimer Script......Page 642
Keystroke Triggers......Page 643
Detecting which keys were pressed......Page 644
Creating a simple keystroke trigger......Page 646
Setting up a keystroke filter......Page 647
How Security Works......Page 652
What They Can Do......Page 653
Privilege Sets......Page 654
Custom privilege sets......Page 655
Other privileges......Page 656
Creating a privilege set......Page 658
Data Access and Design Privileges......Page 659
Creating record-level access......Page 661
Editing a privilege set......Page 669
Managing Accounts......Page 670
Adding a New Account......Page 672
External Authentication......Page 673
Extended Privileges......Page 674
Creating an Extended Privilege......Page 675
Assigning Extended Privileges......Page 676
Detecting Privileges in a Script......Page 677
Running Scripts with Full Access Privileges......Page 678
Add Account......Page 679
Enable Account......Page 680
Re-Login......Page 681
Sharing Your Data with Others......Page 682
Saving as Microsoft Excel......Page 683
Saving as Portable Document Format (PDF)......Page 684
External SQL Sources......Page 687
Installing the ODBC driver......Page 688
Configuring the data source......Page 689
Connecting FileMaker to a SQL Data Source......Page 691
Adding SQL Tables to a FileMaker Database......Page 693
Creating relationships......Page 694
Shadow tables......Page 695
Using SQL Tables......Page 697
Exporting Data......Page 698
Grouped Exports......Page 701
Export formats......Page 702
Importing Data......Page 704
Update existing records in found set......Page 709
Update matching records in found set......Page 710
Creating Tables on Import......Page 711
Import Data Sources......Page 712
Folder......Page 713
Importing a folder of text files......Page 714
Digital Camera......Page 715
XML Data Source......Page 716
Bento Data Source......Page 717
ODBC Data Sources......Page 719
Save Records Script Steps......Page 721
The Export Records Script Step......Page 722
Sharing Your Database......Page 724
Setting Up a Host Computer......Page 725
Opening a Shared File......Page 727
Choosing a host computer......Page 728
Choosing a file......Page 729
Advanced Web Publishing Options......Page 730
Connecting from a Web Browser......Page 733
Custom Home Page......Page 735
FileMaker Server......Page 736
File Compatibility......Page 737
Installing FileMaker Server......Page 738
Configuring your server......Page 739
Administering FileMaker Server......Page 741
Installing a Database......Page 744
Scheduling a backup......Page 745
Testing Your Server......Page 750
Server Hardware......Page 751
Applying Developer Utilities......Page 754
Importing Tables and Fields......Page 755
Copying and Pasting Fields......Page 756
Script Debugger......Page 757
Controlling Script Execution......Page 758
Examining Errors......Page 761
Pause on Error......Page 762
Active Scripts......Page 763
The Data Viewer......Page 764
The Current Tab......Page 765
The Watch Tab......Page 766
Disable Script Steps......Page 767
The Database Design Report......Page 768
Generating the DDR......Page 769
Using the DDR......Page 770
Finding Broken Elements with the DDR......Page 772
Tooltips......Page 773
Custom Menus......Page 774
Editing a Menu......Page 776
Creating a New Menu......Page 779
Removing a Menu......Page 780
Installing Custom Menu Sets......Page 781
Developer Utilities......Page 782
Using the Developer Utilities......Page 783
Renaming files......Page 784
Remove admin access from files permanently......Page 785
Enable Kiosk mode for non-admin accounts......Page 786
Create error log for any processing errors......Page 787
Delivering a Runtime Solution......Page 788
Getting Help from FileMaker Itself......Page 792
Templates......Page 793
Downloadable Resources......Page 795
Technical Support......Page 796
Developer Programs......Page 797
DevCon......Page 798
FileMaker Error Codes......Page 800
Index......Page 808