7 Replies Latest reply on Feb 7, 2016 4:20 PM by FredH

    Chart showing linked & filtered data

    FredH

      Hi Experts,


      I have 2 tables :

      • TableA with
        • __pkActivityID
        • ActivityName
      • TableB with
        • __pkTurnoverID
        • __fkActivityID
        • Year
        • Turnover


      For each record in Table A, I have 20 records of Table B.  Each year being the turnover for the 20 past/future years for the linked activity.

       

      Whilst being on a form linked to TableA, showing a record from TableA - i.e. with __pkActivityID = 1, I would like to show a line chart with the turnover for the current activity (__pkActivityID = 1) for all the years < X (i.e. 2020).

      I did several tests using ExecuteSQL but none of them gave the expected results.

       

      Could you tell me which syntax I should use to achieve this?

       

      Thanks in advance for your input & time!
      Kind regards,

       

      _Fred_

        • 1. Re: Chart showing linked & filtered data
          macwombat

          Hi Fred.  I'm no expert on ExecuteSQL so I can't advise you on that.  However it can be done reasonably easily without it.  I've attached a sample file that you can look at.  Couple of things to note:

          1.  In the Chart Setup under Data Source - you need to set the Chart Data to "Current Record (delimited data)".  This sets the chart to look from the current record in your form view across to the related records.

          2.  I used the list function in the calculations for the x & y data eg. List ( TableB::Year ).

           

          HTH.  Chris

          • 2. Re: Chart showing linked & filtered data
            FredH

            Hi Chris,

             

            Thanks a lot for your answer!  I did not know the list function and it looks nice but I am still not sure how to make it work.

            Let's say that in your example, I would like to only show the years 2001 until 2010 on the graph whilst TableB contains values for 1998 until 2015.  How would you combine this with the List function?

             

            Thanks in advance for your input!
            Kind regards,

             

            Fred

            • 3. Re: Chart showing linked & filtered data
              macwombat

              Hi Fred.  There might be a way to sort that out in the calculation, but I think an easier way is to create 2 global fields called ChartStartYear and ChartEndYear in Table A and then create a new Table Occurrence (TO) of Table B that is related to Table A by the ID fields and the Year fields (see screenshot).  After this, you update the chart to work on the List of the values from the new TO.  Then you can dynamically change the range of years that the chart is showing by simply changing the values in the global fields.

              Screen Shot 2016-02-08 at 8.47.33 AM.png

              I've attached an updated sample file.  Have a great day!  Chris

              • 4. Re: Chart showing linked & filtered data
                FredH

                Hi Chris,

                 

                That is exactly what I would like to do! Though, one question again :-)

                My TableA contains 100+ activities and the ChartStarYear & ChartEndYear are Global variables within the same table.  I understand that I can change these on every record of my TableA and that the result is exactly what I need but would it be possible to obtain the same result without putting them in this table? 

                 

                It seems not "100% proper" to have these values repeated on every record as I would need to have them only once.

                For example, I have a "Parameter" table that contains all my global variables and that is not linked to TableA nor TableB.

                 

                I suppose that I will not be able to use the same technique anymore as the join cannot be performed as in your example?

                 

                Sorry for the extra question although you solved the issue but I try to push it a little further in order to also extend my understanding of the possibilities :-p

                 

                Thanks again!
                Kind regards,

                 

                Fred

                • 5. Re: Chart showing linked & filtered data
                  FredH

                  Hi Chris,


                  In addition to the "general knowledge" question of above, I have an issue with the approach you gave.  Let me explain :

                  For all activities, I have the years 1998 to 2015 but I do not have Turnovers for all these years for all activities.  In example, for the second activity, I have only turnovers for 2005 to 2010. 

                   

                  With the List function, I have my years that are correctly shown on the graph but my turnovers are not linked to the correct years.  The list function skips the blank values and therefore the TO values of 2005 to 2010 are "linked" with the years 2000 to 2005 (when StartYear = 2000 & EndYear = 2005).

                   

                  Any idea? :-s


                  Thanks in advance!
                  Kind regards,

                   

                  Fred

                  • 6. Re: Chart showing linked & filtered data
                    macwombat

                    Hi Fred.  Re: global fields - this is a common way of filtering the results of a relationship and to work in your context they need to be in Table A.  If you are worried about the value being used in all the records I think it only stores them once even though they display in every record. [ from FileMaker help: "A field that uses global storage contains one value that's used for all records in the file." ]  The other advantage is that if you have multiple users, each user can have different values in those fields to be displaying a different set of data.

                     

                    I've thrown an extra field in to Table A which is a constant of 1.  Then added to the relationship to filter records where the turnover is not equal to or greater than 1.  This removes those records from the chart, which should resolve the issue for you.  At any rate this gives you enough to play around with different scenarios.  Try making changes to the file I've supplied to see how different things work with the globals and the relationship and the graph and it will help you to learn more about how FileMaker works without affecting your own database.

                     

                    V3 of the sample file is attached.

                     

                    All the best.  Chris

                    • 7. Re: Chart showing linked & filtered data
                      FredH

                      Excellent!  Thanks Chris