What is the value of the allowedSiteList variable? Formatting is important in these lists.
Is. AllowedSitesIn a field or a variable? If its a variable the $ is missing, if its a field then it probably needs a fully qualified field name.
If you have advanced you should be able to check the value in the data viewer.
The allowedSitesList is generated in the same LET calc as the main ExecuteSQL. It's an ExecuteSQL that grabs a RETURN delimited list of allowed sites that is then parsed to an "IN" list format:
allowedSitesList = ExecuteSQL...etc
Then the main query and so on
'Site A', 'Site B', 'Site C' and so on. So the "IN" list evaluates in the SQL as ... REPORTS_SITE IN ( 'Site A', 'Site B', 'Site C' ... etc )
I've tested this in the Data Viewer and it all seems to evaluate properly.
It's gotta be in the evaluation of the allowedSitesList parameter to the "?" in the SQL statement; the results suggest that nothing is there, hence a full list returned if "NOT IN".
But I can't see any syntax error. Pieced out in the Data Viewer everything evaluates properly.
You have to do like this:
ExecuteSQL ( "SELECT REPORTS_TIME + ' / ' + REPORTS_SITE + ' / ' + REPORTS_TITLE + ' / ' + REPORTS_PATIENT_NAMESTYLE_FML_CALC FROM REPORTS WHERE REPORTS_DATE = ? AND REPORTS_SITE IN ( " & allowedSitesList & " )" ; "" ; "" ; $dateSelected )
For whatever reason, FMP doesn't like the IN lists in a ? clause.
That was it. Thank you Christopher, did not think of putting it inline.