First, I would like to thank everyone who attended the first ever FileMaker Academy Webinar, How to Create Dashboards in FileMaker. This blog is intended merely to summarize the webinar and to act as a reference to the development strategies that were discussed during the presentation.
Dashboards, or Management Information Systems, are designed to give employees information pertaining to their companies unique business process. Think of a car dashboard, and how it displays information about the status of the car (e.g. Speed, Odometer, Gas, Engine Heat). Items known as “Widgets” display information about a particular category, and the combination of these widgets creates the Dashboard. Some familiar examples:
Take notice of the different widgets and their orientations, and pay attention to certain details that are displayed within each widget.
Our goal in FileMaker will be to adequately define what information we want displayed on the Dashboard, and from the Developer standpoint, how to get the information to define our widgets. One way of defining the required values, take a look at the reports that need to be run regularly. What values ultimately, are required from that report? From there, pass the puck to the Developers.
In our example file, we are using a standard Invoice system. This database includes the following core tables:
- Line Items
Most developers should be familiar with this process, as it discusses the use of a join table to connect Invoices to Products through a Many to Many Relationship. Since we are using this setup, values such as Price and Cost will be looked up through relationships and copied to the correct database location. This will be a pivotal piece of the puzzle when we put it all together for the Dashboard.
Before we move on, let’s discuss some of the requirements for good Widgets and Dashboards.
Normalized data is the process of organizing the data in our systems, to minimize data redundancy. The goal of normalizing data should be to define and store smaller, well-structured information. This usually means taking larger sets of data, and when prudent, dividing this information into multiple tables. In addition, this requires development of the relationship between the new tables. “Minimize” will be the key word in this description, as we will need some data to copy to other locations.
The general idea is to copy only what you need!
Examples of bad data entry:
In the first example, notice how one field holds multiple values. In the second example, data that isn’t required to copy over is being transferred to the second table. See the duplication of the phone number field.
A general rule, whatever the Primary Key is for the table (Customers -> _CustomerID) defines what set of information should be defined in that table.
Key Performance Indicators
KPIs as they are known, are ways of indicating status through the use of color.
We can use KPIs as color-coding for problematic records, status changes, and notifications through simple color assignments. As we can see, given world values are used to define the color schemes – this is something most people know.
Think of the color codes we use everyday (traffic lights, Stop Signs, Crossing Signs, etc…)
Tools in FileMaker
In order to translate the report values to FileMaker, we need to understand certain concepts of development, and we have to know which tools to use in order to define the widgets.
- Global Fields – User Interface Objects that allow for dynamic relationships and keys, as well as temporary storage of values
- Relational Concepts – Using Comparative Operators such =, <, >, ≥, ≤, ≠ since we will be defining relationships that use date ranges as well as other filter options
- Understanding Elements of the Charting Engine – There are hidden gems in the interface that seem to be overlooked or misunderstood
- Heavy Calculation Work – What can I say, you will be doing a lot of math
Let’s start the database system, take a look at the core system relational design:
The design of the Dashboard will focus on the invoices table, however we will incorporate some Customer details as well.
As we mentioned earlier, consider the reports that we work with regularly, and translate the numbers to a Dashboard environment.
Let’s imagine a series of simple reports for the Invoice system:
- Monthly Revenue Report
- Monthly Profit Report
- Quarterly Revenue Report
- Unpaid Invoices
- Summary by Market
- Late Payments Report
This is enough for me to get started on the widgets for my dashboard.
Some Points to consider:
- If monthly calculations are needed, define a Months table to process the calculations
- Use a global table called Dashboard, where you can define starting points and needed calculations
- Use comparative operators to define your relationship sets
- Use Delimited sets of data when Charting, to define your own sets of data
- Lookups and Auto Enter Calculations can create a way of passing valuable info through tables, especially for defining items like, service cost, product price, etc…
The Dashboard Relational Diagram:
As seen in the diagram above, the relational diagram for the Dashboard consists of multiple occurrences of the invoice table, as well as the previously mentioned Months table.
The Month Table Relational Diagram:
The Months table will handle all the “Monthly” reports that we mentioned earlier, Monthly Revenue, Profits, etc.
***See the example file for details of all calculations
The Month table is defined to have exactly 12 records, with calculations to define the start and end dates of each month. This can be done through a variety of functions, and logic. My example is one way of addressing this issue. In addition to the current year in monthly dates, I want to add in the previous year dates. See the calculations in the example file for details.
Take notice of the KPI (Key Performance Indicators) used in the list. Green Profit represents no unpaid invoices and no issues, whereas the blue profit records represent a neutral amount, since there are unpaid invoices (showing it’s number in red).
In this way, I have created a starting point for my Dashboard – a list of the Months of the year – so now I need to define the related Invoices.
To create the relationship, I will need to use comparative operators in my Edit Relationship Dialog.
As you can see from the example, we use the DateStart and DateEnd with operators such as ≤ and ≥ to define the relationships. At this point we can write calculations to define the total invoices for a given month, with Revenue and Cost as totals to work with.
TotalInvoices = Sum(InvoicesPerMonth::Total)
From the Dashboard I can then list the total Revenue next to the Month in my list. With a little preparation, I can also have the Total Cost per Month.
Some other examples of needed relationships:
This particular relationship will also include a Constant value, which represents the records in Invoices where the status is equal to “Unpaid” – the unpaid invoices for that month.
To see the full spread of relationships, see the example file.
Now with these concepts in mind (comparative operators, Constant Filters for Relationships) I can start to build my Dashboard relationships and create the sets of data needed for all of my widgets.
The last part to the development of the Dashboard, will be creating the widgets. Again, for details please refer to the example file, but let’s look at the definition of one Chart.
The first thing we should point out is the “Use Data From” dropdown. One of the most powerful methods for charting information is custom defined delimited data, as this option allows the developer to define their own sets of values for label and chart data. Using functions like List(), and Sum(), as well as in some cases, using the GetValueListItems(). These functions can produce the values and “lists” of values that we need to chart. Keep this in mind when you want to define a specific set of labels, or if we need to define the multiple sets of Vertical Axis values (the charted data).
Our overall goals will define the screen itself, however some things that you want to keep in mind when developing the Dashboard:
- Create a flow for the layout, what lines will the User follow when looking at the Widgets? Is there a direction that the eye naturally follows?
- Even and well sized diagrams, since we will want the Dashboard to “look” and “feel” good to the users
- KPIs, even a subtle use can make the difference in what is displayed on screen
- Widgets should display appropriate data, so make sure that labels and charts are well defined (notice the Merge Fields sitting on top of the pie charts, which give the user sums of revenue, etc…)
Again I would like to thank the attendees of the Webinar, and I hope that this blog will serve as an excellent reference for Dashboard development.
If you have specific questions or would like more information, please contact Sales@anvildataworks.com
Example File – Tetragrammaton Dashboard