Polarpro

ExecuteSQL in FileMaker / in MSSQL

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

Hello,

 

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

Attachments

Outcomes