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!

 

UPDATE:

 

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.

Share
  • 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; "")

  • Emma Wood

    Just found this…I want to chart number of admissions to our academic program, but only show the last 3 years. How would I handle that? Can this formula do that for me?

    • You’ll probably be charting something like number of admissions per month or per year over a period of three years. In a case like that you’re going to have a fixed, limited number of points on your x-axis (months or year) and wouldn’t need to use a calculation like this

      • Emma Wood

        where/how do I set fixed points?

        • You’re just doing regular charting. There’s nothing special that you need to do. If you’re showing the last three years as one column each you’d get those three data points as your y values either through a found set, sub summary, related set, etc., and the three year numbers as your labels on your x-axis. https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help%2Fcreating-charts.html%23

          • Emma Wood

            I work in academic years and would like to show only the most recent 5 academic years. Is there a way to make things “fall off” the chart as time moves forward?

          • What you’re looking for is the regular behavior of a FileMaker chart. If your chart is based on your found set you need to make sure your found set only includes the last five years. If your chart is based on a related table then you make sure that the relationship filters down to only the last five years.

            If you have different numbers of points in the last five years, and aren’t sure if it will be readable, then you may want to consider adding a formula like the one described in this post to filter the labels down.