The off topic posts--which contained links that might have installed Malware, have been suspended.
Can you copy and paste the exact expression that you are using that results in a ?
(also make sure that your field is wide enough to display the result as that can also result in a ?)
And you might find this custom function helpful:
//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
If you enclose a call to ExecuteSQL inside a call to this custom function in the data viewer, click Monitor and then reopen the watch expression, you'll get actual error text returned inside the Evaluation box.
OK, I was watching the field name in the Data Viewer, which showed the ?.
But I also used the exact same expression in Data Viewer but not attached to a field, and it reports the data....
test_sql is a calculation field with the calculation set to:
SELECT DISTINCT fk_employee_id FROM All_hours
; ", "; "¶¶" )
Could it be that your calculation field is not set up as an Unstored Calculation field?
I used the Custom Function, and now I don't even get the "?"
All I get is a blank. Nothing at all.
I just don't understand how that if I open Data View, and hit the + sign, and past the ExecuteSQL Instructions into the Expression Dialog box I can immediately see what I expect to see- 7 Distinct values. But when I create a new field in the table where these values originate, and paste the exact same code into its ExecuteSQL calculation, nothing happens. Is there some key piece that I am totally missing here?
Seems like that "Unstored Calculation Field" would be at the top of the list in "How to use ExecuteSQL" in big bold letters. I've searched, read, watched, and nowhere in anything that I read do I remember seeing or hearing anything about that key piece of information. Now that I know, I'll be re-reading what I've found up to this point.
Well, it's info that is not specific to ExecuteSQL.
Any stored calculation field that does not specifically reference another field does not have a "trigger" to force it to re-evaluate.
a calculation such as:
Get ( CurrentDate )
Get ( FoundCount )
will have the same limitation.
On the other hand, if you used ExecuteSQL, with the optional ? parameter and inserted a reference to a field to use as that parameter, you could have still used a stored calculation and changes to the referenced field would trigger a requery of the data--just like any other stored calculation field.
A useful resource: Go to the Filemaker Hacks website and look up a presentation by Darren Terry on "shaking the dependency tree".
http://www.filemaker.com/help/12/fmp/html/func_ref3.33.6.html This doesn't mention it.
I'm not griping at you Phil. You are very helpful.
I'm just wondering out-loud how on earth are you supposed to just know this about making sure your calculation field is set to Unstored before ExecuteSQL will work?
But as I just posted. This is not actually the case. Only if you are using ExecuteSQL in a fashion that lacks direct references to a relevant field in the same record.
This info is not in a help reference on ExecuteSQL as it applies to all stored calculation fields, not just ExecuteSQL. And yes, this is a key fact that could be better documented.
It was the Darren Terry presentation--that I got via a digFM meeting at FMI headquarters that was the "aha" moment that explained what was till then a "memorize by rote" list of "don't make this a stored calculation" situations that I had amassed over the years...