Table view in FileMaker has various hidden benefits like the ability to create charts like the one demonstrated in this video.
- [Voiceover] In FileMaker's table view, you can allow your users to create their own chart reports on the fly without you having to build a layout for them. This concept is referred to as Quick Charts, and with a little bit of guidance your users can create and print Quick Charts even while staying in Browse mode. So first you're going to want to have a layout in your database that contains as many fields as you want the users to be able to work with. And then present that layout in table view. You can even train your users to go to any list view that they see, and just to choose table view, which can also be done under View and View as Table.
This won't affect the layout for any other users that are in the system because this will be session-based, which means that any changes that you see here will only be visible to the user within their own session. So if you want to chart group data, you're going to want to start by sorting by one of these columns. So here we are in browse mode. Now what FileMaker's going to do when it sets up a Quick Chart is chart based on the following factors: the field type and contents of an active field, now the active field is the field that we're going to right-click on when we create the Quick Chart, and then the field type and contents of the selected fields, and even a sort order that we've specified.
So, to start this, let's go into Status, and sort by simply clicking on this drop-down here. And we're going to say Sort Ascending. So we see that we have three different statuses: Active, Pending and Inactive. And now that we've sorted, which we can see we've sorted because we see the little Sort Ascending icon on the column header, let's go back to the drop-down and choose Chart by Status. So so far, pretty easy.
As a matter of fact, this could be just the report that your users are looking for. So I've created a column chart that shows me the Active, Inactive, and Pending values for my found set. And Quick Charts will work best for Column, like we see here, Bar, even a pie chart, and Line and Area charts as well. We can change this chart type to Pie, and we can add a couple of values real quick to make our charts a little bit more compelling.
Plus we can make it look a little bit more dynamic by adding some colors and some depth. But now this can get even more interesting if we want to work with more than one column at a time. So let's close this window, and now let's sort by state. And we've sorted by state, and we can tell that because of the Ascending icon on the top of the column. And now let's go to InvoiceTotal. So we already know that state's going to be a part of our chart, because we sorted by it, but if we right-click on InvoiceTotal, and we go down to the Chart option, you see we have two of them.
We can either chart just by the InvoiceTotal, or we can chart by InvoiceTotal by AddressState, and that's because we sorted by AddressState, and we clicked on the InvoiceTotal. In this case, the InvoiceTotal is the active field, and the AddressState is the sort field. So let's choose this Chart InvoiceTotal by AddressState. Now, depending on the complexity of your data set and the chart type you choose, you might need to use the chart setup dialog to make adjustments to a Quick Chart. You see that FileMaker has automatically chosen a column again for us here and plotted each state individually with a bar that represents the total invoices in each state.
So here you see the state names across the bottom and the total invoice value across the sides. And now keep in mind, we still don't have any field in here that summarizes all the totals by state. This is just the chart tool doing that for us on the fly. And we can make adjustments to the chart, changing it to different types if we want to. Or just visually changing some of the styles. Making it 3D if we'd like. All of this we can do on the fly without changing anything about the fields in our database or anything on the layout.
So let's go one step further. Close this out, and this time let's sort by state. Then let's click on the column header for both InvoiceTotal and DiscountTotal by holding down our Shift key. So I select InvoiceTotal, hold down the Shift key, and select DiscountTotal, and those are two active fields now, and our AddressState is still our sort field. So let's look at the drop-down in the DiscountTotal field. Now when we go down to Chart, we see we have three options: sort by the active field, or sort by the active field by the sort, or sort by both active fields by the sort.
Let's choose Chart Discount Total, InvoiceTotal by Address State. Now we see that the chart setup selects a column that has two data series represented by different color bars. Let's give it some color. And this time what I'm going to do is I'm going to change the chart type to an Area. Now if this makes sense for your data, you can choose as many data series as you wish, just by clicking the column headers before you invoke the chart setup. In this case, I wanted two data series, so I Shift-selected two different column headers, and then the third one is what triggered the totals that we see along the side, because that's what I sorted by.
You can open and close this chart setup as many times as you wish, and your users can print a chart right from the chart setup if they wish, or if their account is set up to allow them to create their own layouts, they can hit Save as Layout, and now this file permanently has this report available to them, which can be run at any time, based on the found set and visible to any user. And by the way, if you're creating these charts for the purpose of creating, let's say, a presentation, a quick little tip here is if you right-click on a chart and hit Copy, you can then Paste from your clipboard into some other application and use the chart that you generated from within FileMaker.
Now I should point out that you don't necessarily have to be in table view to do a Quick Chart. As a matter of fact, if you're just in browse view, and you right-click inside of a field, you may notice that you also have the option for Chart. In this case, the same rules apply for active field and sort order. So in this case, you see we still have sorted by AddressState, and the active field that I clicked inside of is Status. So although FileMaker allows you to be able to add charts to your existing layouts, I think this is even a more compelling use of the Chart tool, where you can train your end users to create charts for themselves on the fly.
That way, you don't have to do any of the heavy lifting from a programming standpoint, and users can use their imagination to create these charts whenever they need them.
- Getting going with starter solutions
- Creating relationships in FileMaker
- Defining fields
- Designing user interfaces with layouts
- Working with records
- Finding and sorting data
- Building reports
- Understanding calculations
- Automating using Scripts
- What's new in FileMaker version 16