2 Replies Latest reply on Apr 25, 2014 10:01 AM by c.wagner1

    Learning ExecuteSQL in FMP12, a few questions



      Learning ExecuteSQL in FMP12, a few questions


           Hello all,

           I have joined a new job where I am required to use FileMaker (and gradually transition systems to other databases). I have been a DB Admin of a MS SQL Server database for ~2 years, and I am very well versed in PL/SQL and T-SQL. I am trying to pan my SQL knowledge to FMP using the ExecuteSQL functionaloty, and I'm kinda running into a lot of small pains :)

           I have 2 tables: Movies and Genres. The relevant columns are:

      Movies(MovieId, MovieName, GenreId, Rating)
      Genres(GenreId, GenreName)

           I'm trying to find the movie with the highest rating in each genre. The SQL query for this would be:

      SELECT M.MovieName
          FROM Movies M INNER JOIN Genres G ON M.GenreId=G.GenreId
          WHERE M.Rating=
               SELECT MAX(Rating) FROM Movies WHERE GenreId = M.GenreId


           I translated this as best as I could to an ExecuteSQL query:

      ExecuteSQL ("
      SELECT M::MovieName FROM Movies M INNER JOIN Genres G ON M::GenreId=G::GenreId
      WHERE M::Rating =
      (SELECT MAX(M2::Rating) FROM Movies M2 WHERE M2::GenreId = M::GenreId)
      "; "" ; "")


           I set the field type to Text and also ensured values are not stored. But all I see are '?' marks. 


           What am I doing incorrectly here? I'm sorry if it's something really stupid, but I'm new to FMP and any suggestions would be appreciated. 


           Thank you!





        • 1. Re: Learning ExecuteSQL in FMP12, a few questions

               Compared to how you use SQL in other DBMS, what you can do with ExecuteSQL will be very limited.

               A ? is the Exedrine Headache #1 result that you get with a syntax error.

               The obvious one that I can spot is that the qualified reference of Table::FieldName you used is correct syntax for a FileMaker calculation, but not a SQL query. It would be Table.Fieldname instead of Table::fieldName

               But you could very easily get this value without any SQL at all.

               Assuming this relationship:


               Genres::GenreID = Movies::GenreID

               You can open Manage | Database | Relationships, double click the relationship line and specify a sort order for Movies that sorts the related records by Rating in descending order.

               Then you can go the Genres layout and add any fields from Movies that you want and you will see the Movie of that Genre that has the highest rating.

               You could also leave your relationship unmodified and put a one row portal to Movies on your Genre layout and specify a sort order for the portal that lists the highest rated movie first and you'd get the same result.

          • 2. Re: Learning ExecuteSQL in FMP12, a few questions

                 You should use a "." not a "::".  So your formula should be something like:

            SELECT M.MovieName FROM Movies M INNER JOIN Genres G ON M.Genreid = G.GenreID ... etc


                 The "::" notation is only used in FileMakers calculation engine to identify specific fields.  The format is TableName::FieldName.  When you are working with the ExecuteSQL function anything in the first set of quotes is evaluated by the SQL engine and not the calculation engine.  The main difference is it does not get any error checking.  Items in the outer set of quotes are evaluated by the calculation engine so they need to follow Filemakers naming conventions.