10 Replies Latest reply on Sep 2, 2014 9:22 AM by taylorsharpe



      This should be simple but I am new to ExecuteSQL calculations.


      I have a field "EmailGroup"

      It is on a table called "Contacts"

      I want to know how many records contain the text "FreeTicket" in the field "EmailGroup"

        • 1. Re: ExecuteSQL



          ExecuteSQL ( "

            SELECT COUNT (*)

            FROM Contacts

            WHERE EmailGroup LIKE '%FreeTicket%'

            " ; "" ; ""


          how many records contain the text "FreeTicket" in the field "EmailGroup"

          If the field not just contains, but IS EQUAL to the value "FreeTicket", then this works faster:


          ExecuteSQL ( "

            SELECT COUNT (*)

            FROM Contacts

            WHERE EmailGroup = ?

            " ; "" ; "" ; "FreeTicket"


          • 2. Re: ExecuteSQL

            Formatting doesn't matter for functionality or performance, but I like to think the way I do it is more readable.  It is totally a preference way to do things:




            Let ( [


            SQL =  "SELECT

                             COUNT ( * )




                           EmailGroup LIKE ?" ;

            Result = ExecuteSQL ( SQL ; ¶ ; ¶ ; "%FreeTicket%" )


            ] ;





            • 3. Re: ExecuteSQL

              It really is a preference thing   To my eyes, erolst's code is much clearer than yours.



              • 4. Re: ExecuteSQL

                not agreeing or disagreeing with the "formatting" issue.


                But the use of the "%" wild card and LIKE can be a killer (stored or unstored field) in time needed. The usage here is correct when needing "contains".


                Is there a reason it needs to be ExecuteSQL?


                If it can be a calculated (auto-enter perhaps) with:

                     IF ( PatternCount ( Contacts::EmailGroup ; "Free ticket" ) ; 1 ; 0 )

                then it could be used with a found set of records, too.


                Just wondering...


                • 5. Re: ExecuteSQL

                  I guess it is just a preference thing on what you get used to and Malcolm and Erolst prefer differently.  I was just pointing out there are a lot of ways of formatting ExecuteSQL.  Why I like my method is that I often put double forward slashes at the end of each line and explain what I am doing.  I'm big on commenting even formulas when possible. 


                  I like Beverly's comment too.  ExecuteSQL is usually not any faster than normal FileMaker steps.  When it is good is when you can use it to avoid certain other script steps that do take a long time such as going to another layout to get information.  I run into some solutions where they try to use it too much when that often is not best. 

                  • 6. Re: ExecuteSQL

                    As for formatting....


                         I use erolst's method when it's ONE field, ONE table, ONE criteria, then I use taylor's method when it's MULTIPLE and a list is easy to read. I also use this:




                              , field 2

                              , field 3




                    NOTE the comma at the start of a line? I can't tell you the number of times a query failed, because of a missing comma! (FM's eSQL and the big guns SQL).

                    this makes it easy to see where I've made that mistake. I also can "comment out" any line I wanted once, but don't need now and don't want to delete altogether:




                              //, field2

                              , field3

                              , field4


                    Is one method better than another, NO!!  Take what you want and ignore the rest.



                    • 7. Re: ExecuteSQL

                      Hmmm... I never thought about beginning a line with a comma since that is not normally intuitive, but I can see how works great for things like commenting out lines.  Might have to change the way I do things!  <grin>

                      • 8. Re: ExecuteSQL

                        I use the same method in my Let() statements:


                             Let (

                                  [ exp1

                                  ; exp2

                                  ; exp3

                                  ]; result

                             ) // comment here


                        It makes it easy to see the missing separators. But that's me.

                        • 9. Re: ExecuteSQL

                          At least Filemaker syntax doesn't feature curly braces (yet) and the religious discussions that go with them …


                          Maybe let's agree that the way you format your calculations is your personal style, but that applying formatting is a common best practice.

                          • 10. Re: ExecuteSQL

                            I agree with erolst.  But I encourage that whatever style you do, that you're consistent throughout the solution.  Don't jump between formatting styles!


                            And I highly encourage people to add lots of comments in scripts and calculations to explain what you are doing.