12 Replies Latest reply on Jun 16, 2014 9:46 AM by philmodjunk

    Charting from a value list

    CurtisHowe

      Title

      Charting from a value list

      Post

           I have a database that manages the social account info for our clients and I want to create a dashboard. 

           I would like to have a value list of our clients then go in and pull up a value list of all of that clients Facebook pages and have the charts show the data for the one selected Facebook page. 

           I am stuck on how to make this work. I can get it to show data for the client yet it shows up all data for all Facebook pages.

           Each client and each Facebook page has its own unique ID.

            

        • 1. Re: Charting from a value list
          philmodjunk

               How do you store data from the client's face book pages in the database? Is this in a related table with one record for each facebook page? What kind of data from such a record do you want to chart?

          • 2. Re: Charting from a value list
            CurtisHowe

                 PhilModJunk I do have a relational database that stores the info about following and interaction on their sites. Each client has there own id and each Facebook page has its own id. We have data entered in to the database each week to keep the accounts current.

            • 3. Re: Charting from a value list
              philmodjunk

                   This still does not tell me what you want your charts to show. I can imagine some possibilities but prefer not to guess--especially when you say charts and not chart--which suggests that you have more than one chart in mind.

                   Selecting a value from a value list can either be used with a script to perform a find or it can assign a value to a field from your layout's table that is used as a match field to the related records that you want to access. Either method might be used to establish a data source for a Filemaker Chart.

              • 4. Re: Charting from a value list
                CurtisHowe

                     What we want to show is a chart that displays their following growth and one that shows the activity of likes, comments and shares. 

                     I can get the charts to work if there is only one Facebook page yet if the client has several Facebook pages how do I use a value list to have the charts switch between the info for each Facebook page? 

                • 5. Re: Charting from a value list
                  philmodjunk

                       Does that mean that you have these tables and relationships (but with your names instead of mine)?

                       Clients----<FBPages

                       Clients::__pkClientID = FBPages::_fkClientID

                       and you only want to chart data from a single page for a given client instead of data that combines data from all that client's pages?

                       Such a chart could be set up on a Clients layout or an FBPages layout. From the clients layout, you'd use a relationship or SQL query inside a calculation field using ExecuteSQL (assuming that you are using FileMaker 12 or later).

                       But if you want to chart data that combines data from several records linked to the same client, I recommend setting up the chart on a layout based on the FBPages table.

                       These are design issues that then will determine how to set up your value list(s) for selecting what you want to chart.

                  • 6. Re: Charting from a value list
                    CurtisHowe

                         Thanks for the advice!

                         So my DB does have the set up you detailed out. My goal is to have a series of charts that will show the FB data for the FB page that is selected from a value list drop down menu. When a client logs in they should use the drop down to select which Facebook page they want to display data from in the charts. 

                         All the FB data for all the Fb pages is kept in one table with each FB page having its own ID number. We just need to pull out the records with the right ID number selected and chart the data.

                    • 7. Re: Charting from a value list
                      CurtisHowe

                           It appears that the charts display the info for the first Facebook page yet when I select the other page from the drop down menu all it does is change the name of the second page to the first page in the data. Now the drop down menu reads two pages with the same name and different ID's. Which is annoying since I have to go back and manually switch the Facebook name back in the table view for all the records.

                      • 8. Re: Charting from a value list
                        philmodjunk

                             What you can do is define a field in Clients, SelectedPage.

                             Link it to a new Tutorial: What are Table Occurrences? of FBPages like this:

                             Clients::SelectedPage = FBPages|Selected::__pkPageID

                             Format SelectedPage with a conditional drop down list of values from FBPages. Field 1 would be the __pkPageID field and Field 2 would be some kind of name or description field from FBPages. You can then select an option that shows only the name/description field--hiding the ID number from view.

                             The "conditional" part of this setup uses your original relationship to limit the choices to only those pages linked to the current client record. If you've never created a conditional value list, I've included links on the topic at the end of this post. (You probably only need read the thread of the first link and then maybe download the demo file to see a working example.)

                             Then you'd set up your chart to chart date from FBPages|Selected instead of FBPages.

                             There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                             The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                             Forum Tutorial: Custom Value List?

                             Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                             Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                             Hierarchical Conditional Value lists: Conditional Value List Question

                             Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                             Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                        • 9. Re: Charting from a value list
                          CurtisHowe

                               Thanks! 

                               I have set up conditional value lists that only show the correct list of pages for the selected client. Ive noticed that when I try to change which page in the value list I want to see all it does is try to rename the page I want to see as the page name I am looking at.

                               Also, I noticed that the data in the charts shows all the data for the client with all their pages rather than just one. 

                               We have specific KPI's set up for each client which we track and all the data is showing up not just for the one KPI we want to look at. 

                               Im thinking I have a relationship issue or something that is getting in the way. Any guess?

                          • 10. Re: Charting from a value list
                            philmodjunk

                                 And what relationship do you currently have linking these tables?

                                 

                                      Ive noticed that when I try to change which page in the value list I want to see all it does is try to rename the page I want to see as the page name I am looking at.

                                 Best guess is that you have the wrong field from the wrong table set up with your value list format so that you are changing the data in a record from the related table instead of changing a field in the layout's current record.

                                 

                                      We have specific KPI's set up for each client

                                 I have no idea what a "KPI" is from what you have posted thus far.

                                  

                            • 11. Re: Charting from a value list
                              CurtisHowe

                                   KPI stands for Key Performance Indicator. They are specific goals we track for our clients ( i.e. Clicks to website, etc)

                                   The relationship for our KPI's is- KPI Data 2::ClientID = ClientSheet::ClientID

                              • 12. Re: Charting from a value list
                                philmodjunk

                                     That is the first you've mentioned of such a table and such a relationship. If the data in this table is what you are charting, then you'll need to describe your tables and relationships in much more detail before I can help you.