How can you chart the first 10 records from a portal?
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.
Write a loop that goes through theh first 10 portal rows and create permanent variables ($$) for the chart to use.
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
Perfect, thanks guys
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:
topTenData = ExecuteSQL(
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.
Co-author of O'Reilly's FileMaker Pro 12: The Missing Manual
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?
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:
- 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.
Retrieving data ...