2 Replies Latest reply on Dec 16, 2016 2:03 PM by goutam

    Multiple charts in layout


      I created a chart in a layout and sorted in using Semester and it worked correctly.


      I wanted another chart by the side and so I created a new table instance in the relationship and I modified the filters in the script "Apply Year Filter" under the Admin folder in the script workspace. I also have a dropdown to select the year.


      The left chart splits the TotalSummary of the Approved hours in the table by Semester.

      In the right chart, I want to split the chart by Centers which is not working properly.


      I have attached the file, the layout is "Graph 1612" under the Admin folder. Any help is appreciated.


      Username & password: csd


        • 1. Re: Multiple charts in layout


          You can do this. Here's the concept:

          Each chart has to pull its data separately, and to do that, you cannot have both pull the data from the current found set in its current state. If the data is sorted to show semesters, then both will show semesters. The charting mechisnm works on the sort, so sorting by semesters will plop the data on the chart correctly for the left one, but NOT the right one. to get the right one correct, you need to sort by centers, which messes up the semesters.


          So you have to make each chart pull its sorted data independently.


          I revised the chart to use ExecuteSql. Have you ever used that function in FileMaker 12 and up? This is one way to pull the data you need for each chart.  This method gives you complete control over which chart pulls which data and how it is sorted.

          Notice in my sample, I set it up to pull data for a specific year, the year in your global field. Updating the field auto-updates the chart, no button press needed.

          Check this out. You'll get the jist of it, and we are happy to help you understand it more. Note: ExecuteSQL is so powerful because you can pull data from any table from anywhere and you don't have to worry about finding the records first. ExecuteSQL finds, sorts, sums up the data in one shot for you.

          Read about ExecuteSQL here: https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/  It does take some work to understand it, but once you do, you can create 100 charts on a layout showing different things.


          (Now 100 executeSQL statements can be challenging performance-wise. probably not best to do 100 charts. A few will be okay.


          You could possibly set up the charts on a global Table Occurrence and then set up relationships to see and sort data correctly; that, I think, is more trouble than it is worth, but it is possible. You'd need one TO per chart.

          1 of 1 people found this helpful
          • 2. Re: Multiple charts in layout

            Worked liked a charm! Thank you. I had posted this as a question and so I am unable to accept this a correct answer,