Could you post a copy of your solution or a subset of it that is not proprietary? Difficult to see what's going on by your query alone.
You might try a third party tool like RazorSQL and connect to your live FMP solution. RazorSQL (and similar tools) give you ACTUAL ERROR MESSAGES (not the unhelpful "?") and even SQL code assist. Using that tool or a tool like it, I find query formulation and debugging go much faster.
These external tools also use regular SQL so you can skip the ExecuteSQL syntax until you get the query working. Then just go back to FMP and add your query back there, and update the replaceable parameters and such.
it seems that the extension should be contained in quotes.
I've never been able to get the queries to work right with quotes in FileMaker. I've always had to make them sans quotes or they don't work.
hmm, but doesn't the dot, as in .wav make wav look like a field name without a table?
If you could isolate the sql in a variable and view that variable, it might be helpful.
the column extension in the query, if a reserved word can be escape quoted:
I don't think that is the problem, but it cannot hurt to do this.
if the value passed as a parameter is in the field (and concatenated is ok here) then the ExecuteSQL will apply the correct single quote to the text value before the query is evaluated.
WHERE extension = '.tiff'
I've rewritten the query like this and used the $vars so that this can be monitored in Data Viewer, if you have it or passing the $var values to a custom dialog if not.
[ $query = "
WHERE \"extension\" = ?
FETCH FIRST ROW ONLY
" // query to be made
; $result = ExecuteSQL (
$query ; "" ; ""
; Lower ( "." & nCaseDocumentVersions::file_extension )
) // query called with one parameter
]; If ( $result = "?" ; ""
; ["|location|" ; nCaseDocumentVersions::file_path & nCaseDocumentVersions::file_name]
; [Char(13) ; ""]
) // end Subs to be run after the query result is returned
) // end if
) // end LET
the use of Lower() in passing the parameter should match EXACTLY what you have passed.
xls => xls or XLS, but not xlsx or XLSX (from the field)
xlsx => xlsx or XLSX (from the field)
there should be only one result returned IF you have ONLY one record with the extension in fileTypes.
My question: where are you calling the query: from "nCaseDocuments" or "nCaseDocumentVersions" ?
is this scripted, auto-enter (field definition) or ???
The "." is added because it is in the second table and not in the first. Its for file check on uploading.
I am performing the query from nCaseDocuments and uses through the relation to version.
David, are you thinking, possibly, because the value/contents of the field 'extension' has
that SQL will confuse it with a column name (without the table)?
I know the '.' is an operator/wild card for regEx, but not in FileMaker symbols/operators, so that should also not be a problem (and the query is not the standard FIND)
sreese, are you sure there is ONE-to-ONE relationship for a single value to come over from nCaseDocumentVersion? might there possibly be more than one related record? put the data into a portal with more than one row to see. or GTRR (go to related records) from nCaseDocuments to verify
or don't perform the query if
Count ( nCaseDocumentVersion::id ) ≠ 1
it's definitely not a problem if FM encases the variable ? with single-quotes. That's a cool feature.
Its a one to many relationship that only returns the newest record.
sort (descending) in the relationship graph? (we cannot see the relationship dialog, to verify)
Thank you. Then it SHOULD pull the correct information. I might still make the call from the related version table and view it in the parent table. Then there is sure to be one result.