Learn FileMaker Pro 5.5

The appendixes address issues such as troubleshooting, network error messages, e-mail from FileMaker, and technical support. In addition, an extensive index with more than 2,800 entries guides the reader to specific FileMaker topics.

Contents

Preface ..... xv

Acknowledgments ..... xvii

Introduction ..... xx

Conventions ..... xxii

Updates ..... xxiii

Part 1: Learning the Basics

Chapter 1: Getting Acquainted with FileMaker Pro ..... 3

What is a Database? ..... 4

Using FileMaker Pro ..... 5

Opening FileMaker Pro ..... 5

Opening a FileMaker Pro File ..... 6

Database Concepts ..... 6

Files ..... 6

Records ..... 7

Fields ..... 8

Layouts ..... 8

Saving a Copy of a FileMaker Pro File ..... 9

Closing a FileMaker Pro File ..... 10

Quitting FileMaker Pro ..... 10

Summary ..... 11

Chapter 2: Menus and Modes ..... 13

Define Fields ..... 13

Define Relationships ..... 15

Lookups ..... 16

Related Data ..... 17

Modes ..... 18

Browse Mode ..... 18

Layout Mode ..... 18

Layout Types ..... 20

Find Mode ..... 21

Preview Mode ..... 21

More Menus ..... 22

Sort Command ..... 22

ScriptMaker ..... 22

Access Privileges and Passwords ..... 23

Summary 24

Chapter 3:Creating Your First Database ..... 27

Planning the Database ..... 27

Planning This Database ..... 28

Creating the File ..... 30

Adding Some Fields ..... 30

Adding Field Options ..... 31

Cleaning Up the Layout ..... 32

Tool Panel ..... 34

Layout Toolbars ..... 35

Drawing Tools ..... 36

Object Grids ..... 36

T: Squares ..... 37

Arrow Keys ..... 37

Size Palette ..... 37

Alignment ..... 8

Locking Objects ..... 39

Adding Some Data ..... 40

Adding a Script ..... 40

Simple New Record Script ..... 40

Simple Delete Record Script ..... 41

Simple Find Script ..... 41

Assigning Scripts to Buttons ..... 43

Create a Button ..... 43

Assign a Button to a Script ..... 3

Special Situations ..... 44

Summary ..... 45

Part 2: Using EileMaker Pro

Chapter 4:Creating a New Database ..... 51

Considerations ..... 51

Predefined Databases ..... 51

Creating Your Own ..... 52

What are Fields? ..... 52

Creating a Field ..... 52

About Field Names ..... 53

The Eight Field Types ..... 54

Text Field ..... 54

Number Field ..... 55

Date Field ..... 55

Time Field ..... 55

Container Field ..... 56

Calculation Field ..... 56

Summary Field ..... 58

Global Field ..... 59

Repeating ..... 60

Field Formats on Layouts ..... 61

Date Format ..... 61

Number Format ..... 62

Text Format ..... 63

Summary ..... 63

Chapter 5:Entering and Modifying Your Data ..... 65

Creating a New Record ..... 65

Selecting the Record ..... 66

Adding Data ..... 68

Selecting the Field ..... 68

Using Tab Order ..... 69

Data Entry Shortcuts ..... 71

Insert ..... 71

Copy and Paste ..... 72

Drag: and: Drop ..... 73

Using Value Lists ..... 73

Using Container Fields ..... 75

Insert Movie ..... 75

Insert Picture ..... 75

Insert Sound ..... 76

Changing Data ..... 76

Deleting Data ..... 78

Summary ..... 79

Chapter 6:Working with Related Files: Part 1 ..... 81

One or Many Files? ..... 81

One File: Flat File Design ..... 81

Many Files: Relational Design ..... 81

Define Your Files ..... 82

Normalize Your Information ..... 83

Determine Your Match, or Key, Fields ..... 83

Parent and Child ..... 84

Making the Relationship ..... 84

Allow Creation of Related Records ..... 85

Allow Deletion of Related Records ..... 85

Sort Related Records ..... 86

Making a Portal ..... 87

Drawing the Portal ..... 87

Placing the Fields in a Portal ..... 88

Adding Data to a Portal Field ..... 89

Investigating the Related File ..... 90

Relating a File Back to the Parent File ..... 91

Performing a Find in a Portal ..... 91

Performance Considerations ..... 92

Deleting a Record from a Portal ..... 92

Summary ..... 93

Chapter 7:Working with Related Files: Part 2 ..... 95

Planning Your Database ..... 95

Tricky Terms and Catchy Phrases ..... 96

ER Diagram ..... 96

One: to: many Relationship ..... 96

One: to: one Relationship ..... 96

Many: to: many Relationship ..... 97

The Join, or Link, File ..... 97

Self: join Relationship ..... 98

Other Notes ..... 99

Invoice System ER Diagram ..... 99

Create the Files ..... 100

Invoices File ..... 100

Invoice Line Items File ..... 101

Products File ..... 101

Other Possibilities ..... 102

Example Data ..... 102

Create the Relationships ..... 102

Add the Lookups ..... 103

The Invoice Layout ..... 104

Adding Pop: ups to the Invoices ..... 105

Products Pop: up ..... 105

Getting the Invoice Total ..... 106

Customers Pop: up ..... 106

Placing the Customer Field on the Layout ..... 107

Other Considerations ..... 107

Summary ..... 108

Chapter 8:Finding and Sorting Your Data ..... 111

Finding Records ..... 111

Methods ..... 111

The Find Symbols ..... 112

Less Than (<) ..... 113

Less Than or Equal (_< or < = on Macintosh or < = on Windows) ..... 113

Greater Than (>) ..... 113

Greater Than or Equal (_> or > = on Macintosh or > = on Windows) ..... 113

Exact Match (=) ..... 113

Range (...) ..... 114

Duplicates (!) ..... 114

Today's Date (//) ..... 114

Invalid Date or Time (?) ..... 114

One Character (@) ..... 115

Zero or More Characters (*) ..... 115

Literal Text (" ") ..... 115

Field Content Match (==) ..... 115

AND Finds ..... 115

OR Finds ..... 116

Other Tricks ..... 116

Within Scripts ..... 116

Find Strategies ..... 119

Limitations ..... 120

Omitting Records ..... 120

Methods ..... 120

Omit Multiple ..... 120

Omit as Part of a Find ..... 120

Show Omitted ..... 121

Within Scripts ..... 121

Strategie ..... 121

Limitations ..... 121

Sorting Records ..... 121

Methods ..... 122

Within Scripts ..... 122

Strategies ..... 122

Sort by a Summary Field ..... 122

Limitations ..... 125

Summary ..... 125

Chapter 9:Creating New Layouts with the Layout Assistant ..... 127

Create a New Layout ..... 127

Standard Form ..... 128

Specify Fields ..... 128

Select a Theme ..... 129

Columnar List/Report ..... 130

Choose Report Layout ..... 130

Organize Records by Category ..... 131

Sort Records ..... 131

Specify Subtotals ..... 131

Specify Grand Totals ..... 132

Header and Footer Information ..... 132

Create a Script for this Report ..... 133

Cleaning Up ..... 133

Sub: summary Part Definition ..... 134

Table View ..... 134

Labels ..... 135

Envelope ..... 136

Blank Layout ..... 136

Summary ..... 136

Part 3: Turning Your Data into Information

Chapter 10:Keeping Your Data Clean and Neat ..... 141

Field Data Entry Options ..... 141

Auto: Enter Tab (Automatically Entering Values) ..... 142

Created/Modified (On, At, By) ..... 142

Serial Number ..... 142

Value from Previous Record ..... 142

Data ..... 143

Calculated Value ..... 143

Lookup Value ..... 143

Prohibit Modification ..... 144

Validation Tab (Checking Data for Accuracy) ..... 144

Strict Data Type ..... 145

Not Empty ..... 145

Unique ..... 145

Existing ..... 145

Member of Value List ..... 145

In Range ..... 146

Validated by Calculation ..... 146

Maximum Number of Characters ..... 146

Strict: Do Not Allow User to Override Data Validation ..... 146

Display Custom Message if Validation Fails ..... 147

Storage Tab (Repeating Fields, Storage Options, and Indexing) ..... 147

Repeating Field Options ..... 148

FileMaker's Indexing System ..... 148

Storage Options ..... 148

Field Indexing Pros and Cons ..... 149

Limitations ..... 149

Summary ..... 150

Chapter 11:Putting Your Data to Work for You ..... 153

Defining a Calculation ..... 153

Operators ..... 154

Mathematical Operators ..... 154

Comparison Operators ..... 157

Logical Operators ..... 160

Text Operators ..... 162

Other Options ..... 164

Storage Option ..... 164

Repeating Field ..... 164

Do Not Evaluate ..... 164

Limitations ..... 165

Summary ..... 165

Chapter 12:Real-World Calculations: Part 1 ..... 167

FileMaker's Help Files ..... 167

What are Functions? ..... 167

Text Functions ..... 168

Left ..... 169

A Calculation within a Calculation ..... 169

LeftWords ..... 170

Length ..... 170

Position ..... 171

Building Complex Calculations ..... 171

The Steps ..... 171

Using the Process ..... 172

Make it Permanent ..... 175

Number Functions ..... 175

The Process ..... 175

Date and Time Functions ..... 178

Time Clock ..... 179

Summary 180

Chapter 13: Real-World Calculations: Part 2 ..... 183

Aggregate Functions ..... 183

Aggregate Examples ..... 183

Summary Functions ..... 184

GetSummary Example ..... 184

Repeating Functions ..... 185

GetRepetition Example ..... 185

Financial Functions ..... 188

PMT Example ..... 188

Trigonometric Functions ..... 189

PI Example ..... 189

Logical Functions ..... 190

If and Case ..... 190

IsEmpty 191

Status Functions ..... 192

Status(CurrentRecordNumber) Example ..... 192

Design Functions ..... 192

FieldNames and FieldStyle Examples ..... 192

External Functions ..... 194

Summary ..... 195

Part 4: Creating a Real Solution

Chapter 14: Automating Your Database with Scripts ..... 199

What are Scripts? ..... 199

One Step at a Time ..... 200

Script Options ..... 201

Planning Your Script ..... 201

Where Am I Now? ..... 202

What Do I Want to Do? ..... 202

Where Do I Want to be When I Finish? ..... 203

What Can Go Wrong? ..... 203

Script Steps Overview ..... 203

Script Steps ..... 204

Control Category ..... 204

Main Menu Script Example ..... 205

Main Menu from Contacts File ..... 205

Going to Contacts ..... 206

Fail: safe ..... 207

Getting Back ..... 208

Testing ..... 208

Print Invoices Example ..... 209

The Print Script ..... 209

The Invoices Scripts ..... 210

Figuring Interest ..... 211

Printing One Invoice ..... 212

Monthly Billing Script ..... 212

Testing Monthly Billing ..... 212

Thoughtful Additions ..... 213

Debugging Scripts ..... 213

Important Hints ..... 214

Import Scripts ..... 214

Button Options (Pause, Halt, Exit, Resume) ..... 215

Startup/Shutdown Scripts ..... 216

Summary 216

Chapter 15: Making Sense of Your Information with Layouts ..... 219

What is a Layout? ..... 219

Layout Types ..... 220

On: screen Layouts (Data Entry) ..... 220

Printed Layouts ..... 224

Layout Parts ..... 226

Title Header ..... 227

Header ..... 228

Body 228

Leading and Trailing Sub: summaries ..... 228

Leading and Trailing Grand Summaries ..... 229

Footer ..... 229

Title Footer ..... 229

Working with Layout Parts ..... 229

Adding and Deleting Parts ..... 230

Modifying Parts ..... 230

Summary ..... 230

Chapter 16:Designing Your Screen Layouts ..... 233

Basic Design ..... 233

Know What You Like ..... 234

Keep it Consistent ..... 235

Group Formatting ..... 236

Selecting Groups of Layout Objects ..... 237

How to Make It Pretty ..... 237

Arrange and View Menus ..... 238

Rotate Layout Objects ..... 238

Icons ..... 238

Locking Objects on a Layout ..... 239

Adding Graphics and Movies to a Layout ..... 240

Formatting Graphics on a Layout ..... 240

Customizing the Appearance of Objects on a Layout ..... 241

Adding Borders, Baselines, and Fill to Fields ..... 242

Adding Object Effects ..... 242

Embossed ..... 242

Engraved ..... 242

Drop Shadow ..... 243

Summary ..... 243

Chapter 17: Designing Your Printed Report Layouts ..... 245

What is a Report? ..... 245

Creating an Attractive Report ..... 245

Report Types ..... 247

Creating a Report ..... 247

What Results Do You Want? ..... 247

What Data Do You Have (and Not Have)? ..... 247

Example Reports ..... 248

Customer Sales Report ..... 248

Setup: Making the Data Available ..... 248

Building the Report ..... 249

Touchup Work ..... 250

How to Set Up a Find Layout ..... 250

Percent of Sales by Product Report ..... 255

Building the Report ..... 255

Touchup Work ..... 256

How to Set Up This Find Layout ..... 256

Other Common Reports ..... 257

Avoiding the Today Function in Calculations ..... 257

Summary ..... 258

Part 5: Sharing Your Database

Chapter 18: Personal Data Sharing ..... 263

What is Personal Data Sharing? ..... 263

Capabilities ..... 264

Network Requirements ..... 264

Using Personal File Sharing ..... 266

Using Files as a Guest ..... 267

Sharing Remotely by Way of Modem ..... 268

File Sharing Caution ..... 268

Limitations ..... 269

File and Guest Limits ..... 269

Global Fields and Shared Files ..... 270

Optimizing Performance ..... 270

Hardware Considerations ..... 271

FileMaker Pro Server ..... 271

Some Final Words ..... 272

Closing Hosted Files ..... 272

Slow Network Traffic ..... 272

Summary ..... 272

Chapter 19: Sharing Your Data on the Web ..... 275

Why Publish on the Web? ..... 275

FileMaker and Instant Web Publishing ..... 276

Setting Up Your Computer ..... 276

If You Have an Internet Account ..... 276

If You Don't Have an Internet Account ..... 278

Setting up FileMaker Pro ..... 279

Web Companion Setup ..... 279

Setting Up Your Database ..... 281

Activating the Web Companion ..... 281

Selecting Your Browser Views ..... 281

Web Security ..... 285

FileMaker Based ..... 286

Database Based ..... 286

Try It Out ..... 286

Other Notes and Options ..... 287

Cool Web Sites Powered by FileMaker ..... 288

Summary ..... 289

Chapter 20:Sharing Your Data with Other Applications ..... 291

Using FileMaker's Import and Export Commands ..... 291

ODBC Sharing ..... 292

Making FileMaker Data Available to Other Applications ..... 292

Getting Data from Another Application ..... 294

Setting Up the Data Source (Macintosh) ..... 295

Importing the Data (Macintosh) ..... 298

Setting Up the Data Source (Windows) ..... 302

Importing the Data (Windows) ..... 305

Saving the Import as a Script ..... 310

Execute SQL Script Step5.5 ..... 311

Converting and Importing Data from Microsoft Excel ..... 311

Summary ..... 313

Part 6: Protecting Your Information

Chapter 21: Keeping Your Data Secure ..... 317

Why Protect Your Data? ..... 317

How to Set Up FileMaker Security ..... 317

Passwords ..... 318

Creating ..... 319

Limiting Menu Access ..... 319

Passwords Only ..... 319

Editing and Deleting Passwords ..... 320

Record: level Security5.5 ..... 321

Groups ..... 322

Creating, Editing, and Deleting Groups ..... 322

Access Privileges ..... 322

Creating ..... 323

Accessibility Guidelines ..... 323

Editing ..... 325

Cautions ..... 325

Limiting Access through Scripting ..... 325

Startup Script ..... 326

Controlling Layout Access ..... 327

Limiting Access through Field Calculations ..... 327

The Script ..... 327

The Calculation ..... 328

Internet Considerations ..... 329

Summary ..... 329

Chapter 22: Backup and Recovery ..... 333

Why Back Up? ..... 333

FileMaker Pro Server Backup Features ..... 334

Using Commercial Backup Applications ..... 334

Problems with Tape ..... 335

Database Corruption ..... 336

What is a Clone? ..... 336

Saving a Clon ..... 337

Backup Routine ..... 337

While You Work ..... 338

Other Related Measures ..... 338

FileMaker Techniques ..... 339

Using Your Backups ..... 342

Restoring ..... 343

Import Data ..... 344

Tying Up the Loose Ends ..... 345

Summary ..... 346

Part 7: Beyond Filemaker

Chapter: 23 FileMaker Mobile ..... 351

Installation Overview ..... 351

Setting Up Your Connections ..... 352

Syncing ..... 352

Mobile Companion Plug: in ..... 353

Going Mobile ..... 355

FileMaker Calendar ..... 355

DA Calendar ..... 356

Contacts ..... 356

Grocery List ..... 356

FileMaker Mobile vs. FileMaker Pro ..... 357

Similarities ..... 357

Differences and Limitations ..... 358

An Alternative ..... 358

FMSync for JFile Features ..... 358

Summary ..... 359

Appendix A Leftover, but Important Stuff ..... 361

Appendix B Getting More Help ..... 365

Index ..... 369

About the CD ..... 399