9781556229749

Preface

xvi

About the Authors

xviii

Acknowledgments

xix

Introduction

xxi

Part 1 Learning the Basics

Getting Acquainted with FileMaker Pro

(11)

Introduction

(1)

What is a Database?

(1)

Using FileMaker Pro

(2)

Opening FileMaker Pro

(1)

Opening a FileMaker Pro File

(1)

Database Concepts

(3)

Files

(1)

Records

(1)

Fields

(1)

Layouts

(1)

Saving a Copy of a FileMaker Pro File

(1)

Closing a FileMaker Pro File

(1)

Quitting FileMaker Pro

(1)

Summary

(2)

Menus and Modes

(14)

Introduction

(1)

Define Fields Dialog

(1)

Define Relationships Dialog

(3)

Lookups

(1)

Related Data

(1)

Modes

(3)

Browse Mode

(1)

Layout Mode

(2)

Layout Types

(1)

Find Mode

(1)

Preview Mode

(1)

More Menus

(3)

Sort Command

(1)

ScriptMaker

(1)

Access Privileges and Passwords

(1)

Summary

(3)

Creating Your First Database

(25)

Introduction

(1)

Planning the Database

(2)

Planning This Database

(1)

Creating the File

(2)

Adding Some Fields

(1)

Adding Field Options

(1)

Cleaning Up the Layout

(9)

Format Painter6.0

(2)

Tool Panel

(1)

Layout Toolbars

(1)

Drawing Tools

(1)

Object Grids

(1)

T-Squares

(1)

Arrow Keys

(1)

Size Palette

(1)

Alignment

(1)

Locking Objects

(1)

Adding Some Data

(1)

Adding a Script

(3)

Simple New Record Script

(1)

Simple Delete Record Script

(1)

Simple Find Script

(2)

Assigning Scripts to Buttons

(1)

Create a Button

(1)

Assign a Button to a Script

(1)

Special Situations

(2)

Summary

(5)

Part 2 Using FileMaker Pro

Creating a New Database

(14)

Introduction

(1)

Considerations

(2)

Predefined Databases

(1)

Creating Your Own

(1)

What are Fields?

(8)

Creating a Field

(1)

About Field Names

(1)

The Eight Field Types

(1)

Text Field

(1)

Number Field

(1)

Date Field

(1)

Time Field

(1)

Container Field

(1)

Calculation Field

(2)

Summary Field

(1)

Global Field

(1)

Repeating Field

(1)

Field Formats on Layouts

(2)

Date Format

(1)

Number Format

(1)

Text Format

(1)

Summary

(2)

Entering and Modifying Your Data

(18)

Introduction

(1)

Creating a New Record

(2)

Selecting the Record

(2)

Adding Data

(13)

Selecting the Field

(1)

Using Tab Order

(2)

Data Entry Shortcuts

(1)

Insert

(1)

Copy and Paste

(1)

Drag-and-Drop

(1)

Using Value Lists

(2)

Using Container Fields

(1)

Insert Movie

(1)

Insert Picture

(1)

Insert Sound

(1)

Changing Data

(1)

Highlighting

(1)

Spellchecking

(1)

Replace Command

(1)

Find/Replace6.0

(1)

Revert Command

(1)

Deleting Data

(1)

Summary

(2)

Working with Related Files---Part 1

(15)

Introduction

(1)

One or Many Files?

(1)

One File---Flat File Design

(1)

Many Files---Relational Design

(1)

Define Your Files

(12)

Normalize Your Information

(1)

Determine Your Match, or Key, Fields

(2)

Making the Relationship

(1)

Allow Creation of Related Records

(1)

Parent and Child

(1)

Allow Deletion of Related Records

(1)

Sort Related Records

(1)

Making a Portal

(1)

Drawing the Portal

(1)

Placing the Fields in a Portal

(2)

Adding Data to a Portal Field

(1)

Investigating the Related File

(2)

Relating a File Back to the Parent File

(1)

Performing a Find in a Portal

(1)

Performance Considerations

(1)

Deleting a Record from a Portal

(1)

Summary

(2)

Working with Related Files---Part 2

100

(16)

Introduction

100

(1)

Planning Your Database

101

(1)

Tricky Terms and Catchy Phrases

101

(4)

ER Diagram

101

(1)

One-to-Many Relationship

102

(1)

One-to-One Relationship

102

(1)

Many-to-Many Relationship

103

(1)

The Join, or Link, File

103

(1)

Self-join Relationship

104

(1)

Other Notes

105

(1)

Invoice System ER Diagram

105

(1)

Create the Files

106

(2)

Invoices File

106

(1)

Invoice Line Items File

107

(1)

Products File

107

(1)

Other Possibilities

107

(1)

Example Data

108

(1)

Create the Relationships

108

(2)

Add the Lookups

109

(1)

The Invoice Layout

110

(3)

Adding Pop-ups to the Invoices

110

(1)

Products Pop-up

110

(1)

Getting the Invoice Total

111

(1)

Customers Pop-up

111

(1)

Placing the Customer Field on the Layout

112

(1)

Other Considerations

113

(1)

Summary

113

(3)

Finding and Sorting Your Data

116

(19)

Introduction

116

(1)

Finding Records

117

(11)

Methods

117

(1)

The Find Symbols

118

(1)

Less Than (<)

118

(1)

Less Than or Equal (≤ or < = on Macintosh or < = on Windows)

119

(1)

Greater Than (>)

119

(1)

Greater Than or Equal (≥ or > = on Macintosh or > = on Windows)

119

(1)

Exact Match (=)

119

(1)

Range (...)

119

(1)

Duplicates (!)

119

(1)

Today's Date (//)

120

(1)

Invalid Date or Time (?)

120

(1)

One Character (@)

120

(1)

Zero or More Characters (*)

121

(1)

Literal Text (`` '')

121

(1)

Field Content Match (= =)

121

(1)

AND Finds

121

(1)

OR Finds

121

(1)

Constrain and Extend Found Set6.0

122

(1)

Constrain Found Set6.0

122

(1)

Extend Found Set6.0

123

(1)

Other Find Tricks

124

(1)

Within Scripts

124

(3)

Find Strategies

127

(1)

Limitations

128

(1)

Omitting Records

128

(1)

Methods

128

(1)

Omit Multiple

128

(1)

Omit as Part of a Find

128

(1)

Show Omitted

129

(1)

Within Scripts

129

(1)

Strategies

129

(1)

Limitations

129

(1)

Sorting Records

129

(4)

Methods

130

(1)

Within Scripts

130

(1)

Strategies

130

(1)

Sort by a Summary Field

130

(3)

Limitations

133

(1)

Summary

133

(2)

Creating New Layouts with the Layout Assistant

135

(14)

Introduction

135

(1)

Create a New Layout

136

(9)

Standard Form

136

(1)

Specify Fields

136

(1)

Select a Theme

137

(1)

Columnar List/Report

138

(1)

Choose Report Layout

139

(1)

Organize Records by Category

139

(1)

Sort Records

139

(1)

Specify Subtotals

140

(1)

Specify Grand Totals

140

(1)

Header and Footer Information

140

(1)

Create a Script for this Report

141

(1)

Cleaning Up

141

(1)

Sub-summary Part Definition

142

(1)

Table View

143

(1)

Labels

143

(2)

Envelope

145

(1)

Blank Layout

145

(1)

Summary

145

(4)

Part 3 Turning Your Data into Information

Keeping Your Data Clean and Neat

149

(12)

Introduction

149

(1)

Field Data Entry Options

150

(1)

Auto-Enter Tab (Automatically Entering Values)

150

(3)

Created/Modified (On, At, By)

150

(1)

Serial Number

151

(1)

Value from Previous Record

151

(1)

Data

151

(1)

Calculated Value

151

(1)

Lookup Value

152

(1)

Prohibit Modification

153

(1)

Validation Tab (Checking Data for Accuracy)

153

(3)

Strict Data Type

153

(1)

Not Empty

154

(1)

Unique

154

(1)

Existing

154

(1)

Member of Value List

154

(1)

In Range

154

(1)

Validated by Calculation

155

(1)

Maximum Number of Characters

155

(1)

Strict: Do Not Allow User to Override Data Validation

155

(1)

Display Custom Message if Validation Fails

156

(1)

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

156

(3)

Repeating Field Options

156

(1)

FileMaker's Indexing System

157

(1)

Storage Options

157

(1)

Field Indexing Pros and Cons

158

(1)

Limitations

158

(1)

Summary

159

(2)

Putting Your Data to Work for You

161

(14)

Introduction

161

(1)

Defining a Calculation Field

162

(11)

Operators

163

(1)

Mathematical Operators

163

(3)

Comparison Operators

166

(2)

Logical Operators

168

(2)

Text Operators

170

(2)

Other Options

172

(1)

Storage Option

172

(1)

Repeating Field

172

(1)

Do Not Evaluate

172

(1)

Limitations

173

(1)

Summary

173

(2)

Real-World Calculations---Part 1

175

(16)

Introduction

175

(1)

FileMaker's Help Files

176

(1)

What are Functions?

176

(1)

Text Functions

177

(3)

Left

178

(1)

A Calculation within a Calculation

178

(1)

LeftWords

178

(1)

Length

179

(1)

Position

179

(1)

Building Complex Calculations

180

(4)

The Steps

180

(1)

Using the Process

181

(1)

Replacing the Parameter Template

181

(1)

Testing the Results

182

(1)

Making Adjustments

182

(1)

Insert Subfunctions

183

(1)

Different Function, Same Result

183

(1)

Make It Permanent

183

(1)

Number Functions

184

(3)

The Process

184

(1)

Make an Adjustment

185

(1)

Modify Further

185

(1)

Try, Try Again

186

(1)

Case Function

186

(1)

Plug It In

186

(1)

The Real Trick

187

(1)

Date and Time Functions

187

(2)

Time Clock

188

(1)

Third Shift

188

(1)

Rebuild the Calculation

188

(1)

A Conditional Alert

189

(1)

Summary

189

(2)

Real-World Calculations---Part 2

191

(18)

Introduction

191

(1)

Aggregate Functions

192

(1)

Aggregate Examples

192

(1)

Summary Functions

193

(1)

GetSummary Example

193

(1)

Repeating Functions

194

(3)

GetRepetition Example

194

(3)

Financial Functions

197

(1)

PMT Example

197

(1)

Trigonometric Functions

198

(1)

PI Example

198

(1)

Logical Functions

199

(1)

If and Case

199

(1)

IsEmpty

200

(1)

Status Functions

200

(1)

Status (CurrentRecordNumber) Example

200

(1)

Design Functions

201

(3)

FieldNames and FieldStyle Examples

201

(1)

External Functions

202

(2)

Summary

204

(5)

Part 4 Creating a Real Solution

Automating Your Database with Scripts

209

(22)

Introduction

209

(1)

What are Scripts?

210

(2)

One Step at a Time

210

(2)

Script Options

212

(1)

Planning Your Script

212

(2)

Where Am I Now?

213

(1)

What Do I Want to Do?

213

(1)

Where Do I Want to Be When I Finish?

214

(1)

What Can Go Wrong?

214

(1)

Script Steps Overview

214

(1)

Script Steps

214

(1)

Control Category

215

(1)

Main Menu Script Example

215

(4)

Main Menu from Contacts File

216

(1)

Going to Contacts

217

(1)

Fail-safe

218

(1)

Getting Back

219

(1)

Testing

219

(1)

Print Invoices Example

219

(5)

The Print Script

220

(1)

The Invoices Scripts

220

(1)

Figuring Interest

221

(1)

Printing One Invoice

222

(1)

Monthly Billing Script

223

(1)

Testing Monthly Billing

223

(1)

Thoughtful Additions

223

(1)

Debugging Scripts

224

(1)

Important Hints

224

(1)

Import Scripts

225

(4)

Button Options (Pause, Halt, Exit, Resume)

226

(1)

Startup/Shutdown Scripts

226

(1)

Show Custom Dialog Script Step6.0

227

(2)

Summary

229

(2)

Making Sense of Your Information with Layouts

231

(14)

Introduction

231

(1)

What is a Layout?

232

(6)

Layout Types

232

(1)

On-screen Layouts (Data Entry)

232

(4)

Printed Layouts

236

(2)

Layout Parts

238

(3)

Title Header

240

(1)

Header

240

(1)

Body

240

(1)

Leading and Trailing Sub-summaries

240

(1)

Leading and Trailing Grand Summaries

241

(1)

Footer

241

(1)

Title Footer

241

(1)

Working with Layout Parts

241

(1)

Adding and Deleting Parts

242

(1)

Modifying Parts

242

(1)

Summary

242

(3)

Designing Your Screen Layouts

245

(14)

Introduction

245

(1)

Basic Design

246

(9)

Know What You Like

247

(1)

Keep It Consistent

248

(1)

Group Formatting

248

(1)

Selecting Groups of Layout Objects

249

(1)

How to Make It Pretty

250

(1)

Arrange and View Menus

250

(1)

Rotate Layout Objects

251

(1)

Icons

251

(1)

Locking Objects on a Layout

252

(1)

Adding Graphics and Movies to a Layout

253

(1)

Formatting Graphics on a Layout

253

(2)

Customizing the Appearance of Objects on a Layout

255

(1)

Adding Borders, Baselines, and Fill to Fields

255

(1)

Adding Object Effects

255

(1)

Embossed

256

(1)

Engraved

256

(1)

Drop Shadow

256

(1)

Changing Colors

256

(1)

Summary

256

(3)

Designing Your Printed Report Layouts

259

(18)

Introduction

259

(1)

What is a Report?

260

(2)

Creating a Useful and Attractive Report

260

(1)

Report Types

261

(1)

Creating a Report

262

(1)

What Results Do You Want?

262

(1)

What Data Do You Have (and Not Have)?

262

(1)

Example Reports

262

(10)

Customer Sales Report

262

(1)

Setup---Making the Data Available

263

(1)

Building the Report

263

(1)

Touchup Work

264

(1)

How to Set Up a Find Layout

265

(4)

Percent of Sales by Product Report

269

(1)

Building the Report

269

(1)

Touchup Work

270

(1)

How to Set Up This Find Layout

270

(1)

Other Common Reports

271

(1)

Avoiding the Today Function in Calculations

271

(1)

Summary

272

(5)

Part 5 Sharing Your Database

Personal Data Sharing

277

(12)

Introduction

277

(1)

What is Personal Data Sharing?

278

(2)

Capabilities

278

(1)

Network Requirements

279

(1)

Using Personal File Sharing

280

(7)

Using Files as a Guest

281

(1)

Sharing Remotely by Way of Modem

282

(1)

File Sharing Caution

283

(1)

Limitations

284

(1)

File and Guest Limits

284

(1)

Global Fields and Shared Files

284

(1)

Optimizing Performance

285

(1)

Hardware Considerations

285

(1)

FileMaker Pro Server

286

(1)

Some Final Words

286

(1)

Closing Hosted Files

286

(1)

Slow Network Traffic

287

(1)

Summary

287

(2)

Sharing Your Data on the Web

289

(18)

Introduction

289

(1)

Why Publish on the Web?

290

(1)

FileMaker and Instant Web Publishing

290

(14)

Setting Up Your Computer

290

(1)

If You Have an Internet Account

291

(3)

If You Don't Have an Internet Account

294

(1)

Setting Up FileMaker Pro

294

(1)

Web Companion Setup

294

(2)

Setting Up Your Database

296

(1)

Activating the Web Companion

296

(1)

Selecting Your Browser Views

297

(3)

Web Security

300

(1)

FileMaker Based

301

(1)

Database Based

301

(1)

Try It Out

302

(1)

Other Notes and Options

302

(2)

Cool Web Sites Powered by FileMaker

304

(1)

Summary

305

(2)

Sharing Your Data with Other Applications

307

(30)

Introduction

307

(1)

Using FileMaker's Import and Export Commands

308

(1)

ODBC Sharing

308

(18)

Making FileMaker Data Available to Other Applications

309

(1)

Getting Data from Another Application

310

(1)

Setting Up the Data Source (Macintosh)

311

(3)

Importing the Data (Macintosh)

314

(4)

Setting Up the Data Source (Windows)

318

(3)

Importing the Data (Windows)

321

(4)

Saving the Import as a Script

325

(1)

Execute SQL Script Step5.5

326

(1)

Converting and Importing Data from Microsoft Excel

327

(2)

Importing a Folder of Files in a Single Operation6.0

329

(2)

Summary

331

(6)

Part 6 Protecting Your Information

Keeping Your Data Secure

337

(16)

Introduction

337

(1)

Why Protect Your Data?

337

(1)

How to Set Up FileMaker Security

338

(9)

Passwords

339

(1)

Creating

339

(1)

Limiting Menu Access

340

(1)

Passwords Only

340

(1)

Editing and Deleting Passwords

341

(1)

Record-Level Security5.5

341

(1)

Groups

342

(1)

Creating, Editing, and Deleting Groups

343

(1)

Access Privileges

343

(1)

Creating

343

(1)

Accessibility Guidelines

344

(1)

Editing

345

(1)

Cautions

346

(1)

Limiting Access through Scripting

347

(1)

Startup Script

347

(1)

Controlling Layout Access

348

(1)

Limiting Access through Field Calculations

348

(2)

The Script

349

(1)

The Calculation

349

(1)

Internet Considerations

350

(1)

Summary

351

(2)

Backup and Recovery

353

(18)

Introduction

353

(1)

Why Back Up?

354

(2)

FileMaker Pro Server Backup Features

354

(1)

Using Commercial Backup Applications

355

(1)

Problems with Tape

355

(1)

Database Corruption

356

(2)

What is a Clone?

357

(1)

Saving a Clone

357

(1)

Backup Routine

358

(4)

While You Work

359

(1)

Other Related Measures

359

(1)

FileMaker Techniques

360

(2)

Using Your Backups

362

(5)

Restoring

363

(1)

Importing Data

364

(2)

Tying Up the Loose Ends

366

(1)

Summary

367

(4)

Part 7 Beyond FileMaker

FileMaker Mobile 2.0

371

(13)

Installation Overview

372

(5)

Setting Up Your Connections

373

(1)

Syncing

373

(1)

Mobile Companion Plug-in

374

(1)

Properties

375

(1)

Sync Me, Baby

376

(1)

Going Mobile

377

(3)

FileMaker Appointments

377

(2)

PDA Calendar

379

(1)

Contacts

379

(1)

Grocery List

380

(1)

FileMaker Mobile vs. FileMaker Pro

380

(2)

Similarities

380

(1)

Differences and Limitations

381

(1)

Summary

382

(2)

FileMaker and XML6.0

384

(11)

Introduction

384

(1)

What is XML?

385

(1)

Add in XSL

385

(1)

What Can I Use It For?

386

(1)

Examples: Exporting XML as HTML

387

(4)

Using a Local XSL Style Sheet

387

(1)

Creating the Export XML Script

388

(1)

Using a Remote XSL Style Sheet

389

(1)

And Beyond...

390

(1)

Profile of a FileMaker XML Web Request

391

(1)

Import XML

391

(1)

Open XML

392

(1)

Summary

392

(3)

Appendix A Leftover, but Important Stuff

395

(6)

Appendix B Getting More Help

401

(4)

Appendix C Answers to Review Questions

405

(18)

Appendix D Glossary

423

Index

369

(30)

About the CD

399

Amazon no longer offers textbook rentals. We do!

Amazon no longer offers textbook rentals. We do!

We're the #1 textbook rental company. Let us show you why.

Learn Filemaker Pro 6

1556229747

Summary

Table of Contents

Supplemental Materials

Rewards Program