FileMaker Training Series Modules

About the FileMaker Training Series at San José State University

eric

Below are descriptions of the individual modules offered in the FileMaker Training Series:

1. Using FileMaker Pro - Approach FileMaker Pro 11 from the perspective of users: learn techniques for working with FileMaker data, performing Quick Finds, saving Find requests, printing and exporting, setting up recurring imports, and accessing features for working with email, Adobe® PDF, and Microsoft® Excel documents.
  1. Module Overview
  2. Module 1 introduces you to the fundamentals of the FileMaker® Pro database application. Understanding the basics of working with data in FileMaker Pro is the first step to full-scale development. In this module, you will be guided through the FileMaker Pro application environment. You will be introduced to all of the menu commands and you will learn how to manipulate data, manage records, find and sort records, print, save data in other file formats, and import and export data.
    1. Objectives
    2. This module serves as an introduction to making use of the functionality available in FileMaker Pro. Upon
    3. completion of this lesson you will be able to:
    4. • Open, create, and close a file
    5. • Understand and operate the menu commands
    6. • Create, edit, and delete records
    7. • Perform basic and advanced finds, save find requests, and sort data
    8. • Print and preview data
    9. • Save data in Excel and PDF formats
    10. • Understand how to use FileMaker Pro in conjunction with other applications to import and export data
    11. • Set up recurring imports
    12. Contents at a Glance
    13. 1.1 Moving Around & Viewing Data
    14. 1.2 Working with Data
    15. 1.3 Finding Data
    16. 1.4 Printing & Previewing Data
    17. 1.5 Importing & Exporting data
    18. About the FileMaker Training Series at San José State University
2. Working with Fields - Learn practical skills required to define FileMaker Pro 11 data tables and fields. This module explores data types and options for auto-entry, validation, and field storage. Discover how to manage images and other objects in container fields and work with the Database Design Report in FileMaker Pro Advanced.

- 1. Module Overview
    1. The purpose of this module is for you to become comfortable creating fields and working with various field options. FileMaker® Pro Software supports several different data types, including text, number, date, time, and timestamp data. You can specify many options for automatically entering data into a field when it is created and/or modified. Setting up field validation and storage options provides an additional layer of control over the data that can be entered into your system. In the first four lessons of this module, you will learn how to define fields and set these types of options.
    2. Lesson 2.5 focuses on a special type of field in FileMaker Pro called a container field, which can contain any binary data, such as an image, movie, or file. Finally, in Lesson 2.6, you will learn how to create a Database Design Report, which is a valuable resource for investigating how and where fields and other objects are used in a database.
    3. Objectives
    4. When you have finished this module, you will:
    5. • Be familiar with the different field types available in FileMaker Pro
    6. • Be able to define fields using both the Manage Database dialog and Table View
    7. • Know how to specify auto-entry and validation options for a field
    8. • Understand the importance of indexing
    9. • Be able to insert and export files from container fields
    10. • Know how and why to run a Database Design Report
    11. Contents at a Glance
    12. 2.1 Creating and Using Fields
    13. 2.2 Auto-Entry Options
    14. 2.3 Field Validation
    15. 2.4 Field Storage and Indexing
    16. 2.5 Container Fields
    17. 2.6 Documenting Database Schemas
    18. About the FileMaker Training Series at San José State University

1. Data Modeling - Learn how to construct different types of data relationships, ensure data integrity, and implement multiple-table solutions. This module also covers lessons in building layouts using related data fields and portals.

- - 1. Module Overview
    2. FileMaker® Pro can be viewed as both a rapid application development (RAD) tool and a relational database management (RDBM) tool. In order to create effective FileMaker Pro solutions, you will need to master skills related to both aspects of FileMaker Pro. Learning how to write effective calculations and scripts (covered in other modules) is an important part of mastering the RAD capabilities of FileMaker Pro; learning which data tables to create and how to link them together via relationships is a key to mastering its relational database capabilities. The latter skill is often referred to as "data modeling."
    3. Your FileMaker Pro solutions are intended to address real-world problems. Therefore, you will need to
    4. translate aspects of the real world into the form and language of a relational database. Suppose you want to build a solution that tracks arboretum members, their personal interests and hobbies, their contact information, and their history of donations. How many FileMaker Pro tables should you create toward this end? Which fields go in which tables? How should they be linked together? The skills you will learn in this module will help you answer these questions with informed decisions.
    5. This module begins by introducing some of the basic concepts that underlie data modeling. It will delve into such concepts as entities, attributes, and relationships. The second half of the module will show you how to apply these concepts to get effective and useful work done in FileMaker Pro.
    6. This module is unusual in that some of its lessons involve pencil and paper, or marker and whiteboard, rather than FileMaker Pro.
    7. Objectives
    8. When you have finished this module, you will be able to:
    9. • Break a real-world scenario into a set of entities and their attributes
    10. • Identify cases where one-to-many or many-to-many relationships are appropriate
    11. • Create an entity-relationship diagram (ERD)
    12. • Create relationships between FileMaker Pro tables
    13. • Understand relationship options
    14. • View and manage related FileMaker Pro data via a portal
    15. • Create complex relationships for filtering views of related data
    16. Contents at a Glance
    17. 3.1 Identifying Entities and Attributes
    18. 3.2 Identifying Relationships
    19. 3.3 Creating One-to-Many Relationships with Keys
    20. 3.4 Working with Many-to-Many Relationships
    21. 3.5 Creating an Entity-Relationship Diagram (ERD)
    22. 3.6 Creating Relationships in FileMaker Pro
    23. 3.7 Working with Complex Relationships

1. About the FileMaker Training Series at San José State University
2. Working with Layouts - Discover tools and techniques for creating layouts in FileMaker Pro 11, including the new Inspector tool palette, conditional formatting, auto-resize, Web Viewers, portals, and tab controls. Learn how to organize layouts into groups and how to set Script Trigger options. Understand how to create and use static, dynamic, and relational value lists.

- - 1. Module Overview
    2. FileMaker® Pro developers spend a significant amount of time working with layouts, because layouts are the primary way of presenting and organizing data in FileMaker Pro. Layouts serve both as user interfaces and as areas for design and development. In this module, you will learn how to work efficiently and effectively with layouts, an essential skill for any developer.
    3. Layouts come in various shapes and forms and can be rather simple or extremely detailed, all depending on the needs of the particular system and the aesthetic sense and design skills of their creator. The lessons in this module explore various types of layout objects and the tools and methods for controlling them. In FileMaker Pro 11, many of these have been reorganized into a centralized object management tool called the Inspector. You will learn how to make user interfaces more intuitive and better organized using tooltips and tab control objects and by setting logical tab orders. You will also learn about auto-resizing of layout objects and conditional formatting, both of which can significantly enhance users’ experience of your database.
    4. Objectives
    5. When you have finished this module, you will:
    6. • Know how to create and organize layouts using the Layout Setup and Manage Layout dialogs
    7. • Be adept at adding, selecting, modifying, and formatting layout objects
    8. • Know how to change the control style for a field and create static, dynamic, and relational value lists
    9. • Be comfortable working with tab controls, portals, and web viewers
    10. • Be able to set auto-resize options for layout object
    11. • Understand how to use conditional formatting to alter the appearance of an object under specified conditions
    12. • Know how to set script trigger options for layouts and objects
    13. Contents at a Glance
    14. 4.1 Managing and Organizing Layouts
    15. 4.2 Working with Layout Objects
    16. 4.3 Field Control Style and Value Lists
    17. 4.4 Special Layout Objects
    18. 4.5 Advanced Object Formatting
    19. FREE: Complete self-paced "Working with Layouts" module

1. About the FileMaker Training Series at San José State University
2. Calculation Functions - Learn how functions work in the calculation engine in FileMaker Pro 11 and how to apply them in various contexts. Study how custom functions are built and how to use them in database solutions.

- - 1. Module Overview
    2. FileMaker® Pro developers use calculation functions on a daily basis for many tasks, including defining calculation fields, setting up field auto-entry and validation options, creating tooltips, filtering portals, displaying conditional formatting, and writing scripts. Because calculation functions are used so pervasively throughout FileMaker Pro, mastering calculations is one of the crucial steps to becoming a proficient developer.
    3. While there are more than 200 calculation functions available within FileMaker Pro, the typical developer works with a subset of 30-50 of them on a regular basis. In this module, you will learn about this core of useful and practical functions. They are presented by type (number, text, date, etc.) to make them easier to learn and remember.
    4. In addition to presenting the syntax and common uses of various functions, the lessons in this module each focus on a different area in FileMaker Pro in which you encounter calculation functions. Lesson 5.3, for instance, focuses on using functions in conjunction with field options for validating and auto-entering data, while Lesson 5.7 showcases using calculations in tooltips. The only significant contexts in which calculations are used that are not covered in this module are scripting and record-level security settings; these are subjects covered in Modules 6 and 8, respectively, so you will have an opportunity to practice using functions in those contexts later.
    5. Objectives
    6. When you have finished this module, you will:
    7. • Be familiar with the most common and useful calculation functions
    8. • Be able to create calculation fields
    9. • Know how to use calculations for field auto-entry and validation
    10. • Have experience using calculation functions in a variety of practical contexts
    11. • Understand how to use custom functions and why they are useful
    12. Contents at a Glance
    13. 5.1 Working With Calculations
    14. 5.2 Number Functions
    15. 5.3 Text Functions
    16. 5.4 Date and Time Functions
    17. 5.5 Logical Functions
    18. 5.6 Text Formatting Functions
    19. 5.7 Aggregate Functions
    20. 5.8 Custom Functions

- 1. About the FileMaker Training Series at San José State University

1. Scripts - Explore the Script Trigger features of FileMaker Pro 11, as well as best practices for writing scripts. Learn how to build navigation and window controls, and study logical branching and looping routines.

- - 1. Module Overview
    2. One of the most important and powerful tools in FileMaker® Pro is the Manage Scripts feature, which allows you to create scripted routines that can then be triggered in a variety of ways. Scripts can serve a wide variety of purposes, including automating everything from simple navigation tasks to complex reporting and workflow needs.
    3. Despite their variety of purposes, there are common design patterns—sequences or arrangements of script steps—that serve as the building blocks for script after script. Examples of these design patterns include interacting with the user, finding data, error trapping, navigating to related records, and looping over a set of records. This module approaches script writing from the standpoint of design patterns. By focusing on the common patterns, you will learn the building blocks necessary for creating scripts in your own database solutions.
    4. Before working through this module, you should be familiar with the concepts covered in the previous modules in this training series. As you will soon discover, scripting relies heavily on calculation formulas. Similarly, for certain sections in this module, you will need to be familiar with topics such as the Relationships Graph, portals, tab controls, and other concepts covered in previous modules.
    5. Objectives
    6. When you have finished this module, you will know how to:
    7. • Create scripts that interact with users, perform finds, and manipulate data
    8. • Attach scripts to buttons
    9. • Set up layout and object level script triggers
    10. • Use the Script Debugger and Data Viewer to troubleshoot scripts
    11. • Add error trapping routines to scripts
    12. • Use script parameters, script results, and script variables
    13. • Create scripted routines for generating related records
    14. • Use loops to perform iterative tasks
    15. Contents at a Glance
    16. 6.1 Working with Scripts
    17. 6.2 Script Triggers
    18. 6.3 Interacting with the User
    19. 6.4 Using Scripts to Find Data
    20. 6.5 Navigation Techniques and Script Context
    21. 6.6 Window Management
    22. 6.7 Looping Techniques

- 1. About the FileMaker Training Series at San José State University

1. Reporting - Practice creating reports in FileMaker Pro 11 using a variety of techniques. Explore sub-summary reports that group information by break fields. Work with the new FileMaker Charts feature of FileMaker Pro 11. Learn how to build a cross-tab report (with data-driven columns) and an HTML report that is displayed via the Web Viewer object.

- 1. Module Overview
    1. One of the key features of FileMaker® Pro is its powerful reporting capabilities. As a developer, you can use these capabilities to create reports that allow users to better understand and analyze their data. An effective report can be anything from a simple list view to a basic subsummary or even a chart or complex HTML report. This module will guide you through the process of building several types of reports in FileMaker Pro and demonstrate various uses of each type.
    2. For most developers, the most frequently used reporting tool is a basic subsummary report, covered in Lesson 7.1. While these reports can be valuable and are quick to develop, there are also circumstances in which it is useful to build reports that are more advanced. Lesson 7.2 explains several such advanced techniques, including how to create reports that contain no body part, ranking reports, and subsummary reports with more than one break field.
    3. Lesson 7.3 explores the new charting feature of FileMaker Pro 11 in depth. Lessons 7.4 and 7.5 address two other methods to create reports in FileMaker Pro. First, you will learn how to create a crosstab report, which is a more intuitive format than a subsummary report for analyzing certain types of data. You will also learn how to create an HTML report using the web viewer capability in FileMaker Pro; this technique is useful when a more flexible report is needed.
    4. Objectives
    5. When you have finished this module, you will:
    6. • Be able to create a basic subsummary report
    7. • Be able to create a bodyless subsummary report, a ranked subsummary report, and a subsummary report that summarizes by multiple variables
    8. • Understand how to create various types of charts, including summary charts and charts that use delimited lists of data
    9. • Be able to create a crosstab report and understand the advantages and disadvantages of subsummary and crosstab reporting
    10. • Understand the uses of the web viewer in the context of reporting
    11. Contents at a Glance
    12. 7.1 Basic Subsummary Reports
    13. 7.2 Advanced Subsummary Reports
    14. 7.3 Working with Charts
    15. 7.4 Crosstab Reports
    16. 7.5 Reporting Using Web Viewers
    17. FREE: Complete self-paced "Reporting" module

1. About the FileMaker Training Series at San José State University
2. Security - Study the account-based security features of FileMaker Pro 11, and learn how to implement privilege sets, tie access to database solutions to external authentication servers, control both access and authoring rights to systems, and extend security controls with scripting.

- - 1. Module Overview
    2. Implementing a proper security model often requires going beyond controlling whether or not someone can
    3. log into a database. FileMaker® Pro includes a robust set of features for controlling which users have access
    4. to what information and what they can do with that information via the accounts and privileges settings.
    5. This module will demonstrate how to isolate legitimate users from sensitive operations or information within
    6. a database, how to associate privileges with authenticated accounts, and how to control specific features
    7. within a database based on a given user’s assigned privileges.
    8. Database administration and server security is covered in Module 10 – FileMaker Server.
    9. Objectives
    10. When you have finished this module, you will:
    11. • Be familiar with the security controls in FileMaker Pro
    12. • Know how to create and manage user accounts
    13. • Grasp how to control access within a group of files
    14. • Understand how to associate privilege sets with accounts
    15. • Know how to manage privilege settings
    16. • Be able to control record deletion
    17. • Know how to control record level access
    18. Contents at a Glance
    19. 8.1 Creating Accounts & Granting Access
    20. 8.2 Setting Up Privilege Sets
    21. 8.3 Restricted Conditional Deletes
    22. 8.4 Conditional Record Access
    23. FREE: Complete self-paced "Security" module

1. About the FileMaker Training Series at San José State University
2. Intermediate & Advanced Techniques - Explore how to implement custom menu sets in FileMaker Pro 11. Learn advanced techniques with portals, including portal sorting and dynamically controlling the contents with a portal with a filter. Use advanced scripting techniques and Script Triggers to implement an audit trail.

- - 1. Module Overview
    2. The techniques presented in the following lessons are somewhat larger in breadth than in prior modules and build upon other materials presented throughout this courseware. The lessons in this module also present specific techniques that FileMaker® Pro developers often face in real-world situations. It is important to note that the authors of this material do not offer these lessons as examples of the only or best ways to create certain functionality in FileMaker Pro databases. These lessons attempt to conform to what would be considered reasonable best practices but, in the end, the lessons in this module are presented for instructional purposes.
    3. Objectives
    4. When you have finished this module, you will:
    5. • Know how to implement custom menus in a FileMaker Pro solution
    6. • Be able to control the contents of a portal dynamically
    7. • Understand two techniques for sorting records within a portal
    8. • Understand a technique for creating a “filtered sub-summary list”
    9. • Be familiar with more advanced scripting techniques and the use of script triggers
    10. Contents at a Glance
    11. 9.1 Custom Menus
    12. 9.2 Filtered Portals
    13. 9.3 Dynamic Portal Sorting
    14. 9.4 Filtered Sub-summary List
    15. 9.5 Audit trail
    16. About the FileMaker Training Series at San José State University

1. FileMaker Server - Learn the best practices for setting up and maintaining FileMaker Server 11 and FileMaker Server 11 Advanced. Understand how and why to use server-side scripts. Review hardware and networking requirements, learn proper backup routines, and be trained in configuration processes.

- - 1. Module Overview
    2. FileMaker® Server software is the linchpin in deploying FileMaker Pro solutions to many users. Using FileMaker Server, a FileMaker solution can be shared among tens or hundreds of FileMaker Pro clients at once, all of them simultaneously accessing and, depending on their allotted privileges, modifying data in the system.
    3. Without FileMaker Server, the alternative is to create standalone solutions that run in isolation on the desktops of your users, who never share data with one another. You may also share files using the sharing capability built into the FileMaker Pro or Pro Advanced client itself, but doing this limits sharing to only 9 additional users.
    4. This module illustrates why it is vital to use FileMaker Server if you plan to deploy a multi-user FileMaker Pro solution for significant production use. FileMaker Server provides a stable, scalable platform for multi-user solutions. The added protection of integrity checks and regular backups, assuming you configure your installation to manage such things, assures data integrity.
    5. To follow the examples and exercises in this module, you will need an installed copy of FileMaker Server Advanced. If you do not already have a copy installed, you can visit http://www.filemaker.com/products/filemaker-server/index.html and follow the links at the right to download a 30-day trial of the software.
    6. Objectives
    7. When you have finished this module, you will be able to:
    8. • Install and configure FileMaker Server and FileMaker Server Advanced
    9. • Upload databases to FileMaker Server
    10. • Back up and maintain your databases using FileMaker Server backup tools
    11. • Integrate FileMaker Server backups with organizational backup strategies
    12. • Administer FileMaker Server from the command line
    13. • Trigger FileMaker Pro and operating system scripts from FileMaker Server
    14. Contents at a Glance
    15. 10.1 Installing FileMaker Server
    16. 10.2 Configuring FileMaker Server
    17. 10.3 Backing Up FileMaker Pro Databases
    18. 10.4 Running Scripts with FileMaker Server
    19. About the FileMaker Training Series at San José State University

1. Connectivity - Learn how to integrate FileMaker 11 with SQL systems, both through ODBC connections and with the External SQL Data Source capabilities. Review configuration processes and learn to use other data systems in tandem with your FileMaker Pro databases.

- - 1. Module Overview
    2. One of the great strengths of FileMaker® Pro software is its ability to share data with other applications. Not only can FileMaker Pro make its own data available, via technologies such as the Web or Open Database Connectivity (ODBC), it can also access data stored in other systems as well.
    3. This module covers some of the ways in which FileMaker Pro can share data using ODBC, a database interchange technology. Using ODBC, you can import data from sources, such as SQL databases, into your FileMaker Pro solution. Likewise, you can make data from your FileMaker Pro solution available to other programs that use ODBC, whether they are spreadsheets or programs written in languages like Java or Visual Basic. Through the External SQL Sources (ESS) feature in FileMaker Pro, you can access certain kinds of SQL-based data directly from within a FileMaker Pro system.
    4. Setup Notes
    5. To follow all of the examples and exercises in this module, you will need to download, install, and configure Microsoft SQL Server 2008 Express Edition with Advanced Services. You will also need to use Microsoft Query in conjunction with Microsoft Excel 2007. Please refer to the Appendix for Module 11 for guided setup instructions.
    6. All databases used in this module may be opened with an account name of Admin and a blank password.
    7. Objectives
    8. When you have finished this module, you will be able to:
    9. • Understand the different ways of getting data into and out of FileMaker Pro using ODBC technologies
    10. • Configure Data Source Names (DSNs) to connect to specific ODBC data sources
    11. • Use ODBC Import feature in FileMaker Pro to populate a FileMaker Pro database with records from an ODBC data source
    12. • Use ODBC Import feature in FileMaker Pro to perform a one-way synchronization of data from an ODBC data source to FileMaker Pro
    13. • Understand and use the External SQL Sources (ESS) features in FileMaker Pro 10
    14. • Use the ESS feature to integrate SQL data sources directly with your FileMaker Pro solutions
    15. • Use a FileMaker Pro solution as an ODBC data source•
    16. Contents at a Glance
    17. 11.1 FileMaker Pro Connectivity Options
    18. 11.2 Configuring a DSN For Use by FileMaker Pro
    19. 11.3 Importing Records from an ODBC Data Source
    20. 11.4 Working with External SQL Sources

- 1. 11.5 Using FileMaker Pro as an ODBC Data Source
    1. About the FileMaker Training Series at San José State University

1. Web Publishing - Understand the capabilities of both Instant Web Publishing (IWP) and Custom Web Publishing in FileMaker Pro 11 and learn how to develop solutions to publish via the web. Review the PHP Site Assistant and gain hands-on experience with IWP.

- - 1. Module Overview
    2. With FileMaker® Server, it is possible to make a FileMaker Pro solution available to up to 250 FileMaker Pro
    3. users on either a local area network (LAN) or a wide area network (WAN). Using FileMaker Server Advanced,
    4. a sufficiently powerful server, and a well-constructed database, you can make your solution available to as
    5. many FileMaker Pro users as your hardware can handle. Suppose, however, that you need to make your
    6. FileMaker Pro data more widely available, perhaps to many hundreds of people spread across a large
    7. geographic area. These users may have lower-bandwidth connections or they may not wish to acquire or
    8. install the FileMaker Pro client software. FileMaker Server’s web publishing technologies offer a solution to
    9. this problem.
    10. The web technologies in FileMaker Server allow you to give your solutions a very broad reach—potentially
    11. as wide as the web itself. However, there are some tradeoffs to be aware of which this module will discuss
    12. in detail. For instance, web-based applications have neither the rich user interface nor the seamless ready
    13. connectivity of a native networked FileMaker Pro solution without considerable additional work from
    14. the developer. Under circumstances in which you need to make some subset of a solution’s functionality
    15. available to a very wide audience, the web technologies in FileMaker Server may be the right answer.
    16. Web technologies allow users to interact with a FileMaker Pro solution using only a web browser. In some
    17. cases, such as with Instant Web Publishing, the browser may display a fairly faithful rendering of a FileMaker
    18. Pro solution’s look and feel and require little additional development effort. With Custom Web Publishing,
    19. screen fidelity and automated functionality are traded for increased power and flexibility. The web
    20. publishing option you choose will be guided by your project aims.
    21. All of the files used in this module may be opened using an account name of Admin and a blank password.
    22. Objectives
    23. When you have finished this module, you will be able to:
    24. • Understand the different web publishing options available with FileMaker Pro
    25. • Configure FileMaker Pro and FileMaker Server Advanced to publish data using Instant Web Publishing
    26. • Create web-friendly layouts and scripts for use with Instant Web Publishing
    27. • Create a custom home page for Instant Web Publishing
    28. • Use the PHP Site Assistant to generate a variety of web sites that publish FileMaker Pro data by using
    29. Custom Web Publishing with PHP
    30. Contents at a Glance
    31. 12.1 FileMaker Web Publishing
    32. 12.2 Configuring FileMaker Pro and FileMaker Server Advanced for Instant Web Publishing
    33. 12.3 Designing for Instant Web Publishing
    34. 12.4 Scripting for Instant Web Publishing
    35. 12.5 Creating a Custom Home Page
    36. 12.6 Using the PHP Site Assistant

About the FileMaker Training Series at San José State University