Learn FileMaker Pro 8. 5

by Stars, Jonathan

• ISBN13: 9781598220254
• ISBN10: 159822025X
• Format: Paperback
• Copyright: 2006-07-31
• Publisher: Jones & Bartlett Learning

Summary

Learn FileMaker® Pro 8.5 presents the fundamentals of the award-winning FileMaker Pro database program and distills the complexity of the program into a logical hands-on approach for both beginners and those who have experience with previous versions of FileMaker Pro. With this version, FileMaker Pro enhances the dramatically new and efficient approach to database management introduced in FileMaker Pro 7, giving the user even greater flexibility.Learn how to use the alignment tools to quickly align objects on your layout; set up fields for auto-complete and create tooltips to assist users; attach a calendar to a date field to efficiently enter dates; import tables and their data in a single step; save and send your reports as PDF or Excel files; organize information in different layers on a single layout using the Tab Control feature; export the contents of a field and attach it to an e-mail; use the Web Viewer to display information from the web inside your database.

Author Biography

Jonathan Stars is a professional FileMaker consultant in Michigan

Table of Contents

Preface	p. xvii
About the Author	p. xx
Acknowledgments	p. xxi
Introduction	p. xxiii
Learning the Basics
Getting Acquainted with FileMaker Pro	p. 2
Introduction	p. 2
What Is a Database?	p. 3
Using FileMaker Pro	p. 4
Opening FileMaker Pro	p. 4
Opening a FileMaker Pro File	p. 5
Database Concepts	p. 5
Files	p. 5
Tables	p. 7
Records	p. 7
Fields	p. 8
Layouts	p. 9
Saving a Copy of a FileMaker Pro File	p. 10
Closing a FileMaker Pro File	p. 11
Quitting FileMaker Pro	p. 11
Summary	p. 11
Menus and Modes	p. 13
Introduction	p. 13
Define Database	p. 13
Tables Tab	p. 14
Fields Tab	p. 14
Relationships Tab	p. 16
Lookups	p. 19
Related Data	p. 19
Modes	p. 20
Browse Mode	p. 20
Layout Mode	p. 20
Layout Types	p. 22
Find Mode	p. 23
Preview Mode	p. 23
Menu Commands	p. 24
Sort Command	p. 24
ScriptMaker	p. 25
Accounts and Privileges	p. 25
Summary	p. 27
Creating Your First Database	p. 28
Introduction	p. 28
Planning the Database	p. 28
Planning This Database	p. 29
Creating the File	p. 31
Adding Some Fields	p. 31
Adding Field Options	p. 32
Cleaning Up the Layout	p. 33
Format Painter	p. 34
Duplicate	p. 35
Tool Panel	p. 36
Layout Toolbars	p. 37
Drawing Tools	p. 37
Object Grids	p. 37
T-Squares	p. 38
Arrow Keys	p. 38
Object Info Palette	p. 38
Alignment[superscript 8]	p. 39
Locking Objects	p. 40
Adding Some Data	p. 41
Adding a Script	p. 41
Simple New Record Script	p. 41
Simple Delete Record Script	p. 42
Simple Find Script	p. 43
Assigning Scripts to Buttons	p. 45
Special Situations	p. 46
Summary	p. 48
Using FileMaker Pro
Creating a New Database	p. 52
Introduction	p. 52
Considerations	p. 52
Predefined Databases	p. 52
Creating Your Own Database	p. 53
What Are Fields?	p. 53
Creating a Field	p. 53
About Field Names	p. 54
The Eight Field Types	p. 55
Text Field	p. 55
Number Field	p. 55
Date Field	p. 56
Time Field	p. 56
Timestamp Field[superscript 7]	p. 56
Container Field	p. 57
Calculation Field	p. 57
Summary Field	p. 59
Global Storage Option	p. 60
Repeating Field	p. 61
Formatting Your Fields in Japanese[superscript 7]	p. 62
Field Formats on Layouts	p. 63
Date Format	p. 63
Number Format	p. 64
Text Format	p. 64
Time Format	p. 64
Summary	p. 65
Entering and Modifying Your Data	p. 66
Introduction	p. 66
Creating a New Record	p. 66
Selecting the Record	p. 67
Adding Data	p. 69
Selecting the Field	p. 70
Using Tab Order	p. 70
Data Entry Shortcuts	p. 73
Insert	p. 73
Copy and Paste	p. 75
Drag-and-Drop	p. 75
Using Value Lists	p. 76
Auto-Complete[superscript 8]	p. 78
Calendar Drop-down[superscript 8]	p. 79
Using Container Fields	p. 80
Insert QuickTime	p. 80
Insert Picture	p. 80
Insert Sound	p. 81
Changing Data	p. 82
Highlighting	p. 82
Spellchecking	p. 82
Visual Spell Checker[superscript 8]	p. 82
Replace Command	p. 83
Find/Replace	p. 84
Revert Command	p. 85
Deleting Data	p. 86
Summary	p. 87
Working with Related Tables - Part 1	p. 88
Introduction	p. 88
One or Many Tables?	p. 88
One Table - Flat File Design	p. 88
Many Tables - Relational Design	p. 88
Define Your Tables	p. 90
Normalize Your Information	p. 91
Determine Your Match, or Key, Fields	p. 92
Introducing "Anchor-Buoy" Relationship Design	p. 93
Making the Relationship	p. 94
Relationship Tools[superscript 7]	p. 95
Tool Icons	p. 97
Allow Creation of Related Records	p. 99
Parent and Child	p. 100
Allow Deletion of Related Records	p. 101
Sort Related Records	p. 102
Making a Portal	p. 103
Drawing the Portal	p. 103
Placing the Fields in a Portal	p. 104
Adding Data to a Portal Field	p. 105
Portal Tool Behavior	p. 106
Sorting a Portal	p. 107
Investigating the Related Table	p. 108
Relating a Table Back to the Parent Table	p. 110
Performing a Find in a Portal	p. 111
Performance Considerations	p. 111
Deleting a Record from a Portal	p. 112
Dealing with "Portal Pop"	p. 113
Before We Go	p. 113
Summary	p. 114
Working with Related Tables - Part 2	p. 116
Introduction	p. 116
Planning Your Database	p. 116
Tricky Terms and Catchy Phrases	p. 117
ER Diagram	p. 117
One-to-Many Relationship	p. 117
One-to-One Relationship	p. 118
Many-to-Many Relationship	p. 118
The Join, or Link, Table	p. 119
Self-join Relationship	p. 120
Multiple-Criteria Relationships[superscript 7]	p. 121
Unequal Relationships[superscript 7]	p. 122
The Relationships Graph	p. 123
Other Notes	p. 123
Invoice System ER Diagram	p. 124
Create the Tables	p. 125
Invoice Table	p. 125
InvoiceLineItems Table	p. 126
Import Table[superscript 8]	p. 126
Product Table	p. 127
Other Possibilities	p. 127
Example Data	p. 128
Create the Relationships	p. 128
Invoice and InvLI	p. 128
Invoice and Contact	p. 131
InvLI and Product	p. 131
Add the Lookups	p. 132
The Invoice Layout	p. 133
Adding Drop-downs to the Invoices	p. 134
Products Drop-down	p. 134
Getting the Invoice Total	p. 136
Customers Drop-down	p. 136
Placing the Customer Field on the Layout	p. 137
Other Considerations	p. 138
Summary	p. 138
Finding and Sorting Your Data	p. 140
Introduction	p. 140
Finding Records	p. 140
Methods	p. 141
The Find Symbols	p. 142
Less Than (<)	p. 142
Less Than or Equal To ([less than or equal] or < = on Macintosh, < = on Windows)	p. 143
Greater Than (>)	p. 143
Greater Than or Equal To ([greater than or equal] or > = on Macintosh, > = on Windows)	p. 143
Exact Match (=)	p. 143
Range (...)	p. 143
Duplicates (!)	p. 143
Today's Date (//)	p. 144
Invalid Date or Time (?)	p. 144
One Character (@)	p. 144
Zero or More Characters (*)	p. 145
Literal Text ("")	p. 145
Relaxed Search ([tilde])	p. 145
Field Content Match (==)	p. 145
AND Finds	p. 145
OR Finds	p. 145
Constrain and Extend Found Set	p. 146
Constrain Found Set	p. 146
Extend Found Set	p. 147
Quick Find[superscript 8]	p. 148
Auto-Complete[superscript 8]	p. 148
Other Find Tricks	p. 148
Within Scripts	p. 149
Find Strategies	p. 151
Limitations	p. 152
Omitting Records	p. 152
Methods	p. 152
Omit Multiple	p. 153
Omit as Part of a Find	p. 153
Show Omitted	p. 153
Within Scripts	p. 153
Strategies	p. 154
Limitations	p. 154
Sorting Records	p. 154
Methods	p. 154
Within Scripts	p. 155
Strategies	p. 155
Sort By a Summary Field	p. 155
Limitations	p. 157
Summary	p. 157
Creating New Layouts with the Layout Assistant	p. 159
Introduction	p. 159
Create a New Layout	p. 159
Standard Form	p. 160
Specify Fields	p. 160
Select a Theme	p. 161
Columnar List/Report	p. 162
Choose Report Layout	p. 163
Organize Records by Category	p. 163
Sort Records	p. 163
Specify Subtotals	p. 164
Specify Grand Totals	p. 164
Header and Footer Information	p. 165
Create a Script for This Report	p. 165
Cleaning Up	p. 165
Sub-summary Part Definition	p. 166
Table View	p. 167
Labels	p. 168
Envelope	p. 169
Blank Layout	p. 169
Summary	p. 170
Turning Your Data into Information
Keeping Your Data Clean and Neat	p. 172
Introduction	p. 172
Field Data Entry Options	p. 172
Auto-Enter Tab (Automatically Entering Values)	p. 173
Creation	p. 173
Modification	p. 173
Serial Number	p. 173
Value from Last Visited Record	p. 174
Data	p. 174
Calculated Value	p. 174
Looked-up Value	p. 175
Prohibit Modification	p. 176
Validation Tab (Checking Data for Accuracy)	p. 176
Validate Data in This Field[superscript 7]	p. 176
Strict Data Type	p. 177
Not Empty	p. 177
Unique Value	p. 178
Existing Value	p. 178
Member of Value List	p. 178
In Range	p. 178
Validated By Calculation	p. 178
Maximum Number of Characters	p. 179
Display Custom Message if Validation Fails	p. 179
Storage Tab (Global Storage, Repeating, and Indexing)	p. 179
Global Storage	p. 179
Repeating Field Options	p. 180
FileMaker's Indexing System	p. 181
Storage Options	p. 181
Field Indexing Pros and Cons	p. 181
Limitations	p. 182
Furigana Tab[superscript 7]	p. 182
Summary	p. 183
Putting Your Data to Work for You	p. 184
Introduction	p. 184
Defining a Calculation Field	p. 184
Operators	p. 185
Mathematical Operators	p. 185
Comparison Operators	p. 188
Logical Operators	p. 191
Text Operators	p. 193
Other Options	p. 195
Storage Option	p. 195
Repeating Field	p. 195
Do Not Evaluate	p. 195
Limitations	p. 196
Summary	p. 196
Real-World Calculations - Part 1	p. 197
Introduction	p. 197
FileMaker's Help Files	p. 197
What Are Functions?	p. 198
Text Functions	p. 198
Left	p. 199
A Calculation within a Calculation	p. 200
LeftWords	p. 200
Length	p. 200
Position	p. 201
Building Complex Calculations	p. 202
The Steps	p. 202
Using the Process	p. 202
Make It Permanent	p. 205
Number Functions	p. 206
The Process	p. 206
A Calculation for FMP7 and Later	p. 209
Date and Time Functions	p. 209
Time Clock	p. 210
Commenting Calculations[superscript 7]	p. 211
Summary	p. 212
Real-World Calculations - Part 2	p. 213
Introduction	p. 213
Aggregate Functions	p. 213
Aggregate Examples	p. 213
Summary Functions	p. 214
GetSummary Example	p. 215
Repeating Functions	p. 216
GetRepetition Example	p. 216
Financial Functions	p. 219
PMT Example	p. 219
Trigonometric Functions	p. 220
Pi Example	p. 220
Logical Functions	p. 221
If and Case	p. 221
IsEmpty	p. 222
Evaluate[superscript 7]	p. 222
Let[superscript 7]	p. 224
GetLayoutObjectAttribute[superscript 8.5]	p. 226
Get Functions[superscript 7]	p. 227
Get (RecordNumber) Example	p. 227
Design Functions	p. 228
FieldNames and FieldStyle Examples	p. 228
External Functions	p. 230
Text Formatting Functions[superscript 7]	p. 231
Timestamp Functions	p. 231
Custom Functions[superscript 7]	p. 232
Summary	p. 232
Creating a Real Solution
Automating Your Database with Scripts	p. 236
Introduction	p. 236
What Are Scripts?	p. 236
One Step at a Time	p. 237
Script Options	p. 238
Planning Your Script	p. 238
Where Am I Now?	p. 239
What Do I Want to Do?	p. 240
Where Do I Want to Be When I Finish?	p. 240
What Can Go Wrong?	p. 240
Script Steps Overview	p. 240
Script Steps	p. 241
Control Category	p. 241
Main Menu Script Example	p. 242
Going to Contact	p. 243
Fail-safe	p. 244
Getting Back	p. 246
Testing	p. 246
Access the Main Menu Layout from Other Files	p. 247
Using Layouts from Other Files[superscript 7]	p. 250
Portable Portals[superscript 7]	p. 251
Print Invoices Example	p. 253
Else If[superscript 7]	p. 253
The Print Script	p. 254
The Invoices Scripts	p. 255
Figuring Interest	p. 257
Printing One Invoice	p. 258
Monthly Billing Script	p. 258
Testing Monthly Billing	p. 258
Thoughtful Additions	p. 259
Printing to PDF[superscript 8]	p. 259
Script Variable[superscript 8]	p. 260
Match Found Set (Related Records)[superscript 8]	p. 261
Debugging Scripts	p. 261
Important Hints	p. 262
Import Scripts	p. 264
Button Options (Pause, Halt, Exit, Resume)	p. 264
Startup/Shutdown Scripts	p. 265
Show Custom Dialog Script Step	p. 265
Other Features	p. 268
New Window[superscript 7]	p. 268
Script Parameters[superscript 7]	p. 270
Accounts Script Category[superscript 7]	p. 272
File References[superscript 7]	p. 273
And Finally	p. 273
Summary	p. 273
Making Sense of Your Information with Layouts	p. 275
Introduction	p. 275
What Is a Layout?	p. 275
Layout Types	p. 276
On-screen Layouts (Data Entry)	p. 276
Printed Layouts	p. 280
Layout Parts	p. 282
Title Header	p. 283
Header	p. 283
Body	p. 284
Leading and Trailing Sub-summaries	p. 284
Leading and Trailing Grand Summaries	p. 285
Footer	p. 285
Title Footer	p. 285
Working with Layout Parts	p. 285
Adding and Deleting Parts	p. 286
Modifying Parts	p. 286
Summary	p. 286
Designing Your Screen Layouts	p. 288
Introduction	p. 288
Basic Design	p. 288
Know What You Like	p. 289
Keep It Consistent	p. 291
Group Formatting	p. 291
Selecting Groups of Layout Objects	p. 292
How to Make It Pretty	p. 293
Arrange and View Menus	p. 293
Rotate Layout Objects	p. 294
Icons	p. 294
Locking Objects on a Layout	p. 295
Adding Graphics and Movies to a Layout	p. 296
Formatting Graphics on a Layout	p. 296
Customizing the Appearance of Objects on a Layout	p. 297
Adding Borders, Baselines, and Fills to Fields	p. 297
Adding Object Effects	p. 298
Embossed	p. 298
Engraved	p. 298
Drop Shadow	p. 298
Changing Colors	p. 298
Tab Control[superscript 8]	p. 299
Object Names[superscript 8.5]	p. 303
Tooltips[superscript 8]	p. 304
Summary	p. 305
Designing Your Printed Report Layouts	p. 307
Introduction	p. 307
What Is a Report?	p. 307
Creating a Useful and Attractive Report	p. 307
Report Types	p. 308
Creating a Report	p. 309
What Results Do You Want?	p. 309
What Data Do You Have (and Not Have)?	p. 309
Example Reports	p. 309
Customer Sales Report	p. 309
Setup - Making the Data Available	p. 310
Building the Report	p. 310
Touchup Work	p. 311
How to Set Up a Find Layout	p. 312
Percent of Sales by Product Report	p. 318
Building the Report	p. 318
Touchup Work	p. 319
How to Set Up This Find Layout	p. 319
Other Common Reports	p. 321
Avoiding the Today Function in Calculations	p. 321
Organizational Details	p. 323
Summary	p. 324
Sharing Your Database
Personal Data Sharing	p. 328
Introduction	p. 328
What Is Personal Data Sharing?	p. 328
Capabilities	p. 329
Network Requirements	p. 329
Using Personal File Sharing	p. 330
Using Files as a Guest	p. 331
Sharing Remotely by Way of Modem	p. 332
File Sharing Caution	p. 333
Limitations	p. 334
File and Guest Limits	p. 334
Global Fields and Shared Files	p. 334
Optimizing Performance	p. 335
Hardware Considerations	p. 336
FileMaker Server	p. 336
Some Final Words	p. 336
Closing Hosted Files	p. 337
Slow Network Traffic	p. 337
Save/Send Records as Excel[superscript 8]	p. 337
Export Field Contents/Fast Send[superscript 8]	p. 338
Summary	p. 339
Sharing Your Data on the Web	p. 340
Introduction	p. 340
Why Publish on the Web?	p. 340
FileMaker and Instant Web Publishing (IWP)	p. 341
Setting Up Your Computer	p. 341
If You Have an Internet Account	p. 341
If You Don't Have an Internet Account	p. 344
Setting up FileMaker Pro for IWP	p. 344
Instant Web Publishing Settings	p. 345
Advanced Options	p. 346
Setting Up Your Database	p. 347
Browsers	p. 348
Layouts	p. 349
IWP Browser Interface	p. 351
Web Security	p. 354
Try It Out	p. 355
Other Notes and Options	p. 356
Cool Web Sites Powered by FileMaker	p. 358
Summary	p. 359
Sharing Your Data with Other Applications	p. 360
Introduction	p. 360
Using FileMaker's Import and Export Commands	p. 360
ODBC Sharing	p. 361
Making FileMaker Data Available to Other Applications	p. 362
Getting Data from Another Application	p. 364
Setting Up the Data Source (Macintosh OS X)	p. 364
Importing the Data (Macintosh)	p. 366
Setting Up the Data Source (Windows)	p. 367
Importing the Data (Windows)	p. 368
Saving the Import as a Script	p. 371
Execute SQL Script Step	p. 372
Converting and Importing Data from Microsoft Excel	p. 373
Importing a Folder of Files in a Single Operation	p. 374
Summary	p. 376
Protecting Your Information
Keeping Your Data Secure	p. 380
Introduction	p. 380
Why Protect Your Data?	p. 380
FileMaker Security	p. 380
Accounts Tab[superscript 7]	p. 381
Edit Account[superscript 7]	p. 381
Guest Account[superscript 7]	p. 381
Admin Account[superscript 7]	p. 383
New Account[superscript 7]	p. 383
A Word about Passwords	p. 384
Privilege Set[superscript 7]	p. 384
Privilege Sets Tab[superscript 7]	p. 385
Edit Privilege Set[superscript 7]	p. 386
Data Access and Design	p. 386
Other Privileges[superscript 7]	p. 397
Extended Privileges[superscript 7]	p. 398
Extended Privileges Tab[superscript 7]	p. 398
Expand and/or Reduce Privileges	p. 401
Editing and Deleting Accounts[superscript 7]	p. 402
Editing	p. 402
Cautions	p. 402
Limiting Access through Scripting	p. 404
Startup Script	p. 404
Controlling Layout Access	p. 405
Limiting Access through Field Calculations	p. 405
The Script	p. 406
The Calculation	p. 406
Internet Considerations	p. 408
Conversion Issues	p. 408
Summary	p. 408
Backup and Recovery	p. 410
Introduction	p. 410
Why Back Up?	p. 410
FileMaker Server Backup Features	p. 411
Using Commercial Backup Applications	p. 411
Problems with Tape	p. 412
Database Corruption	p. 413
What Is a Clone?	p. 414
Saving a Clone	p. 414
Backup Routine	p. 415
While You Work	p. 416
Other Related Measures	p. 416
FileMaker Techniques	p. 417
More about Exporting	p. 420
One-Record Table	p. 421
Using Your Backups	p. 422
Restoring	p. 422
Importing Data	p. 424
More about Importing	p. 425
Tying Up the Loose Ends	p. 425
Summary	p. 427
Beyond FileMaker
FileMaker Mobile 8	p. 430
Introduction	p. 430
Installation Overview	p. 431
Setting Up Your Connections	p. 432
Syncing	p. 433
FileMaker Mobile Setup	p. 433
Starting Over	p. 434
Mobile Settings	p. 435
Properties	p. 436
Synchronization Options	p. 437
Sync Me, Baby	p. 437
Other Stuff	p. 439
Going Mobile	p. 439
FileMaker Appointments	p. 439
PDA Calendar	p. 441
Contacts	p. 441
Grocery List	p. 441
FileMaker Mobile vs. FileMaker Pro	p. 442
Summary	p. 443
FileMaker and XML	p. 444
Introduction	p. 444
What Is XML?	p. 444
Add in XSL	p. 445
What Can I Use It For?	p. 445
Examples: Exporting XML as HTML	p. 447
Using a Local XSL Style Sheet	p. 447
Creating the Export XML Script	p. 448
Using a Remote XSL Style Sheet	p. 449
And Beyond...	p. 450
Profile of a FileMaker XML Web Request	p. 450
Import XML	p. 451
Open XML	p. 451
Summary	p. 451
FileMaker and the Internet	p. 453
Introduction	p. 453
Web Viewer[superscript 8.5]	p. 453
Creating a Web Viewer	p. 454
Web Viewer Economy	p. 457
Layout Work	p. 458
Importing Some Data	p. 458
Working with the Radio Buttons	p. 459
Working with the Web Viewer	p. 460
Getting the URL	p. 460
Mini Web Viewer	p. 461
Automatically Filling Out Forms - Not	p. 461
Which Way Did He Go?	p. 462
Back and Ahead Buttons	p. 463
Screen Scraping	p. 464
Saving the URL	p. 465
The Example File	p. 465
The Web Viewer and Other Applications	p. 465
Cleaning Up	p. 466
Rules	p. 466
Suggested Uses	p. 467
Summary	p. 468
File Conversion Issues and Answers	p. 469
Introduction	p. 469
Documentation	p. 469
Step By Step	p. 470
Conversion Log	p. 472
Comparing Fields	p. 472
Comparing Layouts	p. 473
Accounts & Privileges	p. 473
Comparing Scripts	p. 474
Comparing Relationships	p. 474
File References	p. 475
More on Accounts and Passwords	p. 476
Finishing Up	p. 476
Summary	p. 476
Leftover, but Important Stuff	p. 479
Getting More Help	p. 491
Index	p. 495
Table of Contents provided by Ingram. All Rights Reserved.