I am having difficulties with an ExecuteSQL statement that works perfectly in a FileMaker only solution but does not seem to work when I transfer it to a solution that also uses ESS.
I attached a simple fmp12 example file to this message.
There is a table Projects and a table Expenses, one project can have many expenses.
In FileMaker you can easily find out the total expenses of a project using a relationship; in this case I do not use a relationship, but a calculation field that uses an ExecuteSQL statement:
ExecuteSQL ( "
SELECT SUM ( S.Expenses )
FROM Projects P
JOIN Expenses S ON P.ProjectID = S.ProjectID
WHERE S.ProjectID = ?
" ; "" ; "" ; Projects::ProjectID
In the attached example file, this works perfectly, but when I try to use this in a solution where the Expenses table is an ESS (belonging to an MSSQL solution), I only get ? as a result. The Projects table is a FileMaker table.
I'd be grateful for a hint.