A properly formatted FileMaker chart

Posted by & filed under FileMaker.

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!

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)

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.

  • Salman Javaheri

    This works great! Is there any way to make it work for related data?

    • This will work with any data you can use for graphing in FileMaker. Replace the Environment::Read Timestamp field with whatever you need and if your fields and relationships are set up properly you should be fine.

      • Salman Javaheri

        It doesn’t work if the data comes from a related table, I believe it’s because the Get(RecordNumber) function is problematic with related data. I’ve resolved it for now: Because the data my graph refers to is script generated, it’s not too much hassle to mark the record that constitutes the ‘start’ of the data category (e.g. month/day). The X axis then uses an ‘if’ function, leaving out the axis label if the ‘start’ field is not populated.

        • I’ve updated the post with the calculation you requested.

          • Salman Javaheri

            Wow! Thank you very much!

  • Thanks for the tip. It would be great if this could be made to work with related data as already mentioned.

    • I think the updated solution will solve your issue. You’ll need two new calculation fields, but it should be quick.

      • Thanks! I couldn’t make it work right away and have to get back to it later today. I am plotting summarized data, so my guess is it has to do with that.

  • Well you all asked for it, so here it is.:

    If (related::_c_RecordNum = 1 or

    Let([$pointCount = 8; $fc = Count(related::_kp_id); $rn = related::_c_RecordNum];

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

    ; related::_kp_id )

    Make an unstored calc in your related table where _c_RecordNum = Get(RecordNumber).

  • Could this script be modified to show only, for example, the first 5 labels and ignore the rest?

    • Yes, but that would seem to defeat the purpose of that calculation. You’d have 5 unreadable labels stacked on top of each other (if there were a lot of records) and then nothing for the rest of the chart.

      case ( get(recordnumber) < 6; table::mylabel; "")