This is a limitation of FileMaker SQL. Using the SQL.Debug custom function to test this expression, I get this error message:
Expressions involving aggregations are not supported.
For those interested, this is the SQL.Debug custom function:
//the sql call results in an error, return empty so the error will be returned
_executeSQL = "?" ; "" ;
//the sql call is executed correctly, just return the result
This function is published on FileMaker Custom Functions
to check for updates and provide feedback and bug reports
please visit http://www.fmfunctions.com/fid/335
Prototype: sql.debug( _executeSQL )
Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
Last updated: 28 July 2012
You can use this with FileMaker Advanced's data viewer to get actual error messages out of failed uses of ExecuteSQL.
Thanks for your input! I was not aware of this SQL.Debug custom function.
Do you think there is a workaround for my specific case?
I found following example on another forum but I am not sure how to adapt this to my specific case, knowing that it is to be used with a graph :Let ( [$$vl = ExecuteSQL ("SELECT COUNT(tab), SUM(\"amounts\")FROM SummaryDataWHERE tab >15 GROUP BY tab"; "||" ; "" )] ;ExecuteSQL ("SELECT Field01, Field02, NUMVAL(Field01) * NUMVAL(Field02)FROM VirtualListWHERE Field01 IS NOT NULL"; "" ; "" ))
Thanks in advance for your additional input!
I don't quite follow how that would work. But I can see a way to chart this data using older "FileMaker" methods in place of the SQL. If you chart summary values from a found set, you can use summary fields and the getSummary function to compute and chart the same data.
Is it possible to combine the GetSummary together with a "filter"?
I could use Exploitation::Année as BreakField but I would also like to "filter" on Exploitation::Region as I would like to have one line per Region.
That would be a bit of a problem. It's not insurmountable, but I can see why ExecuteSQL would be a preferable approach here.
You'd need to set up a series of calculation field such as:
If ( Exploitation::Region = "region 1" ; Field with value to sum here )
You can then define a different summary field for each such calculation field. That's far from ideal, but if your set of regions don't change very often, it might be possible to use.
I'm not sure if there is a "pure SQL" way to generate these values or not, but I can conceive of a recursive custom function that takes two lists of values and returns a single list of delimited values for charting by dividing the items in list one by the items in list 2....
I could work with the additional fields but do you have an example of your second option? (recursive custom function)
After some tests, I am not sure that the GetSummary will work for me as I have to calculate a ratio of 2 summary fields (based on 2 group by's).
Do you have an example of this recursive custom function to check if I could use this ?
Thanks in advance for your input!
GetSummary could still be used. You specify the "inner" break field and still sort your data by both break fields.
Example: If you sorted "cities" by State and then by County, you'd use use "county" as your break field.
A recursive custom function requires Filemaker Advanced to create and install into your file. If you are charting large sets of numbers (1,000's of data points) it may not work or may be too slow:
//ListDivide ( List1 ; List2 )
//Divide each item in list1 by the corresponding item in List2. The same number of list elements must be present in both parameters:
Let ( [ vc1 = ValueCount ( List1 ) ;
vc2 = ValueCount ( List2 )
Case (vc1 ≠ vc2 ; "" ; // end and return no value if lists do not have equal numbers of values
IsEmpty ( List1 ) ; "" ; // terminate recursion when lists are empty
List ( GetValue ( list1 ; 1 ) / GetValue ( list2 ; 1) ; ListDivide ( RightValues ( List1 ; vc1 - 1 ) ; RightValues ( List2 ; vc1 -1)))
) // Case
) // Let
Just remembered a crucial limitation of charting groups of summarized data. Unlike a summary report, you can only sort on one break field. The getsummary calcs will work as I described, but you can't get the right groups for data points in the chart.
The work around is to define a calculation field that combines the two break field values such that by sorting on this calculation field, you get the same groups that you would if you sorted on your two break fields.
Referring to my States, Counties sorting example, You'd create a calculation field such as: State & "|" & County with a text result type and then sort on that calculation field.