7 Replies Latest reply on Feb 7, 2013 9:08 AM by sprosser

    Charting Portals

    RickWells-Cole

      How can you chart the first 10 records from a portal?

        • 1. Re: Charting Portals
          taylorsharpe

          Write a loop that goes through theh first 10 portal rows and create permanent variables ($$) for the chart to use. 

          1 of 1 people found this helpful
          • 2. Re: Charting Portals
            RickWells-Cole

            Thanks, I understand the principle of looping through the first 10 records to grab the data.  I could then write the data to a new table in order to graph it.  I am not sure about writing it to $$ variables, please could you explain a bit further, thanks

            • 3. Re: Charting Portals
              jbante

              By setting the data you want to chart to $$variables rather than writing data to a new table, you can build the chart (using "Current Record (Delimited Data)") without having to create a new table.

              • 4. Re: Charting Portals
                RickWells-Cole

                Perfect, thanks guys

                • 5. Re: Charting Portals
                  sprosser

                  Rick:

                   

                  If you have v12 you can use a simple ExecuteSQL() function rather than a loop. You don't need a variable, just put the calc in the Chart dialog for your data axis. This is simpler, plus it updates automatically if the data changes where a script needs to run again to refresh the data. Something like this will work:

                   

                  Let (

                   

                  topTenData = ExecuteSQL(

                   

                  "SELECT yourField

                  FROM ChildTable

                  ORDER BY yourField Asc";

                   

                  ""; ¶) ;

                   

                  RightValues ( topTenData ; 10 )

                   

                  )

                   

                  FYI, the "Asc" part of the ORDER BY argument is optional, but I like to be explicit. Tweak it a bit for your labels and you're done.

                   

                  Susan Prosser

                  DBHQ

                  Co-author of O'Reilly's FileMaker Pro 12:  The Missing Manual

                  • 6. Re: Charting Portals
                    RickWells-Cole

                    I have v12 but cannot get my head round this one or how to apply it!.  I have 10 portals all coming from the same table, the portals are sorted and filtered to display top 10 data under various categories.  I have figured out how to do this with loops, delimeted data and variables. Would your method work under these circumstances?  How would it know which portal to target?

                    • 7. Re: Charting Portals
                      sprosser

                      ExecuteSQL should work but you'll have to adjust the calc to ORDER BY or WHERE the data the same way your portals are sorted and filtered. So given the calc:

                       

                      Let (

                       

                      topTenData = ExecuteSQL(

                       

                      "SELECT yourField

                      FROM ChildTable

                      ORDER BY yourField Asc";

                       

                      ""; ¶) ;

                       

                      RightValues ( topTenData ; 10 )

                       

                      )

                       

                      - topTenData is a variable that holds the result of the SQL statement

                      - SELECT identifieds the field containing the data you want to chart

                      - FROM identifies the table you need the data from

                      - ORDER BY sorts the resulting data

                       

                      This SQL statement grabs the yourFIeld data for the whole table, sorted in Ascending order. The RightValues() bit chops off the top ten from the bottom of the list. In a bar chart, the biggest value will be at the top of the chart. You may need to ORDER BY yourField DESC and then grab the LeftValues () to get the data to appear the way you want it on a column chart.

                       

                      Sorry if this sounds like overexplaining. But I figured that if you know how the parts work then you can adjust the ORDER BY bits to work with your portals. If it's more complex than sorts (say you have categories or similar filtering out some values) then you may need to add WHERE or GROUP BY arguments to limit the results to match your portals. Beverly Voth's The Missing FM 12 ExecuteSQL Reference does a great job of explaining SQL to mere mortals. (Thanks, Bev!)

                       

                      FYI, ExecuteSQL() doesn't target a portal at all. It targets the source table (called ChildTable in my example) and works in the absence of context, sort order and found set.