Here's a custom function that was shared here in the forum that I think all developers working with ExecuteSQL should have and use:
//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 set up the Data Viewer to test your SQL query like this:
ExecuteSQL ( "
then you click "Monitor", then click the pencil icon to re-open the watch expression for editing and check the Evaluate box at the bottom. This little gem then magically displays an honest to goodness error message instead of the dreaded ? that just might clue us in as to why there's a problem with the Query syntax.
My second suggestion would be to try defining a calculation field using the Year function to extract the year and then try grouping by that field instead of a calculation to see if the GROUP BY clause can't properly evaluate your expression.
Thanks for the custom function idea yet I prefer to work with a remote console (for me, that means using a Java IDE) since it gives me all the error information without all this FMP setup. Plus, you can browse tables and see data directly, which you can't do in FMP unless you go to a layout. But then, if you're in the mostly modal debug window, then, ....
Doing this query in a Java IDE with a JDBC connection to the FMP datbase with this query:
SELECT YEAR(date_first_received), count(year(date_first_received)) as cnt from vendors group by year(date_first_received) order by date_first_received DESC
Gives this error (referring to the group by expression on the third line):
[2014-12-03 10:55:28]  [FileMaker][FileMaker JDBC] FQL0001/(3:2): There is an error in the syntax of the query.
My question was really about possible FMP's GROUP BY limitations if any since this simple query doesn't work.
Being able to get a count of items by year is such a simple requirement, I'm still baffled why FMP isn't able to do this.
I could post a sample solution if necessary to demonstrate.
Yet your error information is not in the context of using ExecuteSQL and that might be a crucial difference here.
I repeat my suggestion that you replace the Year calculation with a reference to a calculation field that uses the year function to see if perhaps the Group By clause in FileMaker does not support using a calculated expression.
I sincerely appreciate your terrific replies, as always.
Yes, your suggestion does work, as if there were any doubt, though I was trying to avoid creating a calc field and instead stick to SQL.
Using your idea, however, works fine:
select YEAR_OF_FIRST_CONTACT_DATE, COUNT(YEAR_OF_FIRST_CONTACT_DATE) as numberContacts from VENDORS GROUP BY YEAR_OF_FIRST_CONTACT_DATE ORDER BY YEAR_OF_FIRST_CONTACT_DATE ASC
My goal was to be able to just rocket through queries without having to drop back into FMP to fill in (apparent) gaps in its SQL implementation, but whatever. It's nice that FMP has these easy workarounds, when necessary, too. :)
I didn't know if it would work or not. I was hoping it would work based on a half remembered post made by someone else.
I just researched Group By in FileMaker's SQL reference document and found:
The GROUP BY clause specifies the names of one or more fields by which the returned values
should be grouped. This clause is used to return a set of aggregate values. It has the following
GROUP BY columns
columns must match the column expression used in the SELECT clause. A column expression
can be one or more field names of the database table separated by commas.
By that info, I'd predict that the following would work:
SELECT YEAR(date_first_received as yr, ( count(year(date_first_received)) as cnt from vendors group by yr order by date_first_received DESC
Yeah, I checked the same document.
What you wrote above is standard GROUP BY stuff and, unfortunately, your query, after I corrected the syntax, does not work:
SELECT YEAR(date_first_received) as yr, count(year(date_first_received)) as cnt from vendors group by yr order by date_first_received DESC
[FileMaker][FileMaker JDBC] FQL0007/(2:10): The column named "yr" does not exist in any table in the column reference's scope.
Hopefully, somebody from FM will jump in here now...
P.S. Check out the Free Eclipse IDE and its database plug-in: http://www.sqlexplorer.org/screenshots.php
People from FM do not regularly monitor this thread. I suggest posting this as a possible bug over in Report an Issue. You can post a link to here to save retyping everything.
Done. Thanks Phil.
Let me know if you check out the Eclipse data console. I haven't used that particular one, but I think you'll find for a lot of ad-hoc queries, having a facility like that is hard to beat. For application work, the facility inside FMP is "OK", but the Data Viewer is so weak overall -- my least favorite part of the entire product -- due in part to the tiny small, non-resizable, output window at the bottom that makes serious work much more difficult.
Since FM distributes the JDBC "Jar" file, all you need to do is add it to Eclipse. and then create a connection to the open FMP database (after allowing JDBC/ODBC, of course, in the FMP app). It's super easy. Let me know if you have any questions. Unlike ODBC, no up front configuration is necessary on the machine to connect to the DB (using JDBC).
Check it out. :)