SQL doesn't like fields that start with an underscore. They need to be enclosed in double quotes.
WHERE (Route='G' or Route= 'I') AND \"__ID\"=?"
Spot on! Thank you.
a. Albeit when I have better SQL understanding, but is it generally better to use this rather than TOs to achieve desired relationships, just keeping the primary relationships?
b. If I need several or nested ANDs / ORs following a WHERE, i) does SQL follow normal mathematical hierarchy and ii) if there say 2 ANDs would you write them like this: ExecuteSQL("SELECT etc. ......WHERE field1='K' AND fieldX=? AND fieldY=?";"";"";Table2::fieldX;Table3::fieldY)
a) ExecuteSQL cannot replace all the relationships in Manage | Database, there are database functions that depend on those relationships that cannot be implemented without them, but you can use ExecuteSQL to greatly reduce the complexity of that relationship chart. I have mixed opinions as to whether that is a good thing or not. True, reducing the complexity of the relationship graph is generally a good thing, but your executeSQL calculations "hide" relationship details in small scattered packets throughout your database and that isn't necessarily a good thing...
b) yes and if you have constructed such multi-term Boolean expressions in FileMaker calculations before, you should have a good idea how to set them up. While the symbols used are slightly different, the order of operations is the same.