I'm presuming that you want to get the notes for a company one company at a time, as in you already have the Company record; please correct me if I'm misinterpreting. Does this do the trick for you?
"SELECT COUNT(*) FROM Notes WHERE id_Company = ? AND Created >= ?" ;
$startDate ; // Get ( CurrentDate ) - 30, for example
I use ExecuteSQL all the time. I use it for finds, but mainly to do big processing, to fill in number fields that were formally calculations which are usually* based on relationships. (I say usually because I'm sure someone here will disagree with me on that, so i used a generic term!)
Thanks jbante, that worked a treat once I finally worked out that the Created field was actually set as a timestamp rather than date..
I found that ExecuteSQL is very slow when I want to get count of records.
I tried it few times on table with 150K of records and it takes several minutes, as oppose of FM find that returns count immediately. Any thoughts ?
I have done a couple of tests where I setup a script to time a process that uses ExecuteSQL and compares the same Process using filemaker relationships or finds to get the same information. For the most part it seems that in most cases the Standard Filemaker methods appear to be slightly faster.
I have been finding ExecuteSQL to be very useful. It does make it possible to simplify the relationship graph but it may not speed up a script. That stated, you can do some really neat things with it.
My experience with ExecuteSQL has been that you may need to experiment with different alternatives methods of getting the same result to get the most performant solution. Arranging join tables or where match conditions in a different order often leads to different performances. In one example with an outer join, the outer join with one ExecuteSQL query took in the neighborhood of 6 seconds; then I tried a separate query for each table and joined them with a custom function instead, which took about 20 milliseconds total.
ExecuteSQL may be slightly slower than performing a find on a layout we're already on, but I've found it to definitely be faster on related tables — opening off-screen utility windows and going to layouts all take time, and usually more time than the difference between an ExecuteSQL query and a Find for me. For one especially performance-sensitive script I'm working on right now that retrieves data from several tables and sets data in several tables, it took between 1.5 - 2 seconds in a semi-WAN environment. By using ExecuteSQL (no layout switching or new windows) and collecting all record updates into a single transaction, it now takes 150-350 milliseconds, fast enough that the users' reaction times (and RDP latency...) are now the bottleneck. I don't care to make an equivalent version that works through relationships instead of ExecuteSQL, so I can't say how that might compare.
There is one class of finds I've found where the ExecuteSQL method is definitely slower: partial word matches, i.e., anything using the LIKE operator. I presume ExecuteSQL does not get the benefit of FileMaker's word index, but I don't really know why.