3 Replies Latest reply on Apr 4, 2013 2:14 PM by wedge-eagle

    Charting question



      Charting question


           This should be simple (probably is) - I know one way to do this but it seems overly complex and/or looks in-efficient. One of you very smart people will have a better idea than me - I am sure.

           Problem - A Table with a Field (from a pick list) for example - 6 options.  Field="Source of client reference"  Options(pick list) - Internet,radio,word of mouth,referal,pamplet,Paper add.  

           Each record will have one of these answers.  Now to chart - "pie or bar not the issue" to get the data in a format that the chart can use -

           I need the  Feild title - matching the options and then a Count against each.


           Internet               50

           Radio                 23

           Word of Mouth 15


           Graph from this point is easy ... 

           but to get to here -

           Method I know - calc field (binary) for each option that sets "1" when match for option and the a summary field to tally the "1"'s  - So 12 new Fields plus the titles (in a variable or global) - or can i use the list ?

           Other Option (perhaps ?) Relate to new table with 6 records matching options then some sort of Count field (not sure how)

           All chart exampls I have found so far seem to use data already in neat numeric pre-formated data arrays pretty much ready to use.  The above seems like a lot of extra work to get to that point in my simple case.  Just seem to be missing a countif type function.  

           What the best (and easy to understand) method to use 

           Thanks in advance 




        • 1. Re: Charting question

               Harry Summons:

               Thank you for your post.

               The method you know (12 additional fields) is fine.  However, since your pop-up field is already set to take values from another table, create a relationship between the current table and the value list table, and link the two tables together using the pop-up field.  Next, in the value list table, create a calculation field with the formula:

               Count ( OriginalTable::Popup )

               This will then extract the number of times the pop-up value has been used in the other table.  You can then use the values from the calculation field to perform the charting.

               Let me know if you need additional clarification.

               FileMaker, Inc.

          • 2. Re: Charting question

                 Hi Harry,

                 If you are adept at SQL, you may be able to use the new ExecuteSQL function in FMP 12 to set variables with the counts without creating new fields


            ExecuteSQL("SELECT COUNT(…) FROM … WHERE …";"";"")
            • 3. Re: Charting question

                   Thank you -  I am using a value-list not a table for the pop-up. But the answer still works where I can either change to using a table for the value-list

                   Or just create a new table (with same values in the Key field as value-list).  - I suspect option one is what you would recommend (no duplication of the data) .  So that is the way I will go..  

                    Either way the count on relationship was the trick i had not figured out. 


                   Also thanks to Jade - still a better method than the original.  A lot of the charting examples I have been looking at do use SQL calls - Guess it depends if you want the chart data in a lot of variables or into a table.  I think when you start to get a lot of Non numeric type data to chart  EG more fields - for me I think I prefer a table for each field.(the chart then will be easier I think)   Probably just personal preference... Or could just be how much programing background  I have/havenot had ?  

                   Anyway Both are Much better than fileds  all over the place !