FileMaker Pro 11 · the Missing Manual by Gripman, Stuart, Prosser, Susan -- Read -- Imperial Library of Trantor

Creating Fields Managing Field Types Creating Records in Table View Inserting a File into a Container Field Exporting Field Contents Inserting a File Using QuickTime

Understanding Layouts

The Layout Bar The Layout Status Toolbar and Layout Mode Layout Objects

Customizing a Layout

Editing Text Objects and Fields

Moving and resizing fields Using object grids Viewing sample data Editing text objects Deleting objects Adding text objects

Using the Inspector Inserting a Picture on a Layout Adding Lines and Shapes Formatting Fields

Formatting a Number field Adding a field control style and a value list Editing a value list Adding a drop-down calendar to a field Controlling field borders

Customizing a Layout's Body Part

Adding color to a layout part Adjusting the Body part's height

Creating a New Layout

The Format Painter Making Two Layouts Match

T-Squares

Arrange and Align Tools

Aligning and distributing

Aligning and distributing Resizing Grouping/Locking Arranging Rotating

4. Adding Power to Your Database

Creating a Simple Calculation

Creating Fields with Manage→Database Adding New Fields to a Layout

Creating a Related Table

Understanding the Elements of a Relationship Creating a Key Field with an Auto-Enter Serial Number

Using Replace Field Contents to add serial numbers

Creating a New Table

Viewing the new table's layout

Creating a Relationship Between Two Table Occurrences

Creating and Using Portals

Adding a Portal to a Layout Resizing and Moving a Portal Context Creating Records Through a Portal Editing Records Through a Portal

Deleting records through a portal

Performing Finds with Related Data

Using Tab Controls

Creating a Tab Control Editing Tab Controls

Adding, removing, and reordering tabs Default Front Tab Tab justification Appearance Tab width Formatting a Tab Control

Deleting a Tab Control

Adding Merge Fields

Using Symbols to Show Important Info

Writing a Basic Script

Creating a Sort Script Creating a Button Applying a Script Trigger

Creating a Dynamic Report with the Assistant Creating a Trailing Group Report Changing the Default Account Summing Up

three. Thinking Like a Developer

5. Creating and Managing a Relational Database

Understanding Relational Databases Modeling Your Database

Choosing Entities Finding Relationships

One-to-many relationships One-to-one relationships Many-to-many relationships

The Entity-Relationship Diagram

Creating an ER diagram

Keys

Primary and foreign keys Choosing a good primary key field

Join Tables

Creating a Relational Database

Creating Relationships Sorting a Relationship Managing the Relationships Graph

Managing table occurrences using the Tables/Relationships tools Arrange the graph Selection tools Color your table occurrences Adding notes Printing the graph

Using Relational Database Power

One Table Occurrence, One Found Set Viewing Related Fields on a Layout Creating a Value List Based on a Related Field

Lookups

Creating Lookups Using a Relookup Navigating Between Related Records

Go to Related Record Using GTRR to create a found set

Reviewing Relationship Concepts

Bidirectionality Implicit Relationships in Action

6. Field Options

Understanding Field Types

Text Number Date Time Timestamp Container Calculation Summary

Auto-Enter Field Options

Creation Values

Using Creation Name and Account Name

Modification Values Serial Number Value from Last Visited Record Data Calculated Value Looked-up Value

Validation Options

Making Validation Stricter Validation Requirements

Strict data type Not empty Unique Existing value Member of value list In range Maximum number of characters

Storage Options

Global Storage Repeating Fields Indexing

Automatic indexing Controlling indexing manually Indexing language Seeing the index

Printing Field Definitions

Beyond Text: Container Fields

Pictures

Copy and paste Insert→Picture

QuickTime Sound

Recording sound

File

Exporting data from container fields

7. Layout Tools

Layout Parts

Parts in Form View Parts in List View Parts in Preview Mode Part Setup Dialog Box When to Use Each Type Arranging Parts

Layout Types

Standard Form Table View List View Report Blank Layout Labels or Vertical Labels Envelope Renaming a Layout Setting Layout View Found Sets and Layouts Layout Setup Manage Layouts

The Inspector's Data Tab

Field Control

Advanced Field Controls

Edit Box Drop-Down List Pop-Up Menu Checkbox Set Radio Button Set Drop-Down Calendar Auto-Complete

Auto-Complete in Edit Boxes Auto-Complete in drop-down lists

Repetitions Display Data From Field Behavior

Data Formatting

Number Formatting

General As entered Boolean Decimal Currency Percent

Date Formatting

As entered Preset Styles Custom

Time Formatting

As entered Other Formats

Timestamp Formatting Graphic Formatting

Format Alignment

Buttons

Creating Buttons with the Button Tool Turning an Existing Layout Object into a Button Making a Button Not a Button Button Actions

Tab Order

Customizing the Tab Order Fixing the Tab Order in the People Database Preserving the Automatic Order

Merge Fields Merge Variables Tooltips Autoresizing

Autoresizing in List Layouts

Creating Layouts for Printing

Visualize the Result in Preview Mode Print Margins Columns Sliding Layout Objects

When to use sliding Choosing the objects to slide Setting sliding options More Sliding & Visibility options

8. Understanding Calculations

Understanding Calculations

Creating a Calculation

The Structure of a Calculation

Using Fields in Calculations Using Constants in Calculations

Number constants Text constants

Using Operators in Calculations

Mathematical operators The concatenation operator Comparison operators Logical operators The ^ operator Parentheses

Functions

Text functions Text formatting functions Number functions Date functions Time functions Timestamp functions Aggregate functions Summary functions Repeating functions Financial functions Trigonometric functions Logical functions Get functions Design functions Custom functions External functions

Expressions

Using a Related Field in a Calculation Understanding the Specify Calculation Dialog Box

Table occurrence context Field list Operators Function list Result Type Calculation Box Repetitions Changing the Standard Evaluation Behavior

Auto-Enter Calculations Validate Data Entry with a Calculation Replacing Data Using a Calculation Comments

9. More Calculations and Data Types

Number Crunching Calculations

Number Function Types Using Number Functions

Abs() Average() Count() Floor() and Ceiling() List() Round() SetPrecision()

Going Beyond Basic Calculations

Reviewing the Data and New Business Rules Planning the Calculations Constructing the Calculation

Text Calculations

Text Functions

Text function types Character functions Word functions Text editing functions Text value functions Text counting functions

Text Formatting Functions

TextColor() and RGB() TextFont() TextSize() TextStyleAdd() and TextStyleRemove()

Date and Time Calculations

Math with Dates and Times

Dates Times

Parsing Dates and Times Calculations that Create Dates and Times

The secret powers of Date()

Containers in Calculations

Calculations with Pictures, Sounds, Movies, and Files Calculations with References

10. Understanding Scripts

Understanding Scripts Your First Script

Preparing the Database Planning Your Script Creating Your Script Improving Your Script

Shortcuts to the Edit Script Window The Importance of the Layout

When to Switch

Running Scripts

The Scripts Menu Buttons Script Triggers Performing Scripts When a File Is Opened and Closed Timer Scripts

Organizing Your Scripts

Creating Script Folders Creating Menu Separators

Branching and Looping in Scripts

Branching with If, Else If, Else, and End If Testing Multiple Conditions Looping

Using Go to Record/Request/Page to exit a loop

Script Triggers

Creating a Simple Script Trigger Layout Triggers

OnRecordLoad OnRecordCommit OnRecordRevert OnLayoutKeystroke OnLayoutEnter OnLayoutExit OnModeEnter and OnModeExit OnViewChange

Object Triggers

OnObjectEnter OnObjectKeystroke OnObjectModify OnObjectValidate OnObjectSave OnObjectExit

11. Exploring Script Steps

Go to Layout Go to Object

Naming objects

Naming objects Using Go To Object in a Script

Scripting for Fields

Navigating Fields

Go to Field Go to Next Field and Go to Previous Field

Editing Commands Selecting Text Editing Field Data

Avoiding Cut, Copy, and Paste Set Field Set Field by Name Insert Calculated Result Inserting other values Putting data in container fields

A Field Script in Action

Building the script

Other Steps That Work with Fields

Replace Field Contents and Relookup Field Contents Set Next Serial Value Perform Find/Replace

Working with Records

Creating, Duplicating, and Deleting Records Navigating Among Records

Go to Record/Request/Page Go to Related Record

Opening, Reverting, and Committing Records

Open Record/Request Commit Record/Request Revert Record/Request

Copying Records

Copy Record/Request Copy All Records/Requests

Working with Portals

Go to Portal Row

Finding Records

Performing People's Find Requests

Triggering a Find script

Static Find Requests Using a Variable to Create Dynamic Find Requests Constraining and Extending the Found Set Omitting Records Modify Last Find

Sorting Records

Sorting Records with an OnRecordLoad Trigger

Working with Windows

Creating Windows Bringing a Window to the Front Closing a Window Moving and Resizing Existing Windows

Adjust Window Arrange All Windows Move/Resize Window

Other Window-Related Script Steps

Working with Files

Opening and Closing Files Save a Copy As Other File-Related Script Steps

Printing Other Script Steps

Open URL Dial Phone Set Web Viewer Execute SQL Flush Cache to Disk Exit Application

four. Becoming a Power Developer

12. Applying Developer Utilities

Copying and Pasting Database Structure

Importing Tables and Fields Copying and Pasting Tables Copying and Pasting Fields Copying Scripts and Script Steps

Script Debugger

Controlling Script Execution Breakpoints Examining Errors Pause on Error The Call Stack Working with the Debugger Window

The Data Viewer

The Current Tab The Watch Tab

Disable Script Steps The Database Design Report

Generating the DDR Using the DDR Finding Broken Elements with the DDR

Custom Functions

Defining a Custom Function Editing Custom Functions Sharing Custom Functions

Custom Menus

Editing a Menu Creating a New Menu Using Existing Commands Submenus Removing a Menu Installing Custom Menu Sets

Developer Utilities

Using the Developer Utilities

Renaming files Create runtime solution application(s) Remove admin access from files permanently Enable Kiosk mode for non-admin accounts Databases must have a FileMaker file extension Create error log for any processing errors Loading and saving settings

Delivering a Runtime Solution

File Maintenance

13. Advanced Relationship Techniques

Advanced Relationships

Self-Join Relationships

No ambiguity or circularity allowed

Multiple Criteria Relationships

Editing a relationship's criteria

Relationship Operators

Portal Filtering Understanding Table Occurrences

Adding a new key field to a table

Adding a new key field to a table Adding a table occurrence Adding fields for new table occurrences

Table Occurrence Groups

Listing the New Elements You'll Need Understanding Table Occurrence Groups Creating a New Table Occurrence Group Building the Assign Expenses Layout

Duplicating and editing an existing layout Copying a portal from another layout Using GTRR to switch TOGs

Understanding Graph Arrangements Connecting Database Files

Adding a Table Occurrence from Another File Defining Data Sources Using a Path

Using Multiple Relationship Techniques

Creating the Invoice Finder Layout

14. Reporting and Analysis

Summary Fields and Sub-summary Parts

Summary Fields Other Summary Field Types

Total of Average of Count of Minimum and Maximum Standard Deviation of Fraction of Total of Summarizing repetitions

Advanced Sub-summary Parts

Multiple Sub-summary parts on one layout

Leading and Trailing Grand Summaries Sub-summary Parts and Printing

Web Viewer Objects

Putting a Web Viewer on a Layout Web Viewer Options

Allow interaction with web viewer content Display content in Find mode Display progress bar Display status messages

Conditional Formatting

Conditional Formatting of Fields Conditional Formatting of Text Objects Advanced Conditional Formatting Removing Conditional Formatting

Basic Charting (Found Set Charts)

Bar

Bar Horizontal Bar Line Area Pie

Chart Formatting Charting and Reports

Picking the Right Fields Choosing an Appropriate Layout Part Using the Correct Sort Order

Advanced Charting

Delimited Data Related Records

15. Advanced Calculations

Understanding Boolean Functions

Using the Self Function

Using Storage Options

Understanding Stored and Unstored Calculation Fields

Field dependencies Deciding when to store Indexing calculations

Global Calculation Fields

Logical Functions

Conditional Functions

The If() function The Case() function The Choose() function

Constructing a Conditional Calculation

Total Due calculation #1: Using the If() function Total Due calculation #2: Using the Case() function

The Let() Function and Variables

Defining Calculation Variables

Total Due calculation #3: Using the Let function

The Life of a Variable

Local variables Global variables

Nesting Let Functions

Recursion

Step 1: Solve the First Case Step 2: Assume Your Function Already Works, and Use It Step 3: Find a Stopping Point

Plug-Ins

Installing Plug-ins Old and New Plug-ins Finding Plug-ins Creating Your Own Plug-ins

16. Advanced Scripting

Commenting Scripts Importing Scripts Communicating with Database Users

Showing a Simple Message Asking a Simple Question Asking a More Complex Question

Organizing Complex Scripts

The Perform Script Script Step Halt Script and Exit Script

Exit Script (Result) Organizing scripts that use Halt or Exit Script

Script Parameters Script Variables Handling Errors

The Set Error Capture Script Step The Get ( LastError ) Function The Allow User Abort Script Step

Advanced Script Triggers

Install OnTimer Script Keystroke Triggers

Detecting which keys were pressed Creating a simple keystroke trigger Creating a keystroke filter

Putting a Complex Script Together

Planning the Script Considering Exceptions Creating Subscripts

Creating subscript placeholders Creating skeleton subscripts

Finishing the Subscripts

The Find Unbilled Activity subscript Copying and editing existing script steps Adding a script parameter to a Perform Script step Checking a subscript's result Finishing the Create Invoice Record subscript Finishing the Process Timeslips subscript The Process Expenses subscript

Testing Scripts

five. Integration and Security

17. Sharing Your Database

FileMaker Network Sharing

Setting Up a Host Computer Opening a Shared File The Open Remote File Dialog Box

Choosing a host computer Choosing a file

Sharing over the Internet

Turning on Web Sharing

Advanced Web Publishing Options Configuring file access

Connecting from a Web Browser Custom Home Page

FileMaker Server

File Compatibility Installing FileMaker Server

Single Machine installation Multiple Machine installation Configuring your server

Administering FileMaker Server

Installing a database Scheduling a backup

Testing Your Server

Server Hardware

18. Adding Security

How Security Works

Who Gets Access What They Can Do

Privilege Sets

Understanding Privilege Sets

The built-in privilege sets Custom privilege sets

Understanding Individual Privileges

Other privileges Creating a privilege set Data Access and Design privileges Creating record-level access Editing a privilege set

Managing Accounts

Adding a New Account

Testing your new account

External Authentication

Extended Privileges

Creating an Extended Privilege Checking for an Extended Privilege Assigning Extended Privileges

Managing External File Access Scripts and Security

Detecting Privileges in a Script Handling Security Errors Running Scripts with Full Access Privileges Managing Security with Scripts

Add Account Delete Account Reset Account Password Change Password Enable Account Re-Login

19. Sharing Data with Other Systems

Sharing Your Data with Others

Save/Send Records As

Saving as Microsoft Excel Saving as Portable Document Format (PDF)

Snapshot Link External SQL Sources

Setting Up ODBC

The SQL database server Installing the ODBC driver Configuring the data source Finishing ODBC data source configuration

Connecting FileMaker to a SQL Data Source Adding SQL Tables to a FileMaker Database

Creating relationships Shadow tables

Using SQL Tables

Exporting Data

Grouped Exports

Export formats

Importing Data

Recurring Import Importing over Existing Data

Update existing records in found set Update matching records in found set

Creating Tables on Import Creating a New Database from an Import File Converting Older FileMaker Files

Import Data Sources

File Folder

Importing a folder of pictures or movies Importing a folder of text files

Digital Camera XML Data Source

XML export

Bento Data Source ODBC Data Sources

Importing and Exporting in a Script

Save Records Script Steps The Import Records Script Step The Export Records Script Step

six. Appendixes

A. Getting Help

Getting Help from FileMaker Pro FileMaker's Installed Extras

Starter Solutions Hands-On Tutorial XML Examples

The Quick Start Screen

Getting Help from FileMaker, Inc. The Resource Center Updaters and Downloads Web Seminars FileMaker Training Series Technical Support

Free support Paid support

Developer Programs

TechNet FileMaker Business Alliance

DevCon

Getting Help from the Community

Local User Groups Mail Lists and Newsgroups Training/Consultants

B. FileMaker Error Codes

Index About the Authors SPECIAL OFFER: Upgrade this ebook with O’Reilly

← Prev
Back
Next →

← Prev
Back
Next →