For each company, you can view a list of all work orders along with the total work hours. But it's hard to visualize how the individual work orders contribute to the total.
How about adding a chart that gives subtotals for the different kinds of work that have been performed? FileMaker Pro makes it easy to add charts that present your data so that it can be understood at a glance.
Let’s try adding a chart to your solution now.
Place a chart on the “Hours Summary” tab and configure it to display the work hours for a given customer subtotaled by the work description.
- Make sure you are viewing the “Customer Detail” layout with the “Hours Summary” tab selected.
- Choose the View menu > Layout Mode ⌘L or Ctrl-L
- Select the Chart tool from the status toolbar.
- Draw a square taking up all the space inside the “Hours Summary” tab.
A Chart Setup dialog box appears. The options you will need to change are all located in the right-hand column of the dialog box:
- Locate the Title field in the Chart area of the right-hand column and enter the value Hours by Type into the field.
- Locate the Data field under X-Axis (Horizontal) and enter the value Work Orders::Description into the field.
Because this is the name of a field, you must get it exactly right. If you don’t get it right, FileMaker Pro will put quotes around it and treat it as text (which won't help you populate your chart with data).
- Locate the Data field under Y-Axis (Vertical) and enter the value Work Orders::Hours Total into the field.
Again, you are referencing a field, so be careful to get the name correct.
- Locate the Show data points on chart checkbox under Axis Options and select it.
- Locate the Label Angle field and change it to 25.
This causes the X-Axis labels to be tilted 25 degrees, making more space for long names. (The X-Axis is selected by default).
- Select the Y-Axis button.
This causes new options to be displayed, because the Y-Axis is associated with numeric values (total hours), while the X-Axis is associated with text (work description).
- Select the Show major ticks checkbox and enter the value 50 to the right of it.
- Select the Show minor ticks checkbox and enter the value 10 to the right of it.
- Select the Set minimum field and enter the value 0 to the right of it.
- Select the Set maximum field and enter the value 400 to the right of it.
IMPORTANT: If you don’t set values for these fields, FileMaker Pro determines the maximum and minimum values based on that chart’s individual data. While this makes individual charts look better, it makes it difficult to compare charts with each other. Whenever you design a chart, you need to consider whether you want individual charts to look good, or for the charts as a group to share standard measurements.
Now let’s choose a color scheme that combines well with the logo and the layout theme.
- Switch to the Styles area of the right-hand column.
- Change the Color Scheme dropdown to “Sea Glass”.
Try out some other color schemes to see what they look like too.
Finally, you need to tell FileMaker Pro how to group your hours data in order to produce the summary values that you want.
- Switch to the Data Source area of the right-hand column.
- Change the Chart Data dropdown to “Related Records”.
This causes new options to be displayed.
- Change the Related Table dropdown to “Work Orders”.
This will chart work order data for each customer record being displayed.
Sorting the work order data allows FileMaker Pro to group it together, giving subtotals based on how the data is sorted. In this case, we sort by the “Description” field, which indicates the type of work performed.
- Click the Specify button under Sort Order of Related Records.
A Sort Records dialog box appears.
- Select the "Description" field in the left-hand column.
- Click the Move button.
The “Description” field appears in the right-hand column, indicating that the data is sorted based on the contents of this field.
- Click the OK button.
The Sort Order dialog box disappears, returning to the Chart Setup dialog box.
- Click the Done button in the lower-right of the Chart Setup dialog box.
You are returned to the layout.
That’s it — let’s see how the chart has turned out!
- Choose the View menu > Browse Mode ⌘B or Ctrl-B
- Select the “Hours Summary” tab again to see what the chart looks like.
Here is the chart for the first customer record “Bryant Research Park”.
- Look at some other customer records as well, observing how the chart changes from customer to customer.
The chart looks great now, but it’s important to consider the impact of data accumulating over time. Imagine your solution with another year’s worth of work orders. At that point, the total hours would probably exceed the 400 hours maximum that you put in place.
The problem is that the chart displays cumulative hours for all work orders for a given customer, year after year. In order to keep the totals within a specific range, you would need to limit the data to a specific year, year-and-quarter, or year-and-month.
We’re going to let this slide for this solution, but be sure to keep this principle in mind when you're creating charts.