ExecuteSQL does not work as expected
12.0.3 - Advanced
Operating system version
Mac OS/X 10.8.2
Description of the issue
As you can see at the link and sample FMP 12 database here:
When I have an ExecuteSQL tied to an "On Record Load" script trigger to display sub-totals, the field does not update correctly (at all) on the layout. (see field "Sum of Values From Layout Trigger").
The relevant Execute SQL is in the script:
/* Executed from Layout Trigger - Record Load */
ExecuteSQL ( "SELECT SUM(DATA_VALUE)
YEAR(DATA_DATE) = ?" ; "" ; Table1::YEAR_DATE )
(Another surprise (bug?) was that I could not use the 'Year' function in the GROUP BY. Didn't work at all. Although Year works in the WHERE as shown above, in the GROUP BY it gives an error. To workaround that, I had to create a calculated field with the year extracted from the date. Is this GROUP BY issue a bug or something that won't be implemented until later? How can you summarize by year if you can't group by it? Creating an intermediate calculated field to do this intermediate year calculation seems like it shouldn't be necessary with SQL. ???)
If I use this type of ExecuteSQL on a form, but not in the Record Load script step, then it works.
My goal is to create sub-totals using the ExecuteSQL as I step through the records on the main layout.
I've also tried to use a calculated field to accomplish the same thing, but that doesn't work either. See field: "Sum of Values for this year" and its associated calculation in the database.
For testing -- Using a remote JDBC connection to the database, the SQL should be working fine, but FMP's ExecuteSQL is not taking into account that the field value is changing in the replaceable parameter *as you step through the records*.
After spending many hours on this with several forum postings and conversations, it's totally unclear what is going on here, that is, why FMP's ExecuteSQL doesn't work.
Steps to reproduce the problem
Step through the records on the Table1 layout. Notice that the "Sum of Values From Layout Trigger" field is not correctly updated -- nothing displays for any particular year selected.
Note that FMP's ExecuteSQL is not taking into account that the field value is changing in the replaceable parameter *as you step through the records*.
The expected result in the "Sum of Values From Layout Trigger" field as the user clicks on each year (which may or may not be in any particular sort order) result should be as shown on the report layout. Note that the separate report layout shows the sub-totals by year correctly for the data.
The goal of the "Table1" layout, however, is to be able to step through the records and as you hit a particular year, the Table1 layout's "Sum of Values From Layout Trigger" field would display the subtotals for that user using the ExecuteSQL.
"Sum of Values From Layout Trigger" shows nothing.
Exact text of any error message(s) that appear
The only workaround, which is not really a workaround, is use a report instead, like the one I created on the other layout in this database.