FileMaker Charting

<html><head></head><body><p>Every day it seems, on the FileMaker Community, someone asks about how to work with FileMaker Charting. This question usually comes when I’m walking a dog, so I have to stand there on the corner and try to answer on my phone while my pup does his thing.</p>
<blockquote><p>Setting up a chart using the chart object can be quite confounding until the day it isn’t. And once it is easy to understand you’ll never forget how to use it. It’s like riding a bike or something else that you never forget. While this might not be a <a href="/blog/2018/02/15/learn-filemaker-like-a-boss/">‘boss-level’ technique</a>, it is still worth getting to know.</p></blockquote>
<p>So we are going to focus our attention on the chart object so that you can create a chart in relatively short amount of time. In this post, we’ll take a look at the foundation of the chart object: the data and how to gather it. In the past, there’s been some great <a href="https://community.filemaker.com/docs/DOC-3835">documentation</a> about it. But I’d like to zero in on the data gathering only. The rest you can read in the white paper.You can download the demo (below) and review the video.</p>
<div class="embed-vimeo" style="text-align: center;"><iframe loading="lazy" src="https://player.vimeo.com/video/260935613" width="1500" height="844" frameborder="0" webkitallowfullscreen="" mozallowfullscreen="" allowfullscreen=""></iframe></div>
<h2>Understand the FileMaker Charting Object</h2>
<p>FileMaker chart objects will render a returned-delimited list of data in a visual way. That’s all you need to know. If you can pass a list to the x-axis and a matching one to the y-Axis (as applicable), then you can display data on a chart. It is really that simple.</p>
<div id="attachment_327895" style="width: 285px" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-327895" decoding="async" loading="lazy" class="wp-image-327895 size-full" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/ListData.png" alt="These are valid for the FileMaker Charting object." width="275" height="334" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/ListData.png 275w, https://www.geistinteractive.com/wp-content/uploads/2018/03/ListData-247x300.png 247w" sizes="(max-width: 275px) 100vw, 275px"><p id="caption-attachment-327895" class="wp-caption-text">These are valid for the chart.</p></div>
<h3>There are some caveats:</h3>
<ul>
<li>The data in the x- and y-axis must match exactly. If you have four colors in a list in the x-axis, you must have four values in the y-axis with corresponding values.<br>
You can use an empty value but it must be in the correct spot in the list. I would recommend actually using a 0 instead of an empty value.</li>
</ul>
<p>Here’s an example:</p>
<div id="attachment_327894" style="width: 335px" class="wp-caption alignleft"><img aria-describedby="caption-attachment-327894" decoding="async" loading="lazy" class="size-full wp-image-327894" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/2018-03-18_08-53-25.png" alt="blog image number 1" width="325" height="154" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/2018-03-18_08-53-25.png 325w, https://www.geistinteractive.com/wp-content/uploads/2018/03/2018-03-18_08-53-25-300x142.png 300w" sizes="(max-width: 325px) 100vw, 325px"><p id="caption-attachment-327894" class="wp-caption-text">A chart of some data. What do you see missing?</p></div>
<p>Your list must match exactly:</p>
<p style="padding-left: 30px;">X-axis: “Jan¶Feb¶Mar¶Apr”<br>
Y-axis: “7¶4¶¶9”</p>
<p style="padding-left: 30px;">Or this:</p>
<p style="padding-left: 30px;">X-axis: “Jan¶Feb¶Mar¶Apr”<br>
Y-axis: “7¶4¶0¶9″</p>
<p>These are correct because there are four values in the list. Each value in the y-axis corresponds to a value in the x-axis.</p>
<p>This would be incorrect:</p>
<p style="padding-left: 30px;">X-axis: “Jan¶Feb¶Mar¶Apr”<br>
Y-axis: “7¶4¶9”</p>
<p>There is a missing value, the sales amount for March. In this case, the 9 would apply to March and April would be missing a value.</p>
<p>I must clarify that most of the charts in the object require an x- and y-axis, but not all. The pie chart requires “Category Labels” and “Slice Data”, but they’re basically the same thing.</p>
<h2>Making a list/Checking it Twice</h2>
<p>Now we just have to collect the data into a list, and that is done in a myriad of ways. Let’s take a look at them by first reviewing the three data sources of the FileMaker Charting object.&nbsp;In the Data Source tab, there are three options we can use.&nbsp;&nbsp;Let’s examine each one.</p>
<div id="attachment_327896" style="width: 262px" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-327896" decoding="async" loading="lazy" class="wp-image-327896 size-full" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/TheDataSources.png" alt="The Data Sources" width="252" height="191"><p id="caption-attachment-327896" class="wp-caption-text">The Data Sources of FileMaker Charting</p></div>
<p>And let’s examine it within the context of a table called SummaryData.</p>
<div id="attachment_328020" style="width: 577px" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-328020" decoding="async" loading="lazy" class="size-full wp-image-328020" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/SummaryData_Table.png" alt="The summary data we wil chart." width="567" height="276" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/SummaryData_Table.png 567w, https://www.geistinteractive.com/wp-content/uploads/2018/03/SummaryData_Table-300x146.png 300w, https://www.geistinteractive.com/wp-content/uploads/2018/03/SummaryData_Table-450x219.png 450w" sizes="(max-width: 567px) 100vw, 567px"><p id="caption-attachment-328020" class="wp-caption-text">The summary data we will chart.</p></div>
<p>This table holds the total sales for different product types ( Apples, Bananas, Grapes, Peaches, Pears) for each month of a year (2010-2017). We’ll use this data as much as we can throughout the examples.</p>
<h2>Current Found Set</h2>
<h3>Individual Record Data</h3>
<div id="attachment_327974" style="width: 476px" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-327974" decoding="async" loading="lazy" class="size-full wp-image-327974" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/Current-Found-Set-_individual.png" alt="Current Found Set: Individual Records" width="466" height="247" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/Current-Found-Set-_individual.png 466w, https://www.geistinteractive.com/wp-content/uploads/2018/03/Current-Found-Set-_individual-300x159.png 300w, https://www.geistinteractive.com/wp-content/uploads/2018/03/Current-Found-Set-_individual-450x239.png 450w" sizes="(max-width: 466px) 100vw, 466px"><p id="caption-attachment-327974" class="wp-caption-text">Current Found Set: Individual Records</p></div>
<p>It is often the case that we want to build a chart to display some data from the found set. For example, if we wish to display the total sales for a particular fruit for each month, we can use this data source option.<br>
This option (as shown above) will display on the chart all of the values of of a field for the x-axis and y-axis in the sort order of the records.</p>
<div id="attachment_327989" style="width: 251px" class="wp-caption alignright"><img aria-describedby="caption-attachment-327989" decoding="async" loading="lazy" class="wp-image-327989 size-medium" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartList-241x300.png" alt="A chart displaying the records in the found set" width="241" height="300" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartList-241x300.png 241w, https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartList-567x705.png 567w, https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartList-450x560.png 450w, https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartList.png 606w" sizes="(max-width: 241px) 100vw, 241px"><p id="caption-attachment-327989" class="wp-caption-text">A chart displaying the records in the found set</p></div>
<p>This chart shows the list view of the found set. I’ve placed the chart in the header part of the layout, but I don’t have to have the fields on the layout. This could be a simple form view with just the chart and buttons / fields with global storage for scripted finds.</p>
<p>This current found set option will respect the sort of the found set. If I change the sort from a monthNumber field (not displayed) to the Month field, the chart would update once the sort is finished.</p>
<p>And to address an earlier point, if one of these summary records had zero sales for a month, I would fill that sales field with a big fat 0 so it the value ‘shows up’ on the chart and the remaining values line up with their respective months.</p>
<h3>Summarized Groups of Records</h3>
<div id="attachment_327970" style="width: 514px" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-327970" decoding="async" loading="lazy" class="size-full wp-image-327970" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/CFS_Summarized.png" alt="Current Found Set: Summarized Records" width="504" height="256" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/CFS_Summarized.png 504w, https://www.geistinteractive.com/wp-content/uploads/2018/03/CFS_Summarized-300x152.png 300w, https://www.geistinteractive.com/wp-content/uploads/2018/03/CFS_Summarized-450x229.png 450w" sizes="(max-width: 504px) 100vw, 504px"><p id="caption-attachment-327970" class="wp-caption-text">Current Found Set: Summarized Records</p></div>
<p>We can also visualize the data as we would do on a subsummary report.<br>
Here’s a traditional subsummary report. This one has the body part removed and is just showing the sub summary sections.</p>
<div id="attachment_327994" style="width: 619px" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-327994" decoding="async" loading="lazy" class="size-full wp-image-327994" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/ss_Parts.png" alt="A typical Sub Summary set up" width="609" height="128" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/ss_Parts.png 609w, https://www.geistinteractive.com/wp-content/uploads/2018/03/ss_Parts-300x63.png 300w, https://www.geistinteractive.com/wp-content/uploads/2018/03/ss_Parts-450x95.png 450w" sizes="(max-width: 609px) 100vw, 609px"><p id="caption-attachment-327994" class="wp-caption-text">A typical Sub Summary set up</p></div>
<p>Within each sub summary part, I can place a chart. The chart summarizes the data in the group just below it, exactly as the sub summary part does.</p>
<p>Here’s the chart in the header, and is set up to include the Year in the x-axis, and a summary field (total of sales) in the y-axis. (the picture below shows a graphical bug in the chart set up in High Sierra. Arg!)</p>
<div id="attachment_327973" style="width: 625px" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-327973" decoding="async" loading="lazy" class="wp-image-327973 size-full" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartWithHeader.png" alt="A chart in the header" width="615" height="488" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartWithHeader.png 615w, https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartWithHeader-300x238.png 300w, https://www.geistinteractive.com/wp-content/uploads/2018/03/ChartWithHeader-450x357.png 450w" sizes="(max-width: 615px) 100vw, 615px"><p id="caption-attachment-327973" class="wp-caption-text">A chart in the header</p></div>
<p>In the Year sub summary part, another graph is placed, and it summarizes the each type’s sales for that year.</p>
<div id="attachment_327981" style="width: 628px" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-327981" decoding="async" loading="lazy" class="wp-image-327981 size-full" src="https://www.geistinteractive.com/wp-content/uploads/2018/03/subsummary_Year.png" alt="A chart placed in the sub summary part" width="618" height="536" srcset="https://www.geistinteractive.com/wp-content/uploads/2018/03/subsummary_Year.png 618w, https://www.geistinteractive.com/wp-content/uploads/2018/03/subsummary_Year-300x260.png 300w, https://www.geistinteractive.com/wp-content/uploads/2018/03/subsummary_Year-450x390.png 450w" sizes="(max-width: 618px) 100vw, 618px"><p id="caption-attachment-327981" class="wp-caption-text">A chart placed in the sub summary part</p></div>
<p>The current found set data source is a flexible way to pass a return-delimited list of data to the chart object. The options allow us to choose what to send to the chart: fields in actual values or summary information, as a sub-summary report shows. You can get a lot of mileage with this option, so consider it carefully.</p>
<h3>How to Collect the Data</h3>
<p>Since this chart displays the current found set, we simply have to find and sort the records for our liking. That’s it.</p>
<hr>
<p>In the <a href="/blog/2018/03/21/gathering-data-filemaker-charting-part-2/">next post</a>, we’ll look at the other two data sources of the FileMaker Charting object: Related Records and Current Record (Delimited Data).</p>
<p>&nbsp;</p>
<h3>Download the Demo</h3>

				<form id="edd_purchase_328090" class="edd_download_purchase_form">
										<div class="edd_free_downloads_form_class">
						<a class="button  edd-submit edd-submit edd-free-download edd-free-download-single" href="#edd-free-download-modal" data-download-id="328090">FileMaker Charting Demo</a>					</div>
									</form>

			
</body></html>

Gathering Data for FileMaker Charting, Part 1

The FileMaker Charting object can be mastered by understanding its data sources. We will look at the three sources and how to collect data for it.

Blogs tagged with: FileMaker Charting

Gathering Data for FileMaker Charting, Part 1

RECEIVE THE OCCASIONAL UPDATE.

NEW PROJECT OR COLLABORATION?

FOLLOW US.