ExecuteSQL ( "select sum(Amount) from Budget where ? = fkCategoryID and Month < ?"; "" ; "" ; Budget Categories::pkID;GlobalFields::Month )
"select sum(Amount) from Budget where ? = fkCategoryID and Month < ?";
I have the same format for another table and it works perfectly fine.
1. a quick test will return an error code:
EvaluationError( ExecuteSQL( .... ) )
2. if the message is desired AND you have FileMaker Pro Advanced, the calc as you have it. will return the message not the code if placed in the Data Viewer.
3. A "gotcha": 'Month' is a reserved word and must be escaped
... and \"Month\" < ? " ...
4. This is just to get it working, then you will find many articles here on keeping it from breaking when the field or table names change.
You might want to spend $99 and get a real SQL tool you can connect to your live FMP (or to any other JDBC-enabled) database. It's a lot less frustrating to just type in SQL without having to type in all the ExecuteSQL stuff. Plus, the expected "user goals" are right there (export options, live edit tables, etc.).
To be productive with SQL, you need a real SQL environment, sadly still lacking in the current version of FMP.
So, if you do, or plan to do, SQL, a real SQL environment is critical and will probably save you tons of time.
As an added plus, once you connect to your live FMP database using a tool like razor (via the free FMP JDBC driver), you can also do INSERT, UPDATE, and DELETE (not just SELECT as with ExecuteSQL).
Razor has a free eval period as well and the developer recently fixed an issue in Razor caused by FMP's non-standard dates. He's responsive and friendly.
Check out this article I wrote in App Innovations here on the forum:
Aaaaaaa thank you, Beverly! It was the 'gotcha'!
You are welcome. If in doubt, quote! table.column of SQL becomes "table"."field" in FileMaker's use within the ExecuteSQL. And... because the entire query is quoted text these get 'escaped':
" SELECT \"table\".\"field\" FROM "\table\" WHERE x = 1 "
It gets a little complex eh? That's why there are utilities that help your write the queries to be "FMP-friendly". In addition they may help you with my 4th point (prevent brittleness). If you rename a field (or table) the TEXT with hard-coded names will not automatically be updated.
Enjoy what you've got for now, and come back with additional questions as needed!
Retrieving data ...