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" )
1 of 1 people found this helpful
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" )
Try with 'John' - within single quotes:
ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = 'John'" ; NULL ; "¶" )
220 Gaines Oak Way
Suwanee, GA 30024
Voice: (678) 313-5604
FileMaker Certified Developer
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,
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
thanks guys...case sensitive....aaah...now it all makes sense
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.