Philosophy of FileMaker 20 - Volume 1

Create a concise requirements outline that identifies tables and the ERD, specifies platforms, hosting, user load, security, and reports, and outlines client responsibilities for an efficient development project.

Master entity relationship diagrams (ERD) as a planning tool, learn one-to-one, one-to-many, and many-to-many relationships, and when to use join tables in invoice, product, and line-item examples.

Explore naming conventions for scripts, fields, tables, table occurrences, value lists, layouts, and files in FileMaker, and learn why consistency and compatibility with third-party software and SQL matter.

Create a blank file in FileMaker, name it invoices.fmp12, and explore managed database settings, advanced tools, and auto-saving layout changes to prepare for defining tables and fields.

Create invoices and add customers, products, and lines in FileMaker; apply a clear naming scheme for primary and foreign keys, with serial keys and housekeeping fields.

Create and configure data entry fields for invoices in FileMaker, including date fields, creation date, customer and product fields, and practical layout and field-type tips for indexing and type-ahead.

Learn to build core file relationships using the anchor boy method, create table occurrences, and add foreign keys to connect invoices, customers, lines, and products, including one-to-many and portal behavior.

Determine the minimum layout size by querying users’ actual resolutions, and design an invoice interface with a title, navigation, current record, found set, and housekeeping fields.

Insert record number, found count, and total record count in the invoices form, then adjust layout, merge fields, and footer to display created by and modified by information.

Review the homework by duplicating the invoices form for customers and products, renaming layouts, rearranging them, and learning how anchors, table occurrences, and themes influence the design.

Build a cross-layout button bar with new, duplicate, and delete record actions, using context-sensitive scripts, themed icons, and auto-sizing to keep buttons aligned across layouts; duplicate for other tables.

Create a list view for invoices, using layout setup and merge fields, then switch to form view via buttons and tooltips for easy navigation.

Finish shaping the basic FileMaker 20 interface with form and list views. Duplicate layouts for customers and products, set currency and date fields, and align spacing for a professional look.

Compare two options for phones, emails, and addresses: fields versus tables; model a phones table linked to customers with phone, extension, type, primary, plus delete and portal features.

Create and relate email and address portals and use a built-in send mail button that works with Mac Mail and Outlook to streamline contacting records.

Enhance portals by using placeholder text to guide input and by hiding empty objects, while implementing alternating row shading and portal sorting to improve clarity.

Build a dynamic delete workflow in FileMaker that uses a portal row delete script with show custom dialog, get script parameter, and get last error for 301 error handling.

Automatically format phone numbers in FileMaker to enforce uniform entry, using let, left, middle, and right functions with auto enter and validation for 7 or 10 digits.

Create and use custom functions in FileMaker to centralize phone formatting and validation, passing the phone field as a parameter and referencing self for scalable, single-source logic.

Learn how to import data from an Excel spreadsheet into FileMaker, map fields, create temp fields, and use scripts to parse and distribute records across related tables.

Move data across tables with a FileMaker script using local variables, set field, and a loop to map IDs and foreign keys across layouts.

select a customer for an invoice by building a dynamic value list from the customers table and linking invoices to customers via a primary key; filter active records.

Learn how to build a lines portal in FileMaker to manage a many-to-many relationship between invoices and products, including join tables, auto entering fields, snapshotting price, and calculating extended price.

Add a status field to invoices, filtered portals, compute a total price from line items with a sum, link invoices to customers via an anchor-based relationship, and sort by date.

Create a filtered invoice portal using a global status field as an interface control, toggle paid, billed, or canceled with a checkbox, and refresh the portal with a script trigger.

Master auto sizing and anchoring in layouts, optimize portals and list views for any screen, and leverage case statements over nested if statements for efficient conditional data display.

Learn eleven methods to trigger a script in FileMaker, from buttons to script triggers and Incomer JavaScript, with guidance on prioritizing useful steps.

Explore how go to related record (GTRR) navigates across related tables to show invoices, products, and customer details in new windows, with tooltips and open URL features.

Create a script to generate an invoice from a customer by transferring the customer primary key to the invoices context using set variable, then commit records.

Learn to design a new portal row workflow using a plus sign, a dynamic script, and portal navigation to the last row, with guidance on pop up versus dropdown lists.

Build a FileMaker script to identify upcoming birthdays by month and print cards, using the date function and let expressions, with notes on international date formats.

Develop a simple FileMaker script to identify overdue invoices by filtering date posted with get current date minus 30 and billed status, then test with the script debugger.

Explores duplicating an invoice in FileMaker, showing that only the current record duplicates, while related line items require a scripted loop to copy items and reassign the new invoice id.

Implement dynamic, script-driven sorting in FileMaker that sorts by a selected field, supports ascending/descending order, and adapts across layouts while encouraging found sets over free sorting.

Explore the new window script step by creating a merge letter in FileMaker, using merge fields, page setup, and preview to print while preserving the original found set.

Open the FileMaker open script to reset global fields (which don't store values) and initialize globals, then apply window settings for consistent multi-user interfaces.

Build a Rolodex style alphabetic find in FileMaker by using a single script parameter per letter to filter customers by last name initial.

Compare and contrast FileMaker scripting approaches. Explore copy and paste drawbacks, and use set field, global fields, variables, script parameters, or go to related records for context.

Organize customer notes in a FileMaker database by adding a notes field, a notes button, and calculations that insert a timestamp and author, then position the cursor for quick entry.

Create a centralized tax rate field with auto enter, compute line item tax via a stored calculation linked to invoices, and manage tax exempt customers to adjust tax totals.

Explore correcting portal totals in a FileMaker invoice by using a stored tax rate and case logic to calculate tax, subtotal, shipping, and grand total, then align labels for clarity.

Totaling a filtered portal in FileMaker is tricky. Use a summary field like total grand with a one-row portal filtered the same way to get total, or try execute sql.

Discover how execute SQL, a FileMaker calculation function, fetches values or sums from tables without entering heavy relationships, avoiding summary fields in portals and reducing graph clutter.

Create a payments table linked to invoices to record multiple payments, with date, amount, type, and notes, using a portal to track payments per invoice.

Create a balance calculation by subtracting payment total from grand total, use Total Gram as the source, and apply rounding with conditional formatting to display red when greater than zero.

Learn how to search overdue invoices by outstanding balances and use a script-triggered balance update, deciding where to place calculations between the managed database and scripts for reliability.

Delete a payment to trigger a balance and status update, and use conditional scripts with script parameters to handle payments and line items securely, respecting security rules.

Automate full payment by auto entering the remaining balance on new invoices, align labels for a neat layout, and enhance usability with tooltips and inline edits.

Automate removing all text styling and returns in FileMaker by applying text style remove in manage database and using paste text only, ensuring clean one-line fields across records.

Develop a delivery date calculation in FileMaker that adds five workdays to the posted date, excluding weekends, using the date work finish field and a week-based logic.

Add a tab control to host the map and resize layout elements. Use the web viewer to display Google Maps from addresses and test in browse mode.

Learn to generate directions from addresses using FileMaker go, open URL, and a directions button, embedding starting points from the office data and city, state, and ZIP fields.

Learn how timestamp and modification fields update across invoices and their lines, using max to identify the most recent change and test fixes.

Name windows to reveal context as you navigate records. Build a window title using a calculation and an on record load script that updates with company or person name.

Create a calculated button with the button bar tool, applying a gradient and Verdana 13-point black text, deriving name from the layout's table via get layout table name for duplicates.

Learn how scripting a find reduces user errors by clarifying fine mode, avoiding accidental data entry, and guiding results to list or form views based on matches.

Create a basic find in FileMaker by entering find mode, pausing to enter criteria, and then performing the find with a script, illustrating basic find structure.

Learn to add continue and cancel buttons in FileMaker, use a resume script, and choose pause or halt to control scripts, with layout-based binding for reuse.

Hide and reveal FileMaker objects by window mode, using get window mode to separate browse and find modes. Adjust layouts and buttons for fine mode to improve find workflows.

Learn to simplify FileMaker searches by indexing fields and a phone find field. Use text and number indexing rules to support partial and exact matches, and design a user-friendly interface.

Implement canceling a script with allow user abort so developers can abort while users cannot; use modular scripting with subscripts to centralize changes and enable error capture.

Explore how a find with no matching data triggers a user-friendly message by using get last error checks, showing all records, and the script debugger to diagnose issues.

Learn to handle no find criteria and errors in FileMaker scripts with pause on error and last error checks.

Explore implementing modify last find in FileMaker with a loop that iterates between fine and browse mode, exiting via get last message choice and routing results by found count.

Explore adaptive scripting in FileMaker 20 by building a single dynamic script that works across layouts using Get Layout table name, reducing clutter and ensuring correct messages.

Explore how table occurrences in a single window can hold different found sets, and learn to restore a previous found set after a manual find versus a script in FileMaker.

Use go to related record across table occurrences to hop from customers to invoices, and save and restore found sets with dedicated scripts for seamless navigation.

Combine a dynamic save-and-restore script for found sets in FileMaker, using get layout and layout by calculation to route to invoices, products, and original layouts.

Learn to enable search in list view by converting merge fields to searchable calculation fields, align and size them, and add placeholder text to support find mode across layouts.

Learn to implement instant feedback and single-field find and browse filtering in FileMaker using a global X filter field, on object modify, and a dynamic quick find workflow.