I would like these charts to dynamically update based on the found set of records, and not be dependent on any particular sort order.
That, in a nutshell, is the problem. The sort order needed to group records for one chart is not the sort order that you need for the other two charts.
Put global container fields in your trailing grand summary. Have your script go to a different layout, sort the records, capture an image of the chart and then set one of the container fields to the image of that chart. GetlayoutObjectAttribute can be used for this purpose.
Use Calculation fields with ExecuteSQL to generate a delimitted data series for each "wedge" for each chart. (One Calc field for each chart.) The challenge here is that you have feed data to the SQL WHERE clause that produces a set of values that is in synch with your found set of records used for your report. If you use a script to collect the user specified criteria and that then performs the needed find to pull together the report, you may already have the needed data in global fields so that they can be included as value parameters in the ExecuteSQL function call.
You are a brilliant guy PhilModJunk. Option 1 seems like it would do the job. Option 2 seems like the more elegant solution but my SQL skills are very limited. Could you send me a code sample?
Thanks Phil. I think you are the same guy that helped me with my "diminishing list" problem the last time. Your solution worked perfectly! I thank you 2x.
Take a look at the SQL query used in this thread: FMP 12 Tip: Summary Recaps (Portal Subtotals)
The "summary recap" it produces could be the return separated list of values you'd use for a chart if you used the delimitted values option.
I find the free SQL Explorer tool from SeedCode helpful when working with the ExecuteSQL function.