FileMaker has extremely functional charts, but one tricky part I’ve found is making the x-axis display in an appealing format. The values along the x-axis can overlapIf you have a lot of records. Summaries can help with this, but what if you don’t know how many points you’re going to have to plot, or the range which would need to be summarized?

For example:

A client of mine has an environment monitor that monitors temperature in a manufacturing facility to make sure it doesn’t get too warm for either people or machines. The manufacturing managers need to be able to view current temperatures, as well as historical temperatures over a time range to figure out work schedules. This time range could be a few hours, a few days, or a few weeks. There could only be a few data points which need to be charted, or hundreds. We can’t summarize the data by day or hour because of the potential ranges they could look at, which could be whatever they feel like. This leads to charts that look like this:

Poorly Formatted FileMaker Charts

So how can this be cleaned up? Really we only need to show x values at the beginning, end, and a few points evenly distributed between them.

Well here’s a calculation to help you do that!

If (Get(RecordNumber) = 1 or

Let([$pointCount = 8; $fc = Get(FoundCount); $rn = Get(RecordNumber)];

$rn = (Round( $fc/$pointCount * Round($rn*$pointCount/$fc; 0); 0)))

; Environment::Read Timestamp )

Plug this calculation into the x-axis data field, replacing Environment::Read Timestamp with the data you want to show on the axis, and set the $pointCount value to be the number of points past the first data point that you want to show. You will always have this number of values displayed on the x-axis, no matter how many points you have in your found set. A properly formatted FileMaker chart

Much better!




By popular demand, here’s the calculation and strategy for related records. You will need two unstored calcs for the record numbers where related::_c_RecordNum = get(RecordNumber) and MainTable::_c_RelatedRecordCount = Last(related::_c_RecordNum)

If (related::_c_RecordNum = 1 or
Let([$pointCount = 8; $fc = MainTable::_c_RelatedRecordCount; $rn = related::_c_RecordNum];
$rn = (Round( $fc/$pointCount * Round($rn*$pointCount/$fc; 0); 0)))
; related::xLabelVal )

Count(related::_kp_ID) can be used instead of MainTable::_c_RelatedRecordCount = Last(related::_c_RecordNum) if you really don’t want to make a new field, though that would be slower to calculate.