6 Replies Latest reply on Jul 24, 2012 8:55 PM by fmpros

    ExecuteSQL problem




      I am getting to grips with new executeSQL function and finding it very useful but i am struggling with a very basic search. I have checked reserved words and there seems to be no conflict. I have this calculation which works fine and returns the result of John


      ExecuteSQL ( "SELECT nameFirst FROM contacts WHERE id = 10002" ; NULL ; NULL )


      but when i reverse the search it fails...so below calculation returns a ? I am expecting it to return id 10002, why is this failing?...any ideas?


      ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = John" ; NULL ; NULL )

        • 1. Re: ExecuteSQL problem

          you will need single quotes around John to make the syntax work: 'john'


          If you use the parameter function you don't have to worry about it:


          ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = ?" ; NULL ; NULL ; "john" )

          • 2. Re: ExecuteSQL problem

            Hi Wim


            thanks for that...what is the reason or rule for the the single quotes. I have managed to get it to work but only returns a single id. If i want to get a list of all ids where firstname  = John it only returns 1 id when it should return two ids.


            ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = ?" ; NULL ; "¶"  ; "john" )

            1 of 1 people found this helpful
            • 3. Re: ExecuteSQL problem



              Try with 'John' - within single quotes:


              ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = 'John'" ; NULL ; "¶" )




              Binary Assist

              220 Gaines Oak Way

              Suwanee, GA 30024

              Voice: (678) 313-5604

              Internet: lhoong@binaryassist.com

              Website: www.binaryassist.com


              FileMaker Certified Developer

              • 4. Re: ExecuteSQL problem





                Unlike a native FileMaker default Find, the ExecuteSQL search is case sensitive.




                I pass this along in case (no pun intended) it might help solve your mystery. (I noticed “john” was all lowercase in your example.)




                You can always use the “Upper” or “Lower” functions to force a match, at the expense of some processing overhead.




                Peace, love & brown rice,


                Morgan Jones




                FileMaker + Web:  Design, Develop & Deploy


                Certifications: FileMaker 9, 10, 11 & 12


                Member: FileMaker Business Alliance


                One Part Harmony <http://www.onepartharmony.com/>  


                Austin, Texas • USA



                • 5. Re: ExecuteSQL problem

                  thanks guys...case sensitive....aaah...now it all makes sense

                  • 6. Re: ExecuteSQL problem



                    A primary key comparator will return the name field of the intended record but when a comparator that is so ambiguous like "John" will return all records with name of John.  Reversing the terms in this SQL significantly changes the returned record set.