Learn FileMaker Scripting, Calculations, Relationships and Reporting from the guy who actually wrote the book, Scriptology: FileMaker Pro Demystified. With over two decades of experience teaching FileMaker, John Mark Osborne will help you understand tough FileMaker concepts with ease. Intermediate and advanced FileMaker techniques will be taught throughout the three part tutorial series. In order to better understand every nut and bolt that goes into designing a solution, a single file will be created from scratch. The chosen solution is an invoicing solution for its familiarity to a wide audience and flexibility of applying techniques to other solutions. The completed FileMaker file at each stage is provided to assist with the learning process.
Learn about John Mark Osborne's experience in the FileMaker market so you know you are being by an expert developer.
Find out how to greatly enhance your learning experience with this short video.
There are very many good reasons why an invoicing solution was chosen as the structure for this tutorial.
Reviewing the completed invoicing solution will help you understand the goal at all times and assist in the learning experience.
One of the most common mistakes is not planning your solution and programming yourself into a hole.
The first step to designing a database is creating a new FileMaker file or solution.
Housekeeping fields track the history of a record including creation and modification information.
Field types, naming conventions and general information about field creation is covered in this section.
Every table should have a primary key field to uniquely identify records, even if it is never used in a relationship.
There are three standard methods for adding fields to a layout and a fourth special technique.
Organizing the fields in a grouping familiar to most people will help with the ease-of-use.
Add a title naming the table and merge fields displaying the creation and modification information.
Efficiency in programming is key so let's see how the process of adding fields can be streamlined.
The size of your FileMaker window in comparison to the screen resolution is an often overlooked consideration in the development process.
Since the Status Toolbar will eventually be hidden from the user, let's start by creating navigation buttons to move from record to record.
Recreating the Status Toolbar allows you to regain valuable screen real estate.
A consistent row of buttons at the top of the window helps to define a good graphical interface.
Tab Control objects allow you to easily create a tabbed interface in order to optimize layout real estate.
Nobody needs to be a graphic designer with the built-in interface themes that come with FileMaker.
Make objects grow or stay in a particular location of the layout when the window is resized.
Form and List view are the backbone of interface design in FileMaker.
Using buttons with simple scripts, we can provide more natural navigation between form and list view.
Data modeling is the process of translating a real-life scenario into electronic format, aka a database.
I'm not a big fan of defining relational rules, preferring to teach by example, but some people learn better with definitions.
Entity-Relationships Diagrams (ERD) describe the structure of your database in graphical terms.
Let's add the three new tables along with their appropriate fields as defined by the ERD.
Creating relationships is as simple as clicking and dragging your mouse.
The Anchor Buoy method of relationship design is by far the most popular amongst professional FileMaker developers.
Layouts based on the anchor buoy relational design system will be created for each of the new tables.
Let's review the changes that needed to be made on the duplicated layouts and add a few new changes.
Populating a foreign key using a pop-up menu is the easiest method.
Let's use a portal to interface with the many-to-many relationship.
A couple more features are required to allow the addition of products to the portal on the Invoices layout.
While the portal works fine now, a little bit of polishing will make it even easier to use.
Let's examine the Lines table more closely to see what is created when data is entered through the portal.
Totalling a portal is as simple as adding a calculation with a single function.
Relationships can move data both directions between related tables.
Context or Perspective are all important when analyzing relationships.
Let's aggregate the portal that shows invoices from the customers perspective but there's a little twist this time.
This script step drills down from the related information in a portal to the actual record it represents.
A table occurrence is how you create relationships in FileMaker.
Table occurrences determine the context for relationships as well as many other features.
Determining which related data shows in a portal using a calculation formula is easy to do.
Global fields allow you to add fields that act like interface instead of storage.
Global fields allow you to interact with a filtered portal to display the information you want at the moment.
Aggregate calculations add up the relationship and not the portal. This technique shows how to total a portal with a filter calculation.
The preferred method for totaling a filtered portal is via ExecuteSQL but it is quite a bit more complicated.
It's important to program your solutions so they work naturally in all scenarios for every user.
Adding additional global fields to filter the portal by date range complicates the programming but adds a level of sophistication for the user.
Since the filtered portal calculation was modified, the ExecuteSQL total calculation has to be updated.
Before there were Filtered Portals, there were Filtered Relationships. Both approaches have advantages so learn them well.
Let's complicate the filtered relationship by adding the global date fields into the mix.
Comparing and contrasting different approaches to a problem will help you become a better developer.
Joining a table to itself can provide excellent results. In this case, allowing us to see all customers related by company name.
Filtered relationships provide an excellent way to filter value lists.
Calculated value lists can often substitute for conditional value lists.
Calculated value lists can't always match the same level of complexity as conditional value lists.
Let's add a script to show all related customers and a calculation to count the number of related customers.
The relationship graph can quickly get out of control without good organizational techniques.
Data entry layouts should be designed specifically for on-screen and not dual purpose as a print layout as well.
Let's start with a basic customer contact list to make sure the fundamentals are in place.
The customer contact list is not very efficient in terms of page real estate or ease-of-use so let's improve it.
Labels may seem easy with the assistant but when they go wrong, these tips will help you perfect your labels.
Exporting data can be done by printing, creating a PDF or exporting.
FileMaker has a good word processor built-in so use it to create merge or form letters instead of a third party application.
An analogy to marbles will help you understand reporting.
Building a sub-summarry report requires knowledge about relational design.
Let's delve deeper into report creation to show some of the more advanced abilities.
Exporting a sub-summary report requires a couple of extra steps.
Creating a layout to print an invoice is more complicated than duplicating the data entry layout and removing the buttons, tabs and colors.
Let's complete the task of creating the invoice layout!
Scripting can be defined as automation, a macro and even a programming language.
A detailed discussion of the interface features in the Script Workspace.
This video identifies the script steps with the most depth and the also the ones you should avoid at all costs.
Let's start our official discussion of scripting with simple but very useful scripts for sending email and opening a web site.
Learn how to use the Set Variable script step to move information between two tables.
Learn how to use script parameters to create dynamic or adaptive scripts.
Use script parameters to create a single script to perform finds by each letter of the alphabet.
Locating birthdays in the current month is a handy marketing tool.
When duplicating a record, only the parent is duplicated. This script automates the process of duplicating the child records as well.
Batch printing invoices for your records is more efficient than printing them as each customer makes a purchase.
Electronically track your inventory with FileMaker calculations.
Leveling inventory with script is more efficient than a calculation field.
A script isn't complete till you've plugged all the holes and allowed for every possibility.
Control how a sub-summary report displays from browse mode.
Consistent, concise and meaningful naming conventions are also important for scripts.
John Mark Osborne has been working with FileMaker Pro for over 20 years. He earned his FileMaker Pro degree working on the frontlines of Claris technical support. John was the Technical Lead for FileMaker, serving as an escalation path for other technical support representatives and writing a large portion of TechInfo (now called Knowledgebase), their technical information reference. John also acted as a liaison between the technical support department and the FileMaker development team, reporting bugs, attending product meetings and acting as resource for the development team.
John Mark is best known as the author of the popular FileMaker Pro web site Database Pros. The Database Pros web site has been on the internet for 20 years and gets over 35,000 hits a day by offering free FileMaker Pro tips, tricks and techniques.
John Mark is also well known as the co-author of the wildly popular Scriptology book and CD ROM. John Mark launched a hands-on training class based on his Scriptology book and it has become so popular that it is now taught at MacWorld Exposition in San Francisco and New York. Most recently, John Mark was one of twelve trainers in the United States authorized to teach their new FileMaker Training Series of classes.
John Mark has been a featured speaker at the FileMaker Developer Conference for 15 years. At the 1999 Developer Conference, he was awarded the FileMaker Pro Excellence award for "creation of outstanding technical resources".
John Mark is a former technical editors for Advisor magazine working on the tips and tricks section which is the most popular column in the magazine.
John Mark has recorded beginner, intermediate and advanced tutorial videos for FileMaker 8, 9, 10, 11, 12, 13, 14 and 15 totaling thousands of hours.
John Mark Osborne is 1 of 50 Platinum level FBA (FileMaker Business Alliance) working closely with FileMaker, Inc. and their most important customers. John Mark Osborne has also passed the rigorous FileMaker 7, 8, 9, 10, 11, 12, 13, 14 and 15 certification tests and is proud to display the FileMaker 7, 8, 9, 10, 11, 12, 13, 14 and 15 certification logos.