The Philosophy of FileMaker - Part 1

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.

The Data Viewer has a whole new calculation dialog complete with a pane splitter and an auto-evaluate feature.

The Layout Objects Window places all the objects from a layout in a palette, giving you even more control over your layouts.

The new Window Cards feature will answer all your wishes for a new Show Custom Dialog feature.

Find out about training courses, consulting and solutions from Database Pros.