"delimited data" means that all the data to be charted exists inside a single field of the current record separated by return characters. Do you have such a field? You'd need one for your Y series and one for your X-series in order to provide the labels needed. Both the List function and ExecuteSQL can be used to generate such delimited data series.
The "alphabet soup" of your description also makes it difficult to follow. Actual table and field names can be helpful.
"Alphabet soup" hè, and I was trying to avoid confusion (-:. Ok, here goes. For the many-to-many relationship see the enclosed screenshot.
As to the delimited data, no I do not have that. I took "Current record" to refer to data based on a current record rather than a whole set of records.
I would like to show in the form of a pie chart how often individual animals appear in devices compared to how often all animals appear in devices (in percentages). So if a lion appears in 500 devices and all animals appear in some 5000 devices altogether, this would be 10%. The pie would then consist of a single 10% slice and another one of 90%.
Ideally, I would (also) like to get an overview of all the relative frequencies of animals and I actually use a stacked bar for that, but since so many species are involved this bar becomes rather unwieldy. Can't I just use a small window with scroll-bars in a form, so I don't have to adjust its size all the time as new species are added and users can just scroll down?
I took "Current record" to refer to data based on a current record rather than a whole set of records.
That is correct, but if you are going to plot more than one value in your chart, a field in that single record has to have the needed data in it in order for you to generate a meaningful chart. Since you describe two wedges in your example (10% and 90%, you need two values to chart in order to get the needed pie chart. This can be done with a calculation field that separates the two values with returns or you can set up a calculation for your Wedge data that produces the same list of values.
So if a lion appears in 500 devices and all animals appear in some 5000 devices altogether, this would be 10%
Are you able to calculate the 10%? If so, you only need subtract that value from 1 (100%) in order to get the second delimited value for your pie chart.
and does 5000 represent the total number of records in devAnim or some other count?
From a layout based on Animals, count ( DevAnim::animalID ) would appear to return the 500 from your example. If TotalRec is a "count of" summary field, it should return the same result as count here.
ExecuteSQL ( "SELECT Count ( AnimalID ) FROM DevAnim" ; "" ; "" )
would seem to return the correct count of all records in DevAnim. Thus:
Let ( pct = Count ( DevAnim::animalID ) / ExecuteSQL ( "SELECT count ( AnimalID ) FROM DevAnim" ; "" ; "" ) ;
List ( pct ; 1 - pct )
) // Let
would appear to work to produce your wedge data.
List ( Animals::Animal ; "All Others" )
would appear to work to provide labels for your two wedges.
This solution requires FileMaker 12 or newer. A different approach would be needed for older versions.
I'll be darned, that works like a charm. SQL here I come! Thanks a lot (once again).
PS. Is it correct that ExecuteSQL scripts are not supported in web publishing, i.e. that when I want to use a database in which such statements have been used they will not work on the web?
SQL was just a quick way to get the total number of records in the table. There are nonSQL ways to get the same value.
I also realized after the fact that I was having the calculations do more than is truly needed. You just need the count for each wedge. You should not need to compute percentages.
Let ( [ Wedge1 = Count ( DevAnim::animalID ) ;
Wedge2 = ExecuteSQL ( "SELECT count ( AnimalID ) FROM DevAnim" ; "" ; "" ) - Wedge1
List ( Wedg1 ; Wedge2 )
should also produce the same pie chart.