Why would ExecuteSQL only give you "half of the data"? What query did you use?
Another option that does not use ExecuteSQL:
Assuming that you have one record for each level and you want a count of all the records in each level, you can define a field as a "count of" summary field that computes the count of some field in your table that is never empty. Select that summary field as the field to use for you Y series data. Select your Level field as the field to use for your X series data. Make sure that the "summarized data" option is specified as the chart's data source option.
Then sort your records by level and you should see one data point on your graph for each level that has at least one record. With this chart, you can perform different finds to chart different sub sets of the total records in this table if you need that result.
Well, in a standard SQL query, I would do:
SELECT Grade,COUNT(1) FROM Table GROUP BY Grade ORDER BY Grade;
While this works as a standard SQL statement giving me a colum with the unique values and a column with the number of occurrences of each value.
When I try to extrapolate this into Filemaker, I get a Y axis scaled for the total number of entries in the table, and along the X axis I get a grade value for EACH of the entries (so 83 entries along the X axis).
I'll see if I can manage to try your suggestion...
Well I tried to do something along the lines of your suggestion, but I get the impression that I'm doing it entirely wrong ! :(
The SQL query (from the original MySQL database that the data was imported from) that I pasted earlier provides the first image attached. My attempt at your suggestion produces the second image (chart) attached.
I have check in the Data Viewer, and the queries under the DataViewer show correctly... but in the chart I just get the garbage... :(
TO use the ExecuteSQL() function, you would set up a query for the Y axis that is only the counts you want:
SELECT COUNT(1) FROM Table GROUP BY Grade ORDER BY Grade
Then you'd set up a second ExecuteSQL function call for the X axis labels:
SELECT Grade FROM Table GROUP BY Grade ORDER BY Grade
Then be sure to select the "delimited data" data source option.
Perfect, that's exactly what I was after !!
Many thanks !