4 Replies Latest reply on Aug 5, 2016 5:18 AM by Polarpro

    ExecuteSQL in FileMaker / in MSSQL




      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