2 Replies Latest reply on May 17, 2012 11:15 AM by DanielShanahan

    ExecuteSQL Discovery

    DanielShanahan

      After a bit of trial and error, I discovered that I can grab the contents of a global field and use it with an SQL wildcard (%) and LIKE:

       

      ExecuteSQL ( " SELECT name FROM party WHERE name LIKE ? " ; "" ; "" ; "%" & ROLE::partySearch_g & "%" )

       

      I am enjoying this new function!

        • 1. Re: ExecuteSQL Discovery
          TomHays

          That is a very nice example of using LIKE.

           

          As you embrace using SQL more and more in your workflow, you will also want to take care to guard against your queries breaking when you change the names of fields or tables.

           

          While ROLE::partySearch in your calculation will automatically change when you rename it, you can get the same insurance for "name" and "party" by doing something like the following.

           

          Let(

          [

          sqlField_name = Substitute(GetFieldName(party::name);"::";".");

          sqlTable_party = GetValue(Substitute(GetFieldName(party::name);"::";"¶");1)

          ];

          ExecuteSQL("SELECT " & sqlField_name & " FROM " & sqlTable_party &" WHERE " & sqlField_name & " LIKE ? " ; "" ; "" ; "%" & ROLE::partySearch_g & "%" )

          )

           

          The value for sqlField_name becomes "party.name" which FileMaker's SQL is happy with.

          You could also use the more elaborate

          sqlTable_name = GetValue(Substitute(GetFieldName(party::name);"::";"¶");2)

          to just yield "name" in the same way sqlTable_party gets just "party".

           

          Note that the choice of field in the table "party" used to calculate sqlTable_party is arbitrary. Only the left hand side of "party::name" is used.

           

          -Tom

          • 2. Re: ExecuteSQL Discovery
            DanielShanahan

            Thanks Tom.  Great advice!  I often use GFN when passing script parameters but it never occurred to me to use it with the ExecuteSQL function.  Thanks for taking the time to provide examples.