Example 4: Charting related records
This example is based on the membership database of a fictitious travel firm, The Corporate Travelers Club. Use the FileMaker Pro tutorial sample file, Sample.fp7, to create this chart. The sample file is located in:
FileMaker Pro 11/English Extras/Tutorial
FileMaker Pro 11 Advanced/English Extras/Tutorial
In this example, you will create a bar chart that displays membership fees collected by each company. You will place the chart on the Companies layout, which contains address information for each company in a separate record. When you are finished, your chart will update to show member data for each company as you browse through the three company records.
|
1.
|
|
1.
|
|
2.
|
Click the Relationships tab.
|
You see the relationships graph. The relationships graph shows all the tables in the current file.
When you create a relationship between two tables, you make the data stored in either table accessible to the other table.
|
3.
|
In the Members table, click the Company field and drag a line to the Company field in the Companies table, then release the mouse button.
|
You see the relationship you have created. The relationship is based on matching data in the Company field in both tables. Records are related when data in the Company field in one table matches the data in the Company field in the other table (for example, when data in both tables is ABC Company).
|
4.
|
Click OK to save this relationship.
|
|
1.
|
Notice the Companies layout lists address information for XYZ Inc., which has members in The Corporate Travelers Club.
|
2.
|
Click
 to browse through the three records and see information for the other two companies that have travel club members. |
|
3.
|
|
1.
|
Click the Chart tool
 in the status toolbar, then click in the body layout part underneath the fields and drag a large rectangle where you want the chart to appear. |
|
Choose Bar.
|
|
|
Click
 and choose Specify Calculation. In the Specify Calculation dialog box, type “Fee Paid by Members of “ & (including all characters shown). Next select the Members table from the list on the left, double-click ::Company in the list, and click OK.FileMaker Pro will use the text you typed as the beginning of the title and the data from the current record as the company name. Because the table associated with this layout is Companies, you must specify field names from the related Members table for the title to display correctly.
|
|
|
Click
, choose Specify Field Name, choose the Members table, choose ::Last Name in the list, then click OK.Data values from the Last Name field will appear as labels for the bars in your chart (X-series). This is the data you are comparing.
|
|
|
Click
, choose Specify Field Name, choose the Members table, choose ::Fee Paid in the list, then click OK. The bars in your chart will show the data values from the Fee Paid field (Y-series). This is the data you are measuring.
|
|
|
Choose Related Records.
|
|
|
Choose Members.
|
|
1.
|
Click Format Chart.
|
|
2.
|
|
3.
|
Note FileMaker Pro encloses your title text in quotation marks when you click outside the X-Axis Title field.
|
4.
|
|
5.
|
|
6.
|
|
7.
|
You see placeholder data in the chart on your layout. You must switch to Browse mode to chart data from the sample database.
|
1.
|
|
•
|
If your chart is hard to read or if the maximum value for the X-axis is higher than 300, return to layout mode, select the chart, and drag the handles to make the chart larger. Then return to Browse mode to verify the chart.
|