Join Cris Ippolite for an in-depth discussion in this video Creating quick charts in Table view, part of FileMaker Pro 12 Essential Training.
Thus far, we've discussed creating reports in our FileMaker databases that you can set up and allow users to run against your stored data or even print, and save to a PDF if you like. Charts, of course, have been adding an additional layer of visibility for the reports, and in FileMaker 12, now you can allow 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, users can create and print a Quick Chart in Browse mode and even create and save their own layout in the FileMaker database.
So you're first going to want to have a layout in your database that contains as many fields as you want the user to be able to work with and then present that layout in Table View. So what I've got here is the customer list presented in Table View, and I could have added a ton more of these columns if I want my users to be able to pick and choose and be creative with their charting. But for the sake of this example, I've picked about six of them here, and these are all available to us to be able to chart, and express in sort of a visual way through the different charting options. Users who are familiar with working with spreadsheets and other applications should already be familiar with this concept anyways. So if you want to chart group data, then you're going to want to start by sorting one of the columns.
When you start in Browse mode, FileMaker sets up a chart based on the field type and contents of the active field, and the active field is going to be the one that you right-click on when you want to create the chart. It also depends on the sort order specified so that's why it's important that we go ahead and sort these. So let's try sorting by right-clicking or Ctrl-clicking on a Mac on the Status field. I am going to sort ascending, and we can tell it's sorted because we get this little cellphone signal looking icon on the top of the column, and we also can see that they're all sorted by the different status. Now let's right-click again and choose Chart by Status. This is one of the new features in 12. We say Chart by Status, and up pops the Chart Setup window. So, this is something that users can do as long as you give them access to be able to create the things that are necessary in this interface. They can do all sorts of really neat things in this view. So now that we have the chart set up, the window will appear by default, and it has built a bar chart displaying the three statuses represented in our data set, and it's also displaying the bars as a count of all the invoices for each status. Quick Charts will work best for Column, Bar, Pie, Line, and Area charts out of all the options, because they all have single data series, and one axis, and there's also options for one or more data series on other axes as well.
You can change the Chart Type, for example, to a Pie Chart if you wish, and now you can see the different statuses shown up as a part of the whole, or any kind of chart that works best for the data that you're evaluating. But this can get even more interesting if you decide to work with more than one field at a time. So for example, let's hit Cancel, and let's sort by state, Sort Ascending and then let's right-click the Invoice Total or click the arrow, and now we see that the option for Chart By a certain field has changed.
Now, you can see that it's taken into account the two different fields that we've been working with, we've got Chart by Invoice Total, which we can do, or we can do Chart by Invoice Total by AddressState. So now what it's done is it's added two data series here, the Invoice Total is the active field which is determined by the fact that we've clicked on its header and then the Sort field is the AddressState so that's how FileMaker determines this. It's generically saying chart by active field sorted by this value. The Chart By value plots individual data points in a current column, and the Chart by Sort field plots data from the current column in groups based on the sort order. So let's choose Chart Invoice Total by AddressState, and up pops the chart setup. When you chart data in Table View, FileMaker users see the current state of the file and the context of your selection to be able to estimate chart settings.
You can see some of them already added. So the X axis is the AddressState, the field is already chosen, the title is already given for the Y axis and the field is already chosen, all sorts of stuff is already done here, and we can see that all in our preview. And depending on the complexity of your data set and the chart type that you choose, you might need to use the Chart Setup dialog box to make adjustments to a QuickChart. We can see that FileMaker has chosen the Bar Chart and plotted each state individually with the bar representing the total invoices in each state. Keep in mind that we actually do not have a field to find in FileMaker that totals up the invoices, the Chart tool has summarized that for us. FileMaker Pro is choosing the appropriate summary type based on the active field which was the Invoice Total field. But what if we did want to work with more than one data series? Let's close the Chart Setup and go back to the layout one more time.
This time let's unsort. So let's sort by address state so that's our Sort field, and now I am going to click on the Invoice Total column header, and when I'm holding down the Command key on Mac, or Ctrl key on Windows, I'm also going to select the DiscountTotal. So what I've done is I've established the Sort field, and now I'm actually picking two different data series, the Invoice Total is one data series and the DiscountTotal is another. Now when I choose the dropdown on either DiscountTotal, or Invoice Total, we'll just pick DiscountTotal, I see that my Chart Options get even more extensive. So I can chart just by the one field DiscountTotalor I can chart by the DiscountTotal sorted by AddressState, so broken down by the different states or I can do this new option, Chart by DiscountTotal as one data series, InvoiceTotal as another data series, all broken down by state. Let's choose that one.
Now, up pops our Chart Setup, and you can see that we have two data series represented here, one of the data series is the total invoices by each state and then the other data series reflected here in green are the total discounts for each invoice by state. And we can see those here, you'll notice that the X axis was already determined, we see that along the bottom where we have all the state abbreviations and then we have the Data field which is the Customers::AddressState field. But now look and notice that we've got two different data series, and I could add a third data series here if I'd like to, but instead, we'll just stick with the two that we've got. So we see the first one is the DiscountTotal field, the second is the Customers::InvoiceTotal, and we can toggle these and change it to a calculation or whatever we'd like to do. But what we're doing is also telling it what to do with this data. These are the fields that it points to, but we're telling it to total up all those values in the data series. I could also decide to show a legend if I'd like to, to make it easier for the users to interpret. But what I am going to do here instead of showing two different side-by-side columns, I am going to go, and I am going to pick the Stacked Column instead. So now you see what we've got is that we've got the discount showing on the bottom in the green and then the rest of it is the Invoice Total, and I'd actually really like to swap these because I think it makes more sense to show the discount on the tip of the bar. So what I do here is I use the little handles, and I drag that to the top, and I switch these around. So now, the InvoiceTotal is in the green, and the DiscountTotal is actually on top represented in the beige color.
I could also choose any of the other chart types that have multiple series like, for example, the area or line. But I think in this case, it's most compelling to show the Stacked Column, whichever one makes the most sense for your data. But the great thing about this is that your users can make all these decisions on their own, and of course, they have all the other options available to them, you can show the data points on the chart. I can format those as numbers, currency just like we were doing with fields on a layout, and I can do the same for the Y axis. Users also have the ability to choose different chart styles, choosing 3D, different color schemes, making the background transparent if they'd like.
You can show grid lines or minor gridlines or major gridlines, even position the legend around at different locations, whether or not you want to have a border, all those things that we saw on the previous view. This is an area where they probably won't do much changing, but in the Quick Charts, they're going to be able to work against current found sets, so these options are already chosen for them, and we see that our sort value is already indicated, because the user had gone in and sorted by state before they created the chart.
So it's pretty intuitive stuff, and it doesn't take much training to get your users to understand. But the best part is that the users can actually save their chart as a layout in your database or just print the chart that they just created. So it's not just for this one session that they're here, working with the chart, they can go in here, and say, Print Chart, and they can print it to a printer as a hard copy, or if they've got a PDF option available to them, they can save this as a chart, as a PDF, whatever they're allowed to do through their normal print drivers, and they can also save as a layout if they, like so. that they can go back to this later. So for example, I can say Save as Layout, up pops the screen that ask me to name the layout, we could probably come up with something a little less for both of these, but that's fine, we'll hit OK, and now we see a new window popped up with this new layout, and you'll see that even when we close this and a user navigates over, you'll see that we've got a new Charts folder created, and we've got a DiscountTotal, InvoiceTotal by AddressState, and when you navigate to it, it automatically sorts everything, breaks everything down, and displays a resizable chart inside the new window on screen, and of course, this could be printed at any given time as well. And if the user wants to make any changes, and of course you've given them the ability to access Layout mode, they can go in and simply double-click on it and make any kind of changes that they'd like to. Since the chart will require that the fields be sorted and show totals, FileMaker has also created a script. This is something that we're going to get to later on in this title, but it's basically a script or something where that stores different actions much like the button that we assigned the script to.
But it assigns it to the layout so that anytime it's loaded, it will automatically sort the records as they need to be so that you can view the layout properly. And when I mentioned earlier that users must have the correct privileges, I meant that the users account that they use to log into the database must allow them to create layouts, fields, and scripts in some cases, because some of those summary fields were being created on the fly, the layout was being created on the fly and so was the script. So you won't see those options available if the user has logged in with limited access. And another very important note here is that you do not have to be in Table View to do a Quick Chart. I demonstrated it here in Table View, but you can start by right-clicking any field on any layout. If we go into Products List, for example, you'll see that there's the Chart by option in any of the contextual menus in any one of the list views. So FileMaker provides an ad hoc patent pending charting process that can be used even if your users cannot conceive of the graph first.
Now, you can decide on the graph and then tweak it as you go along, plus users can create their own charts without you having to build it for them. Charting allows the layouts to do most of the heavy lifting of sorting, grouping, and adding summaries and the context of your database makes educated guesses about what you might be interested in charting.
- Comparing flat vs. relational databases
- Creating databases from templates
- Determining what tables you need
- Understanding relationship types
- Defining key fields and creating relationships between tables
- Creating fields
- Using new FileMaker 12 container fields
- Creating, duplicating, editing, and deleting records
- Importing and exporting data
- Managing layouts and layout objects
- Applying new FileMaker 12 themes to layouts
- Finding records and working with found sets
- Building reports and charts
- Authoring calculations
- Creating and triggering scripts
- Working with relationships in scripts, calculations, and charts