The first thing that comes to mind is there is the calculated field have a value at the time of the executesql statement. I guess what I am suggesting is playing with the "Do not evalute if all referenced fields are empty" checkbox and the Indexing option of "Do not store calculation results - recalculate when needed" checkbox. Not that I have used this with executesql but they have tripped me up more than a few times. Maybe changing your settings on the used calculated field will help. Just my 2 cents.
Travis, IF your Surface_ID field is a Number field, do NOT quote it in the list:
4657 ; "Plow Lot" ; "11/12"
Let the ExecuteSQL take care of the quoting.
When it's run the WHERE will become:
WHERE Surface_ID = 4657 AND Service_Description = 'Plow Lot' AND Season = '11/12'
1 of 1 people found this helpful
Did you make the calculation unstored? The ExecuteSQL() function is a lot like the Get ( CurrentDate ) in that there isn't anything in it to trigger the calculation engine to recalculate.
I suggest you use a text field with an AEC (Auto Enter Calculation) that you can trigger when required. Possibly using a Modification TimeStamp field. ExecuteSQL() can slow things down so you don't really want it recalculating a lot. As you get more and more records the function will take longer and longer to process. If you show the calculation in the list or table views it will process a lot causing real slow response.
You might try using SeedCode SQLExplorer to work this out. I have found it an excellent tool for this. You can add Tables from your DB to the file with the same name, develop the query and then copy it to the clipboard. It has a function that gves you a working version of the ExecuteSQL statement that you can past directly into your solution.
That was it exactly; as soon as I turned on 'Do not store', the calculated field started working properly.
Thanks for the suggestion; that's exactly why I made those three options into parameters, though, instead of including them directly in the query. If I correctly understood the excellent discussion at FilemakerHacks (http://www.filemakerhacks.com/?p=4677), turning them into parameters lets the Filemaker engine take care of most (if not all) of those little gotchas.
After turning those parameters into the actual field/variables, BTW (the other main reason for doing them as parameters), still works fine.
Yeah, as I said above, unstoring the calculation turned out to be the issue.
Thanks for the suggestion on making it an auto-enter calc; in all honesty, though, that's probably going to have to wait. I'm trying to insert this into a layout done by another developer that has a really hairy tangle of relationships, TO's and scripting - the reason I wanted to use ExecuteSQL in the first place, to try and avoid muddying the waters further - and until I have a chance to clean up the tangle, I'm not confident in being able to get it to trigger reliably.
Yeah, SQLExplorer is a nice tool, and I used it to try and figure things out; the problem was that the SQL code it was coming up with didn't appear to be any different than what I'd devised on my own, and it had the same problems when used in the calc field. Still, something to look at in the future if I run into more trouble!