Learn Filemaker Pro 5.0

by Stars, Jonathan

• ISBN13: 9781556228001
• ISBN10: 1556228007
• Format: Paperback
• Copyright: 2000-09-01
• Publisher: Jones & Bartlett Pub

Summary

"Learn FileMaker Pro 5.0" is designed to teach the fundamentals of this cross-platform relational database program to both Macintosh and PC users, while also providing more advanced topics for intermediate users. Beginners learn the basic elements of FileMaker in a straightforward, logical manner, complete with hands-on exercises and chapter quizzes. For those who have experience with FileMaker, the book includes information on troubleshooting and debugging scripts, creating a report based on a date range, and how to turn a field into a button. By the time readers complete this book, they will have a set of files they can use to keep track of customers and invoices. The appendixes address issues such as network error messages and technical support. In addition, an extensive index with more than 2,500 entries guides the reader to specific FileMaker topics. The companion CD-ROM contains the example files in both Windows and Macintosh formats, along with other FileMaker developer solutions and plug-ins. Jonathan Stars is a software consultant in Michigan, where he specializes in creating FileMaker databases. Among his many projects, he has developed databases for government agencies in Michigan, music publishers, and universities. He is a member of the FileMaker Solution Alliance and writes for "FileMaker Pro Advisor" magazine.

Table of Contents

Preface	xv
Acknowledgments	xvii
Introduction	xix
Conventions	xxi
Part 1---Learning the Basics
Getting Acquainted with FileMaker Pro	3	(10)
What is a Database?	4	(1)
Using FileMaker Pro	5	(1)
Opening a FileMaker Pro	5	(1)
Opening a FileMaker Pro File	6	(1)
Database Concepts	6	(3)
Files	6	(1)
Records	7	(1)
Fields	8	(1)
Layouts	8	(1)
Saving a Copy of a FileMaker Pro File	9	(1)
Closing a FileMaker Pro File	10	(1)
Quitting FileMaker Pro	10	(1)
Summary	11	(2)
Menus and Modes	13	(14)
Define Fields	13	(2)
Define Relationships	15	(2)
Lookups	16	(1)
Related Data	17	(1)
Modes	17	(4)
Browse Mode	18	(1)
Layout Mode	18	(2)
Layout Types	20	(1)
Find Mode	21	(1)
Preview Mode	21	(1)
More Menus	21	(3)
Sort Command	21	(1)
ScriptMaker	22	(1)
Access Privileges and Passwords	23	(1)
Summary	24	(3)
Creating Your First Database	27	(22)
Planning the Database	27	(3)
Planning This Database	28	(2)
Creating the File	30	(2)
Adding Some Fields	30	(1)
Adding Field Options	31	(1)
Cleaning Up the Layout	32	(7)
Tool Panel	34	(1)
Layout Toolbar	35	(1)
Drawing Tools	36	(1)
Object Grids	36	(1)
T-Squares	36	(1)
Arrow Keys	37	(1)
Size Palette	37	(1)
Alignment	38	(1)
Locking Objects	38	(1)
Adding Some Data	39	(1)
Adding a Script	40	(2)
Simple New Record Script	40	(1)
Simple Delete Record Script	40	(1)
Simple Find Script	41	(1)
Assigning Scripts to Buttons	42	(1)
Create a Button	42	(1)
Assign a Button to a Script	43	(1)
Special Situations	43	(2)
Summary	45	(4)
Part 2---Using FileMaker Pro
Creating a New Database	49	(14)
Create a New Database	49	(1)
Predefined Databases	49	(1)
Creating Your Own	50	(1)
What are Fields?	50	(9)
Creating a Field	50	(1)
About Field Names	51	(1)
The Eight Field Types	52	(1)
Text Field	52	(1)
Number Field	52	(1)
Date Field	53	(1)
Time Field	53	(1)
Container Field	53	(1)
Calculation Field	54	(2)
Summary Field	56	(1)
Global Field	57	(1)
Repeating	58	(1)
Field Formats on Layouts	59	(2)
Date Format	59	(1)
Number Format	60	(1)
Text Format	60	(1)
Summary	61	(2)
Entering and Modifying Your Data	63	(16)
Creating a New Record	63	(3)
Selecting the Record	64	(2)
Adding Data	66	(11)
Selecting the Field	66	(1)
Using Tab Order	67	(2)
Data Entry Shortcuts	69	(1)
Insert	69	(1)
Copy and Paste	70	(1)
Drag-and-Drop	71	(1)
Changing Data	71	(2)
Deleting Data	73	(1)
Using Value Lists	74	(1)
Using Container Fields	75	(1)
Insert Movie	76	(1)
Insert Picture	76	(1)
Insert Sound	76	(1)
Summary	77	(2)
Working with Related Files---Part 1	79	(14)
One or Many Files?	79	(1)
One File---Flat File Design	79	(1)
Many Files---Relational Design	79	(1)
Define Your Files	80	(11)
Normalize Your Information	81	(1)
Determine Your Match or Key Fields	81	(1)
Parent and Child	82	(1)
Making the Relationship	82	(1)
Allow Creation of Related Records	83	(1)
Allow Deletion of Related Records	83	(1)
Sort Related Records	84	(1)
Making a Portal	85	(1)
Drawing the Portal	85	(1)
Placing the Fields in a Portal	86	(1)
Adding Data to a Portal Field	87	(1)
Investigating the Related File	88	(1)
Relationship Back to the Parent File	89	(1)
Performing a Find in a Portal	89	(1)
Performance Considerations	90	(1)
Deleting a Record from a Portal	90	(1)
Summary	91	(2)
Working with Related Files---Part 2	93	(16)
Planning Your Database	93	(1)
Tricky Terms and Catchy Phrases	94	(3)
ER Diagram	94	(1)
One-to-many Relationship	94	(1)
One-to-one Relationship	94	(1)
Many-to-many Relationship	95	(1)
The Join, or Link, File	95	(1)
Self-join Relationship	96	(1)
Other Notes	97	(1)
Invoice System ER Diagram	97	(1)
Create the Files	98	(2)
Invoices File	98	(1)
Invoice Line Items File	99	(1)
Products File	99	(1)
Other Possibilities	100	(1)
Example Data	100	(1)
Create the Relationships	100	(2)
Add the Lookups	101	(1)
The Invoice Layout	102	(4)
Adding Pop-ups to the Invoices	103	(1)
Products Pop-up	103	(1)
Getting the Invoice Total	104	(1)
Customers Pop-up	104	(1)
Placing the Customer Field on the Layout	105	(1)
Other Considerations	105	(1)
Summary	106	(3)
Finding and Sorting Your Data	109	(16)
Finding Records	109	(9)
Methods	109	(1)
The Find Symbols	110	(1)
Less Than (<)	111	(1)
Less Than or Equal (≤ or < = on Macintosh or < = on Windows)	111	(1)
Greater Than (>)	111	(1)
Greater Than or Equal (≥ or > = on Macintosh or > = on Windows)	111	(1)
Exact Match (=)	111	(1)
Range (...)	112	(1)
Duplicates (!)	112	(1)
Today's Date (//)	112	(1)
Invalid Date or Time (?)	112	(1)
One Character (@)	113	(1)
Zero or More Characters (*)	113	(1)
Literal Text (`` '')	113	(1)
Field Content Match (= =)	113	(1)
AND Finds	113	(1)
OR Finds	114	(1)
Other Tricks	114	(1)
Within Scripts	114	(3)
Find Strategies	117	(1)
Limitations	118	(1)
Omitting Records	118	(1)
Methods	118	(1)
Omit Multiple	118	(1)
Omit as Part of a Find	118	(1)
Show Omitted	119	(1)
Within Scripts	119	(1)
Strategies	119	(1)
Limitations	119	(1)
Sorting Records	119	(4)
Methods	120	(1)
Within Scripts	120	(1)
Strategies	120	(1)
Sort by a Summary Field	120	(3)
Limitations	123	(1)
Summary	123	(2)
Creating New Layouts the Easy Way (Using the Layout Assistant)	125	(14)
Create a New Layout	125	(9)
Standard Form	126	(1)
Specify Fields	126	(1)
Select a Theme	127	(1)
Columnar List/Report	128	(1)
Choose Report Layout	128	(1)
Organize Records by Category	129	(1)
Sort Records	129	(1)
Specify Subtotals	129	(1)
Specify Grand Totals	130	(1)
Header and Footer Information	130	(1)
Create a Script for this Report	131	(1)
Cleaning Up	131	(1)
Sub-summary Part Definition	132	(1)
Table View	132	(1)
Labels	132	(2)
Envelope	134	(1)
Blank Layout	134	(1)
Summary	134	(5)
Part 3---Turning Your Data into Information
Keeping Your Data Clean and Neat	139	(12)
Field Data Entry Options	139	(9)
Auto-Entered Values	140	(1)
Created/Modified (On, At, By)	140	(1)
Serial Number	140	(1)
Value from Previous Record	140	(1)
Data	141	(1)
Calculated Value	141	(1)
Lookup Value	141	(1)
Prohibit Modification	142	(1)
Validation Options (Checking Data for Accuracy)	142	(1)
Of Type	142	(1)
Not Empty	143	(1)
Unique	143	(1)
Existing	143	(1)
Member of Value List	143	(1)
In Range	143	(1)
Validated by Calculation	144	(1)
Strict: Do Not Allow User to Override Data Validation	144	(1)
Display Custom Message if Validation Fails	144	(1)
Repeating Fields, Storage Options, and Indexing	145	(1)
Repeating Field Options	145	(1)
FileMaker's Indexing System	146	(1)
Storage Options	146	(1)
Field Indexing Pros and Cons	147	(1)
Limitations	147	(1)
Summary	148	(3)
Putting Your Data to Work for You	151	(14)
Defining a Calculation Field	151	(12)
Operators	152	(1)
Mathematical Operators	152	(3)
Comparison Operators	155	(3)
Logical Operators	158	(2)
Text Operators	160	(2)
Other Options	162	(1)
Storage Option	162	(1)
Repeating Field	162	(1)
Do Not Evaluate	162	(1)
Limitations	163	(1)
Summary	163	(2)
Real World Calculations---Part 1	165	(16)
FileMaker's Help Files	165	(1)
What are Functions?	165	(13)
Text Functions	166	(1)
Left	167	(1)
A Calculation within a Calculation	167	(1)
LeftWords	168	(1)
Length	168	(1)
Position	169	(1)
Building Complex Calculations	169	(1)
The Steps	169	(1)
Using the Process	170	(3)
Make it Permanent	173	(1)
Number Functions	173	(3)
Date and Time Functions	176	(1)
Time Clock	177	(1)
Summary	178	(3)
Real World Calculations---Part 2	181	(16)
Aggregate Functions	181	(1)
Aggregate Examples	181	(1)
Summary Functions	182	(1)
GetSummary Example	182	(1)
Repeating Functions	183	(3)
GetRepetition Example	183	(3)
Financial Functions	186	(1)
PMT Example	186	(1)
Trigonometric Functions	187	(1)
PI Example	187	(1)
Logical Functions	188	(2)
If and Case	188	(1)
IsEmpty	189	(1)
Status Functions	190	(1)
Status(CurrentRecordNumber) Example	190	(1)
Design Funcitons	190	(2)
FieldsNames and FieldStyle Examples	190	(2)
External Functions	192	(1)
Summary	193	(4)
Part 4---Creating a Real Solution
Automating Your Database with Scripts	197	(20)
What are Scripts?	197	(2)
One Step at a Time	198	(1)
Script Options	199	(1)
Planning Your Script	199	(2)
Where Am I Now?	200	(1)
What Do I Want to Do?	200	(1)
Where Do I Want to be When I Finish?	201	(1)
What Can Go Wrong?	201	(1)
Script Steps Overview	201	(2)
Script Steps	202	(1)
Control Category	202	(1)
Main Menu Script Example	203	(4)
Main Menu from Contacts File	203	(1)
Going to Contacts	204	(1)
Fail-safe	205	(1)
Getting Back	206	(1)
Testing	206	(1)
Print Invoices Example	207	(4)
The Print Script	207	(1)
The Invoices Scripts	208	(1)
Figuring Interest	209	(1)
Printing One Invoice	210	(1)
Monthly Billing Script	210	(1)
Testing Monthly Billing	210	(1)
Thoughtful Additions	211	(1)
Debugging Scripts	211	(1)
Import Hints	212	(1)
Import Scripts	212	(2)
Button Options (Pause, Halt, Exit, Resume)	213	(1)
Startup/Shutdown Scripts	213	(1)
Summary	214	(3)
Making Sense of Your Information with Layouts	217	(14)
What is a Layout?	217	(7)
Layout Types	218	(1)
On-screen Layouts (Data Entry)	218	(4)
Printed Layouts	222	(2)
Layout Parts	224	(3)
Title Header	225	(1)
Header	226	(1)
Body	226	(1)
Leading and Trailing Sub-summaries	226	(1)
Leading and Trailing Grand Summaries	227	(1)
Footer	227	(1)
Title Footer	227	(1)
Working with Layout Parts	227	(1)
Adding and Deleting Parts	228	(1)
Modifying Parts	228	(1)
Summary	228	(3)
Designing Your Screen Layouts	231	(12)
Basic Design	231	(9)
Know What You Like	232	(1)
Keep it Consistent	233	(1)
Group Formatting	234	(1)
Selecting Groups of Layout Objects	234	(1)
How to Make it Pretty	235	(1)
Arrange and View Menus	235	(1)
Rotate Layout Objects	236	(1)
Icons	236	(1)
Locking Objects on a Layout	237	(1)
Adding Graphics and Movies to a Layout	238	(1)
Formatting Graphics on a Layout	238	(1)
Customizing the Appearance of Objects on a Layout	239	(1)
Adding Borders, Baselines, and Fill to Fields	239	(1)
Adding Object Effects	240	(1)
Summary	240	(3)
Designing Your Printed Report Layouts	243	(18)
What is a Report?	243	(2)
Creating an Attractive Report	243	(2)
Report Types	245	(1)
Creating a Report	245	(1)
What Results Do You Want?	245	(1)
What Data Do You Have (and Not Have)?	245	(1)
Example Reports	246	(10)
Customer Sales Report	246	(1)
Setup---Making the Data Available	246	(1)
Building the Report	247	(1)
Touchup Work	248	(1)
How to Set Up a Find Layout	248	(5)
Percent of Sales by Product	253	(1)
Building the Report	253	(1)
Touchup Work	254	(1)
How to Set Up This Find Layout	254	(1)
Other Common Reports	255	(1)
Avoiding the Today Function in Calculations	255	(1)
Summary	256	(5)
Part 5---Sharing Your Database
Personal Data Sharing	261	(12)
What is Personal Data Sharing?	261	(3)
Capabilities	262	(1)
Network Requirements	262	(2)
Using Personal File Sharing	264	(6)
Using Files as a Guest	265	(1)
Sharing Remotely by Way of Modem	265	(1)
File Sharing Caution	266	(1)
Limitations	267	(1)
Limited Number of Guests	267	(1)
Global Fields and Shared Files	267	(1)
Optimizing Performance	268	(1)
Hardware Considerations	269	(1)
FileMaker Pro Server	269	(1)
Some Final Words	269	(1)
Closing Hosted Files	269	(1)
Slow Network Traffic	270	(1)
Summary	270	(3)
Sharing Your Data on the Web	273	(14)
Why Publish on the Web?	273	(1)
FileMaker and Instant Web Publishing	274	(11)
Setting Up Your Computer	274	(1)
If You Have an Internet Account	274	(2)
If You Don't Have an Internet Account	276	(1)
Setting up FileMaker Pro	276	(1)
Web Companion Setup	276	(1)
Setting Up Your Database	276	(2)
Activating the Web Companion	278	(1)
Selecting Your Browser Views	279	(3)
Web Security	282	(1)
FileMaker Based	282	(1)
Database Based	283	(1)
Try it Out	283	(1)
Other Notes and Options	284	(1)
Cool Web Sites Powered by FileMaker	285	(1)
Summary	285	(2)
Sharing Your Data with Other Applications	287	(24)
Using FileMaker's Import and Export Commands	287	(1)
ODBC Sharing	288	(19)
Making FileMaker Data Available to Other Applications	288	(2)
Getting Data from Another Application	290	(1)
Setting Up the Data Source (Macintosh)	291	(3)
Importing the Data (Macintosh)	294	(4)
Setting Up the Data Source (Windows)	298	(3)
Importing the Data (Windows)	301	(5)
Saving the Import as a Script	306	(1)
Summary	307	(4)
Part 6---Protecting Your Information
Keeping Your Data Secure	311	(14)
Why Protect Your Data?	311	(1)
How to Set Up FileMaker Security	311	(7)
Passwords	312	(1)
Creating	313	(1)
Limiting Menu Access	313	(1)
Passwords Only	313	(1)
Editing and Deleting Passwords	314	(1)
Groups	314	(1)
Creating, Editing, and Deleting Groups	315	(1)
Access Privileges	315	(1)
Creating	315	(1)
Accessibility Guidelines	316	(1)
Editing	317	(1)
Cautions	317	(1)
Limiting Access Through Scripting	318	(1)
Startup Script	318	(1)
Controlling Layout Access	319	(1)
Limiting Access Throught Calculations	319	(2)
The Script	320	(1)
The Calculation	320	(1)
Internet Considerations	321	(1)
Summary	322	(3)
Backup and Recovery	325	(14)
Why Back Up?	325	(2)
FileMaker Pro Server Backup Features	326	(1)
Using Commercial Backup Applications	326	(1)
Problems with Tape	327	(1)
Database Corruption	327	(2)
What is a Clone?	328	(1)
Saving a Clone	329	(1)
Backup Routine	329	(5)
While You Work	330	(1)
Other Related Measures	330	(1)
FileMaker Techniques	331	(3)
Using Your Backups	334	(3)
Restoring	334	(1)
Import Data	335	(2)
Tying Up the Loose Ends	337	(1)
Summary	337	(2)
Appendix A Leftover, but Important Stuff	339	(4)
Appendix B Getting More Help	343	(4)
Index	347