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

  • 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.

  • John

    Just found this and it is really great. However, I am having a problem with it showing more than the first timestamp with large data sets. I have weather readings every 15 minutes from June 2010 through Sept 2017 (255,000+). If I show the data for 1 month of a specific year (e.g., June 2013), no problem. If I try to show all the data, the data for 1 year, or for several months for 1 year, I only get the first data point (once I got the middle data point as well). Any thoughts on what might be happening or how to correct this? Thanks in advance.

    • It sounds like there’s an error in the calculation for your x-axis values. Double check and make sure you’re using the correct formula, either for the records themselves or for the related records.

      • John

        I copied and pasted from above, replacing Environment::Read Timestamp with my timestamp field.

        I haven’t used the “Let” expression much, but I think I followed the logic. I even tried recreating with a simple case statement and with an if statement in the else field (for only 2 data labels), but still same result for full dataset.

        Could it just be too many records?

        Thanks for the quick reply.

        • It’s possible. I’ve hit the limit on what is graphable before. How many records do you have?

          If you really do have a lot of records the chart won’t actually be able to show them all anyway. It sounds like your use case is very similar to what is above. If you’ve got way more points than your chart has pixels in width you can omit some fraction of the records, such as every other record, or two of every three to cut your charting points down. The chart would look the same, but FM won’t be trying to display thousands of points on a chart only a few hundred pixels wide.

          • John

            Total data set is 255,000 records. If I wanted to limit, think the same calc would work for the y-series?

          • Are you actually trying to chart all 255k records at once? If so, you’ll definitely want to include only a fraction of those in your found set. Try to reduce that to a few hundred, depending on the width of your chart.

          • John

            I’m not per-se trying to plot all 255k records. I want to show data at the overall level, then zoom in to the year level, and the month and year level. I stumbled upon your calc and thought it would be good for th x-axis labels. In the past I’ve done summaries and averages to show similar data, just trying to see what could be done easily with a few quick calcs.

            As for your last question, I was talking about censoring some of the records of the y-data.

          • John

            So I decided to make another table and summarize by day, which brought me from 255,000 to around 3,000 records. Calculation works great on Mac and on iPad Pro, but regular iPad is still a bit wonky. Wonder if a refresh will help on the iPad?

          • 3k records is still a lot for a line chart where you most likely can’t actually chart that many points just due to the DPI of the screen, but I’d expect it to be successful, if a bit slow. If you can cut that number down you might have better success on an older iPad.

          • Are you asking about labeling the y-axis? FileMaker automatically labels your y-axis and gives decent controls for setting ranges and intervals, so you wouldn’t need to use a formula like this for the labeling there, if that’s what you’re asking.