Creating Dashboards with FileMaker - Anvil Dataworks Anvil Dataworks

Creating Dashboards with FileMaker

September 28, 2011 by admin

Windows MediaQuickTime format

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:

Mac Dashboard example

Mac Dashboard Example

Windows Dashboard / Widgets

Windows Dashboard / Widgets

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:

  • Customers
  • Invoices
  • Line Items
  • Products

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.

Data Normalization

 

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:

Example 1

Example 1

Example 2

Example 2

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.

Not Good! Bad! Something is Wrong

Not Good! Bad! Something is Wrong

Good to Go! All Clear

Good to Go! All Clear

Neutral, Not Good, Not Bad

Neutral, Not Good, Not Bad

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:

Core Relational Design of the Database

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:

Dashboard Relationships for Widget Calculations

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:

Month Relational Design

The Months table will handle all the “Monthly” reports that we mentioned earlier, Monthly Revenue, Profits, etc.

List of Monthly Revenue, Profits, and Unpiad Amounts

List of Monthly Revenue, Profits, and Unpiad Amounts

***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.

Example:

Invoices for the Month

Invoices for the Month

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:

Constant Relationship Design

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.

Charting Engine

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.

A Simple Chart Design

A Simple Chart Design

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).

The Dashboard:

The Dashboard

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


9 Comments »

  1. Hi, please advise where can I download the sample file. Thanks

    Comment by Pedro — February 4, 2012 @ 10:46 pm

  2. I watched your video on youtube. Would like to see the sample file if possible. I am trying to figure out how you showed only the top 12 customers in your lists without having a portal trying to bring in all records.

    Thanks

    Comment by Bobby Cross — May 9, 2012 @ 2:57 pm

  3. Sorry about that. The sample file should be available again.

    Also, to show the top 12 customers in your list, Sameer made the recommendation to either use a filter on the portal or just show only 12 records in that portal and sort by the gross purchase amount (descending).

    Comment by admin — May 9, 2012 @ 3:05 pm

  4. Do you have a version of this example file for filemaker 12, that we can look at?

    Thanks, M

    Comment by Martin Collins — May 15, 2012 @ 9:19 pm

  5. Hi,
    I just finished watching your recording on creating a Dashboard and would appreciate all the assistance I can get on doing this on my own. I use Filemaker Pro Advance 11 on Windows 7 and as such your demo file won’t work on my machine as it was designed for the MAC. So my request is this…
    1. Do you have the sample file for Windows?
    2. And can you furnish me with materials or links to further broaden my knowledge on Dashboards?

    Thanks

    Comment by Victor Offiong — September 2, 2012 @ 3:12 pm

  6. Thank you for a great dashboard webinar and a truly elegant sample file. I learned more about Filemaker development in the last hour than I’ve learned in the past couple of years. This will help me streamline our business’s solution that has been growing like an octopus since Filemaker 5.

    Thanks again.

    Comment by Rebecca Awodey — September 9, 2012 @ 2:05 am

  7. Ditto on the great webinar! I just came across it. The Dashboard table and the months table, with their relationships, are such an elegant way to do this!

    Thanks a lot for posting the example file as well.

    Comment by Dan Simonson — November 13, 2012 @ 12:06 am

  8. Great stuff, thanks. I like the portal filtering

    Comment by Shawn Surma — March 12, 2013 @ 11:13 am

  9. I successfully created dashboard using your model as a start. However, I would like to have a layout that list all the customers with the total invoice per month for this year and previous. I don’t have a clue of how I should have this tackled.

    Thanks a lot

    Comment by Emmanuel — February 7, 2014 @ 7:53 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment