book

FileMaker Pro 11: The Missing Manual

by Stuart Gripman, Susan Prosser

May 2010

Beginner to intermediate

915 pages

33h 1m

English

O'Reilly Media, Inc.

Read now

Unlock full access

FileMaker Pro 11: The Missing Manual
A Note Regarding Supplemental Files
The Missing Credits
About the AuthorsAbout the Creative TeamAcknowledgmentsThe Missing Manual Series
Introduction
Why FileMaker Pro?What’s New in FileMaker Pro 11The Very BasicsAbout This BookAbout the OutlineLiving ExamplesMacintosh and WindowsAbout→These→ArrowsAbout MissingManuals.comSafari® Books Online
one. Getting Started with FileMaker
1. Working with a Database
A Very Quick Database TourContent AreaScroll BarsZoom ControlsUnderstanding ModesStatus toolbarOpening and Closing Database FilesOpening a DatabaseClosing a DatabaseAdding Data to Your DatabaseCreating a RecordEntering DataMoving between fieldsEditing a recordReverting a recordDuplicating a recordFields for Lots of TextDeleting RecordsUnderstanding Browse Mode Error MessagesTyping in vainNavigating Your DatabaseNavigating Record by RecordKeyboard ShortcutsFinding RecordsPerforming a FindFastMatchQuickFindUnderstanding Find Mode Error MessagesNo records matchNo valid criteriaAccidental data entry in Find modeSorting RecordsUnderstanding SortingBeyond the Simple SortMultiple Sort FieldsSame Database, Different ViewsViewing a ListViewing a TableCreating a New WindowHiding WindowsClosing hidden windowsAutomatically Arranging WindowsSaving Your DatabaseSaving a Copy of Your DatabaseSaving a Clone of Your DatabaseUsing FileMaker’s Help Menu
2. Organizing and Editing Records
ViewsForm ViewList ViewTable ViewModifying Table viewAdvanced Find TechniquesModify Last FindMultiple RequestsSearching with AND conditionsSearching with OR conditionsFinding by OmittingRefining a found set with Omit commandsConstraining and Extending the Found SetConstraining the found setExtending the found setRefining Searches with Find OperatorsZero or More Characters, a.k.a. “Wildcard” (*)Less Than (<), Less Than or Equal (≤), Greater Than (>), and Greater Than or Equal (≥)Match Whole Word (=), Match Phrase (” “), and Match Entire Field (= =)Range (…)Find Duplicate Values (!)Today’s Date (//) and Invalid Date or Time (?)Any One Character (@), Any One Digit (#), and Zero or More Characters (*)Relaxed Search (~)Using the Find Pop-up MenuSaving findsEditing saved findsEditing What’s in Your FieldsDrag-and-drop EditingUsing the Replace CommandFind and ReplaceChanging Text AppearanceText FormattingParagraph FormattingThe Text RulerFormatting TabsSetting tabs in the Text RulerSetting tabs in the Tabs dialog boxChecking SpellingSpell Checking with Menu CommandsSpell Checking As You TypeManaging Spelling DictionariesUser dictionariesPreview Mode and Printing
two. Building Your First Database
3. Creating a Custom Database
Creating a New DatabaseCreating and Managing Fields in Table ViewCreating FieldsManaging Field TypesCreating Records in Table ViewInserting a File into a Container FieldExporting Field ContentsInserting a File Using QuickTimeUnderstanding LayoutsThe Layout BarThe Layout Status Toolbar and Layout ModeLayout ObjectsCustomizing a LayoutEditing Text Objects and FieldsMoving and resizing fieldsUsing object gridsViewing sample dataEditing text objectsDeleting objectsAdding text objectsUsing the InspectorInserting a Picture on a LayoutAdding Lines and ShapesFormatting FieldsFormatting a Number fieldAdding a field control style and a value listEditing a value listAdding a drop-down calendar to a fieldControlling field bordersCustomizing a Layout’s Body PartAdding color to a layout partAdjusting the Body part’s heightCreating a New LayoutThe Format PainterMaking Two Layouts MatchT-SquaresArrange and Align ToolsAligning and distributingAligning and distributingResizingGrouping/LockingArrangingRotating
4. Adding Power to Your Database
Creating a Simple CalculationCreating Fields with Manage→DatabaseAdding New Fields to a LayoutCreating a Related TableUnderstanding the Elements of a RelationshipCreating a Key Field with an Auto-Enter Serial NumberUsing Replace Field Contents to add serial numbersCreating a New TableViewing the new table’s layoutCreating a Relationship Between Two Table OccurrencesCreating and Using PortalsAdding a Portal to a LayoutResizing and Moving a PortalContextCreating Records Through a PortalEditing Records Through a PortalDeleting records through a portalPerforming Finds with Related DataUsing Tab ControlsCreating a Tab ControlEditing Tab ControlsAdding, removing, and reordering tabsDefault Front TabTab justificationAppearanceTab widthFormatting a Tab ControlDeleting a Tab ControlAdding Merge FieldsUsing Symbols to Show Important InfoWriting a Basic ScriptCreating a Sort ScriptCreating a ButtonApplying a Script TriggerCreating a Dynamic Report with the AssistantCreating a Trailing Group ReportChanging the Default AccountSumming Up

three. Thinking Like a Developer
5. Creating and Managing a Relational Database
Understanding Relational DatabasesModeling Your DatabaseChoosing EntitiesFinding RelationshipsOne-to-many relationshipsOne-to-one relationshipsMany-to-many relationshipsThe Entity-Relationship DiagramCreating an ER diagramKeysPrimary and foreign keysChoosing a good primary key fieldJoin TablesCreating a Relational DatabaseCreating RelationshipsSorting a RelationshipManaging the Relationships GraphManaging table occurrences using the Tables/Relationships toolsArrange the graphSelection toolsColor your table occurrencesAdding notesPrinting the graphUsing Relational Database PowerOne Table Occurrence, One Found SetViewing Related Fields on a LayoutCreating a Value List Based on a Related FieldLookupsCreating LookupsUsing a RelookupNavigating Between Related RecordsGo to Related RecordUsing GTRR to create a found setReviewing Relationship ConceptsBidirectionalityImplicit Relationships in Action
6. Field Options
Understanding Field TypesTextNumberDateTimeTimestampContainerCalculationSummaryAuto-Enter Field OptionsCreation ValuesUsing Creation Name and Account NameModification ValuesSerial NumberValue from Last Visited RecordDataCalculated ValueLooked-up ValueValidation OptionsMaking Validation StricterValidation RequirementsStrict data typeNot emptyUniqueExisting valueMember of value listIn rangeMaximum number of charactersStorage OptionsGlobal StorageRepeating FieldsIndexingAutomatic indexingControlling indexing manuallyIndexing languageSeeing the indexPrinting Field DefinitionsBeyond Text: Container FieldsPicturesCopy and pasteInsert→PictureQuickTimeSoundRecording soundFileExporting data from container fields
7. Layout Tools
Layout PartsParts in Form ViewParts in List ViewParts in Preview ModePart Setup Dialog BoxWhen to Use Each TypeArranging PartsLayout TypesStandard FormTable ViewList ViewReportBlank LayoutLabels or Vertical LabelsEnvelopeRenaming a LayoutSetting Layout ViewFound Sets and LayoutsLayout SetupManage LayoutsThe Inspector’s Data TabField ControlAdvanced Field ControlsEdit BoxDrop-Down ListPop-Up MenuCheckbox SetRadio Button SetDrop-Down CalendarAuto-CompleteAuto-Complete in Edit BoxesAuto-Complete in drop-down listsRepetitionsDisplay Data FromField BehaviorData FormattingNumber FormattingGeneralAs enteredBooleanDecimalCurrencyPercentDate FormattingAs enteredPreset StylesCustomTime FormattingAs enteredOther FormatsTimestamp FormattingGraphic FormattingFormatAlignmentButtonsCreating Buttons with the Button ToolTurning an Existing Layout Object into a ButtonMaking a Button Not a ButtonButton ActionsTab OrderCustomizing the Tab OrderFixing the Tab Order in the People DatabasePreserving the Automatic OrderMerge FieldsMerge VariablesTooltipsAutoresizingAutoresizing in List LayoutsCreating Layouts for PrintingVisualize the Result in Preview ModePrint MarginsColumnsSliding Layout ObjectsWhen to use slidingChoosing the objects to slideSetting sliding optionsMore Sliding & Visibility options
8. Understanding Calculations
Understanding CalculationsCreating a CalculationThe Structure of a CalculationUsing Fields in CalculationsUsing Constants in CalculationsNumber constantsText constantsUsing Operators in CalculationsMathematical operatorsThe concatenation operatorComparison operatorsLogical operatorsThe ^ operatorParenthesesFunctionsText functionsText formatting functionsNumber functionsDate functionsTime functionsTimestamp functionsAggregate functionsSummary functionsRepeating functionsFinancial functionsTrigonometric functionsLogical functionsGet functionsDesign functionsCustom functionsExternal functionsExpressionsUsing a Related Field in a CalculationUnderstanding the Specify Calculation Dialog BoxTable occurrence contextField listOperatorsFunction listResult TypeCalculation BoxRepetitionsChanging the Standard Evaluation BehaviorAuto-Enter CalculationsValidate Data Entry with a CalculationReplacing Data Using a CalculationComments
9. More Calculations and Data Types
Number Crunching CalculationsNumber Function TypesUsing Number FunctionsAbs()Average()Count()Floor() and Ceiling()List()Round()SetPrecision()Going Beyond Basic CalculationsReviewing the Data and New Business RulesPlanning the CalculationsConstructing the CalculationText CalculationsText FunctionsText function typesCharacter functionsWord functionsText editing functionsText value functionsText counting functionsText Formatting FunctionsTextColor() and RGB()TextFont()TextSize()TextStyleAdd() and TextStyleRemove()Date and Time CalculationsMath with Dates and TimesDatesTimesParsing Dates and TimesCalculations that Create Dates and TimesThe secret powers of Date()Containers in CalculationsCalculations with Pictures, Sounds, Movies, and FilesCalculations with References
10. Understanding Scripts
Understanding ScriptsYour First ScriptPreparing the DatabasePlanning Your ScriptCreating Your ScriptImproving Your ScriptShortcuts to the Edit Script WindowThe Importance of the LayoutWhen to SwitchRunning ScriptsThe Scripts MenuButtonsScript TriggersPerforming Scripts When a File Is Opened and ClosedTimer ScriptsOrganizing Your ScriptsCreating Script FoldersCreating Menu SeparatorsBranching and Looping in ScriptsBranching with If, Else If, Else, and End IfTesting Multiple ConditionsLoopingUsing Go to Record/Request/Page to exit a loopScript TriggersCreating a Simple Script TriggerLayout TriggersOnRecordLoadOnRecordCommitOnRecordRevertOnLayoutKeystrokeOnLayoutEnterOnLayoutExitOnModeEnter and OnModeExitOnViewChangeObject TriggersOnObjectEnterOnObjectKeystrokeOnObjectModifyOnObjectValidateOnObjectSaveOnObjectExit
11. Exploring Script Steps
Go to LayoutGo to ObjectNaming objectsNaming objectsUsing Go To Object in a ScriptScripting for FieldsNavigating FieldsGo to FieldGo to Next Field and Go to Previous FieldEditing CommandsSelecting TextEditing Field DataAvoiding Cut, Copy, and PasteSet FieldSet Field by NameInsert Calculated ResultInserting other valuesPutting data in container fieldsA Field Script in ActionBuilding the scriptOther Steps That Work with FieldsReplace Field Contents and Relookup Field ContentsSet Next Serial ValuePerform Find/ReplaceWorking with RecordsCreating, Duplicating, and Deleting RecordsNavigating Among RecordsGo to Record/Request/PageGo to Related RecordOpening, Reverting, and Committing RecordsOpen Record/RequestCommit Record/RequestRevert Record/RequestCopying RecordsCopy Record/RequestCopy All Records/RequestsWorking with PortalsGo to Portal RowFinding RecordsPerforming People’s Find RequestsTriggering a Find scriptStatic Find RequestsUsing a Variable to Create Dynamic Find RequestsConstraining and Extending the Found SetOmitting RecordsModify Last FindSorting RecordsSorting Records with an OnRecordLoad TriggerWorking with WindowsCreating WindowsBringing a Window to the FrontClosing a WindowMoving and Resizing Existing WindowsAdjust WindowArrange All WindowsMove/Resize WindowOther Window-Related Script StepsWorking with FilesOpening and Closing FilesSave a Copy AsOther File-Related Script StepsPrintingOther Script StepsOpen URLDial PhoneSet Web ViewerExecute SQLFlush Cache to DiskExit Application
four. Becoming a Power Developer
12. Applying Developer Utilities
Copying and Pasting Database StructureImporting Tables and FieldsCopying and Pasting TablesCopying and Pasting FieldsCopying Scripts and Script StepsScript DebuggerControlling Script ExecutionBreakpointsExamining ErrorsPause on ErrorThe Call StackWorking with the Debugger WindowThe Data ViewerThe Current TabThe Watch TabDisable Script StepsThe Database Design ReportGenerating the DDRUsing the DDRFinding Broken Elements with the DDRCustom FunctionsDefining a Custom FunctionEditing Custom FunctionsSharing Custom FunctionsCustom MenusEditing a MenuCreating a New MenuUsing Existing CommandsSubmenusRemoving a MenuInstalling Custom Menu SetsDeveloper UtilitiesUsing the Developer UtilitiesRenaming filesCreate runtime solution application(s)Remove admin access from files permanentlyEnable Kiosk mode for non-admin accountsDatabases must have a FileMaker file extensionCreate error log for any processing errorsLoading and saving settingsDelivering a Runtime SolutionFile Maintenance
13. Advanced Relationship Techniques
Advanced RelationshipsSelf-Join RelationshipsNo ambiguity or circularity allowedMultiple Criteria RelationshipsEditing a relationship’s criteriaRelationship OperatorsPortal FilteringUnderstanding Table OccurrencesAdding a new key field to a tableAdding a new key field to a tableAdding a table occurrenceAdding fields for new table occurrencesTable Occurrence GroupsListing the New Elements You’ll NeedUnderstanding Table Occurrence GroupsCreating a New Table Occurrence GroupBuilding the Assign Expenses LayoutDuplicating and editing an existing layoutCopying a portal from another layoutUsing GTRR to switch TOGsUnderstanding Graph ArrangementsConnecting Database FilesAdding a Table Occurrence from Another FileDefining Data Sources Using a PathUsing Multiple Relationship TechniquesCreating the Invoice Finder Layout
14. Reporting and Analysis
Summary Fields and Sub-summary PartsSummary FieldsOther Summary Field TypesTotal ofAverage ofCount ofMinimum and MaximumStandard Deviation ofFraction of Total ofSummarizing repetitionsAdvanced Sub-summary PartsMultiple Sub-summary parts on one layoutLeading and Trailing Grand SummariesSub-summary Parts and PrintingWeb Viewer ObjectsPutting a Web Viewer on a LayoutWeb Viewer OptionsAllow interaction with web viewer contentDisplay content in Find modeDisplay progress barDisplay status messagesConditional FormattingConditional Formatting of FieldsConditional Formatting of Text ObjectsAdvanced Conditional FormattingRemoving Conditional FormattingBasic Charting (Found Set Charts)BarBarHorizontal BarLineAreaPieChart FormattingCharting and ReportsPicking the Right FieldsChoosing an Appropriate Layout PartUsing the Correct Sort OrderAdvanced ChartingDelimited DataRelated Records
15. Advanced Calculations
Understanding Boolean FunctionsUsing the Self FunctionUsing Storage OptionsUnderstanding Stored and Unstored Calculation FieldsField dependenciesDeciding when to storeIndexing calculationsGlobal Calculation FieldsLogical FunctionsConditional FunctionsThe If() functionThe Case() functionThe Choose() functionConstructing a Conditional CalculationTotal Due calculation #1: Using the If() functionTotal Due calculation #2: Using the Case() functionThe Let() Function and VariablesDefining Calculation VariablesTotal Due calculation #3: Using the Let functionThe Life of a VariableLocal variablesGlobal variablesNesting Let FunctionsRecursionStep 1: Solve the First CaseStep 2: Assume Your Function Already Works, and Use ItStep 3: Find a Stopping PointPlug-InsInstalling Plug-insOld and New Plug-insFinding Plug-insCreating Your Own Plug-ins
16. Advanced Scripting
Commenting ScriptsImporting ScriptsCommunicating with Database UsersShowing a Simple MessageAsking a Simple QuestionAsking a More Complex QuestionOrganizing Complex ScriptsThe Perform Script Script StepHalt Script and Exit ScriptExit Script (Result)Organizing scripts that use Halt or Exit ScriptScript ParametersScript VariablesHandling ErrorsThe Set Error Capture Script StepThe Get ( LastError ) FunctionThe Allow User Abort Script StepAdvanced Script TriggersInstall OnTimer ScriptKeystroke TriggersDetecting which keys were pressedCreating a simple keystroke triggerCreating a keystroke filterPutting a Complex Script TogetherPlanning the ScriptConsidering ExceptionsCreating SubscriptsCreating subscript placeholdersCreating skeleton subscriptsFinishing the SubscriptsThe Find Unbilled Activity subscriptCopying and editing existing script stepsAdding a script parameter to a Perform Script stepChecking a subscript’s resultFinishing the Create Invoice Record subscriptFinishing the Process Timeslips subscriptThe Process Expenses subscriptTesting Scripts
five. Integration and Security
17. Sharing Your Database
FileMaker Network SharingSetting Up a Host ComputerOpening a Shared FileThe Open Remote File Dialog BoxChoosing a host computerChoosing a fileSharing over the InternetTurning on Web SharingAdvanced Web Publishing OptionsConfiguring file accessConnecting from a Web BrowserCustom Home PageFileMaker ServerFile CompatibilityInstalling FileMaker ServerSingle Machine installationMultiple Machine installationConfiguring your serverAdministering FileMaker ServerInstalling a databaseScheduling a backupTesting Your ServerServer Hardware
18. Adding Security
How Security WorksWho Gets AccessWhat They Can DoPrivilege SetsUnderstanding Privilege SetsThe built-in privilege setsCustom privilege setsUnderstanding Individual PrivilegesOther privilegesCreating a privilege setData Access and Design privilegesCreating record-level accessEditing a privilege setManaging AccountsAdding a New AccountTesting your new accountExternal AuthenticationExtended PrivilegesCreating an Extended PrivilegeChecking for an Extended PrivilegeAssigning Extended PrivilegesManaging External File AccessScripts and SecurityDetecting Privileges in a ScriptHandling Security ErrorsRunning Scripts with Full Access PrivilegesManaging Security with ScriptsAdd AccountDelete AccountReset Account PasswordChange PasswordEnable AccountRe-Login
19. Sharing Data with Other Systems
Sharing Your Data with OthersSave/Send Records AsSaving as Microsoft ExcelSaving as Portable Document Format (PDF)Snapshot LinkExternal SQL SourcesSetting Up ODBCThe SQL database serverInstalling the ODBC driverConfiguring the data sourceFinishing ODBC data source configurationConnecting FileMaker to a SQL Data SourceAdding SQL Tables to a FileMaker DatabaseCreating relationshipsShadow tablesUsing SQL TablesExporting DataGrouped ExportsExport formatsImporting DataRecurring ImportImporting over Existing DataUpdate existing records in found setUpdate matching records in found setCreating Tables on ImportCreating a New Database from an Import FileConverting Older FileMaker FilesImport Data SourcesFileFolderImporting a folder of pictures or moviesImporting a folder of text filesDigital CameraXML Data SourceXML exportBento Data SourceODBC Data SourcesImporting and Exporting in a ScriptSave Records Script StepsThe Import Records Script StepThe Export Records Script Step
six. Appendixes
A. Getting Help
Getting Help from FileMaker ProFileMaker’s Installed ExtrasStarter SolutionsHands-On TutorialXML ExamplesThe Quick Start ScreenGetting Help from FileMaker, Inc.The Resource CenterUpdaters and DownloadsWeb SeminarsFileMaker Training SeriesTechnical SupportFree supportPaid supportDeveloper ProgramsTechNetFileMaker Business AllianceDevConGetting Help from the CommunityLocal User GroupsMail Lists and NewsgroupsTraining/Consultants
B. FileMaker Error Codes
Index
About the Authors
Copyright

Overview

This hands-on, friendly guide shows you how to harness FileMaker's power to make your information work for you. With a few mouse clicks, the FileMaker Pro 11 database helps you create and print corporate reports, manage a mailing list, or run your entire business. FileMaker Pro 11: The Missing Manual helps you get started, build your database, and produce results, whether you're running a business, pursuing a hobby, or planning your retirement. It's a thorough, accessible guide for new, non-technical users, as well as those with more experience.

Start up: Get your first database up and running in minutes
Catalog your data: Organize contacts, to-do items, and product information with speedy data-entry and sorting tools
Create professional documents: Publish reports, invoices, and more, with ease
Add visual power and clarity: Create colorful charts to illustrate and summarize your data
Harness processing power: Crunch numbers and search text with dozens of built-in formulas
Learn advanced topics: Explore the high-level features of FileMaker Pro Advanced and FileMaker Server

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781449390280Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills