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"
ExecuteSQL ( "
SELECT COUNT (*)
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:
WHERE EmailGroup = ?
" ; "" ; "" ; "FreeTicket"
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%" )
It really is a preference thing To my eyes, erolst's code is much clearer than yours.
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.
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.
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:
Is one method better than another, NO!! Take what you want and ignore the rest.
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>
I use the same method in my Let() statements:
) // comment here
It makes it easy to see the missing separators. But that's me.
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.
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.
Retrieving data ...