ExecuteSQL in FileMaker / in MSSQL

Discussion created by Polarpro on Aug 2, 2016
Latest reply on Aug 5, 2016 by Polarpro



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.

Best regards!

:-) Mike