book

FileMaker Pro 12: The Missing Manual

by Susan Prosser, Stuart Gripman

July 2012

Beginner to intermediate

958 pages

30h 20m

English

O'Reilly Media, Inc.

Read now

Unlock full access

FileMaker Pro 12: The Missing Manual
A Note Regarding Supplemental Files
The Missing Credits
About the AuthorsAbout the Creative TeamAcknowledgmentsThe Missing Manual Series
Introduction
Why FileMaker Pro?About This BookMacintosh and WindowsAbout the OutlineWhat’s New in FileMaker Pro 12The Very BasicsAbout→These→ArrowsAbout Online ResourcesThe Missing CDFeedbackErrataRegistrationSafari® Books Online
1. 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 RecordDeleting RecordsFields for Lots of TextCustomizing the ToolbarUnderstanding Browse Mode Error MessagesTyping in VainNavigating Your DatabaseNavigating Record by RecordKeyboard ShortcutsFinding RecordsPerforming a FindFast MatchQuick FindUnderstanding 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 ViewRearranging Columns in Table ViewModifying Table ViewModifying Table View in Layout ModeAdvanced Find TechniquesMultiple 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 (~)Modify Last FindUsing 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
2. 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 ViewEmbedding a File in a Container FieldInserting a File with a ReferenceExporting Field ContentsUnderstanding 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 ShapesAdding a GradientFormatting FieldsFormatting a Number FieldAdding a Field Control Style and a Value ListEditing a Value ListAdding a Drop-Down Calendar to a FieldControlling Object BordersCreating a New LayoutMaking Two Layouts MatchThe Format PainterCopying Object and Part StylesArrange and Align ToolsAligning and DistributingResizingGrouping/LockingArrangingRotating
4. Adding Power to Your Database
Creating a Simple CalculationCreating Fields with Manage→DatabaseAdding New Fields to a LayoutDeleting a Field from a LayoutDiscard ChangesCreating 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 PortalAdding Rows to 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 JustificationTab 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 ReportCreating Charts in Table ViewChanging the Default AccountSumming Up

3. 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 IndexContainer Field OptionsPrinting Field DefinitionsBeyond Text: Container FieldsPicturesCopy and PasteInsert→PictureQuickTimeSoundRecording SoundFileExporting Data From Container Fields
7. Layout Mechanics
Layout TypesStandard FormTable ViewList ViewReportBlank LayoutLabels or Vertical LabelsEnvelopeLayout PartsParts in Form ViewParts in List ViewParts in Preview ModePart Setup Dialog BoxWhen to Use Each TypeArranging PartsRenaming a LayoutSetting Layout ViewFound Sets and LayoutsLayout SetupManage LayoutsFormatting FieldsField ControlsAdvanced Field ControlsEdit BoxDrop-Down ListPop-Up MenuCheckbox SetRadio Button SetDrop-Down CalendarAuto-CompleteAuto-Complete in Edit BoxesAuto-Complete in drop-down listsRepetitionsDisplay Data FromField BehaviorTab OrderCustomizing the Tab OrderFixing the Tab Order in the People DatabasePreserving the Automatic OrderCreating Layouts for PrintingVisualize the Result in Preview ModePrint MarginsColumns
8. Layout Design
Layout ThemesTouch ThemesGrids, Guides, and Dynamic GuidesGridsGuidesDynamic GuidesScreen StencilsData FormattingNumber FormattingGeneralAs EnteredBooleanDecimalCurrencyPercentDate FormattingAs EnteredPreset StylesCustomTime FormattingAs EnteredOther FormatsTimestamp FormattingGraphic FormattingFormatAlignmentOptimize ForMerge FieldsMerge VariablesTooltipsAutosizingAutosizing in List LayoutsSliding Layout ObjectsWhen to use SlidingChoosing the Objects to SlideSetting Sliding OptionsMore Sliding & Visibility OptionsButtonsCreating Buttons with the Button ToolTurning an Existing Layout Object into a ButtonMaking a Button Not a ButtonButton Actions
9. 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 OperatorsParenthesesFunctionsText FunctionsText Formatting FunctionsNumber FunctionsDate FunctionsTime FunctionsTimestamp FunctionsContainer 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 BoxChanging the Standard Evaluation BehaviorAuto-Enter CalculationsValidate Data Entry with a CalculationCommenting in Calculations
10. 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
11. 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 TriggersTimer 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 TriggerFile Option TriggersOnFirstWindowOpenOnLastWindowCloseOnWindowOpenOnWindowCloseLayout TriggersOnRecordLoadOnRecordCommitOnRecordRevertOnLayoutKeystrokeOnLayoutEnterOnLayoutExitOnModeEnterOnModeExitOnViewChangeObject TriggersOnObjectEnterOnObjectKeystrokeOnObjectModifyOnObjectValidateOnObjectSaveOnObjectExitOnTabSwitch
12. Exploring Script Steps
Go to LayoutGo to ObjectNaming 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 FieldsUsing the Insert File Script StepCreating a Field ScriptBuilding 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 Records/RequestsRevert Record/RequestCopying RecordsCopy Record/RequestCopy All Records/RequestsWorking with PortalsGo to Portal RowFinding RecordsPausing for Users’ 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 WindowsSpecifying Advanced Style OptionsBringing 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
4. Becoming a Power Developer
13. 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 MenuHiding or Showing a Menu by CalculationInstalling 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
14. Advanced Relationship Techniques
Advanced RelationshipsSelf-Join RelationshipsAvoiding AmbiguityMultiple Criteria RelationshipsEditing a RelationshipRelationship OperatorsPortal FilteringUnderstanding Table OccurrencesDeciding Which Table Needs a New OccurrenceAdding a Table OccurrenceAdding Fields for New Table OccurrencesTable Occurrence GroupsPlanning 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
15. Reporting and Analysis
Summary Fields and Subsummary PartsSummary FieldsOther Summary Field TypesTotal ofAverage ofCount ofMinimum and MaximumStandard Deviation ofFraction of Total ofSummarizing RepetitionsAdvanced Subsummary PartsMultiple Subsummary Parts on One LayoutLeading and Trailing Grand SummariesSubsummary 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 MessagesAutomatically Encode URLConditional FormattingConditional Formatting of FieldsConditional Formatting of Text ObjectsAdvanced Conditional FormattingRemoving Conditional FormattingBasic Charting (Found Set Charts)ColumnStacked ColumnPositive/Negative ColumnBarStacked BarPieLineAreaScatterBubbleChart FormattingCharting and ReportsPicking the Right FieldsChoosing an Appropriate Layout PartUsing the Correct Sort OrderAdvanced ChartingDelimited DataRelated Records
16. 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-insFinding Plug-insCreating Your Own Plug-ins
17. Advanced Scripting
Commenting ScriptsImporting ScriptsCommunicating with Database UsersShowing Custom Dialog BoxShowing a Simple MessageAsking a Simple QuestionProviding Input FieldsUnderstanding the Commit Data OptionBuilding a Custom LayoutShowing Feedback with a Merge VariableCreating Modular ScriptsThe Perform Script Script StepHalt Script and Exit ScriptExit Script (Result)Organizing Scripts that Use Halt Script 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 SubscriptCreating a Looping ScriptThe Process Expenses SubscriptTesting Scripts
5. Security and Integration
18. Sharing Your Database
FileMaker Network SharingSetting Up a Host ComputerOpening a Shared FileUnderstanding the 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 ServerInstalling FileMaker ServerInstallation TypesSingle Machine InstallationConfiguring Your ServerUsing the Technology Tests PageAdministering FileMaker ServerInstalling a DatabaseUnderstanding BackupsScheduling a BackupEnabling Progressive BackupsServer HardwareFileMaker GoOpening a DatabaseLogging in after HibernationCompatibilityDesigning for GoTelling Filemaker Go Which Layout to Use
19. 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 AccessField-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
20. 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 Source on WindowsConfiguring Data Sources on Mac OS XFinishing 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 FileImport Data SourcesFileFolderImporting a Folder of Pictures or MoviesImporting a Folder of Text FilesXML Data SourceXML ExportBento Data SourceODBC Data SourcesImporting and Exporting in a ScriptSave Records Script StepsThe Import Records Script StepThe Export Records Script Step
6. 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 CenterUpdates and DownloadsWeb SeminarsFileMaker TrainingFileMaker Training VideosFileMaker Training SeriesTechnical SupportFree SupportPaid SupportDeveloper ProgramsThe FileMaker Technical NetworkFileMaker Business Alliance (FBA)DevConGetting Help from the CommunityLocal User GroupsMail Lists and NewsgroupsTraining/Consultants
B. File Conversion
CompatibilityUnderstanding ConversionStart with a Test RunConverting a Single FileTroubleshooting Conversion ProblemsConverting Multiple Files
C. Using the Insert Commands with Container Fields
D. FileMaker Error Codes
Index
About the Authors
Copyright

Overview

You don’t have to be a techie to use FileMaker Pro 12, but you do need to know what you’re doing. With this crystal-clear guide, you’ll learn how to create a powerful database that lets you do almost anything with your data. Whether you’re running a business, printing a catalog, or planning a wedding, you’ll learn how to customize your database and put it to work.

The important stuff you need to know:

Get started. Tour FileMaker Pro’s features and create your first database in minutes.
Catalog your data. View, sort, and shape information in dozens of ways.
Create professional documents. Publish reports, invoices, and other documents with ease.
Harness processing power. Use calculations and scripts to crunch numbers, search text, and automate tasks.
Add visual power and clarity. Create colorful charts to illustrate and summarize your data.
Think like a developer. Dive into the high-level features of FileMaker Pro Advanced.

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: 9781449332365Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills