3 Replies Latest reply on Dec 13, 2014 10:29 AM by philmodjunk

    Quick ExecuteSQL questions: global fields and refresh



      Quick ExecuteSQL questions: global fields and refresh



      I have the following calculation field set as a global field in a solution:

      ExecuteSQL ( "
        SELECT \"Content Vocabulary_Academic Language\", COUNT ( * )
        FROM \"eval form\"
        WHERE Phase = \"_g_Phase__gxt\"
        GROUP BY \"Content Vocabulary_Academic Language\"
        " ; "    " ; ""


      The problem is, it works fine as a non-global field but fails ("?") when I set it to global storage. (I want the results to be seen in all records.) What do I need to do to make it work?

      Also, the results don't update when I use the Refresh Window script step (on record load, layout load, and manually fired)--it only refreshes when I perform a Find. What do I need to tweak to get that to work, too?


      Thanks in advance for for your help!

        • 1. Re: Quick ExecuteSQL questions: global fields and refresh

          Is your calculation set to be unstored? If not, unselect the "store calculation results" checkbox and I believe that should solve your problems.

          Global calculation fields are very tricky to use, I personally don't fully understand them myself. All I know is that they never seem to behave the way I would expect them to.

          The way Filemaker calculations choose to re-evaluate is a bit tricky, I attended a session at the last Filemaker Developer Conference by a man named Darren Terry who did a great job explaining this very subject. I did a quick google search and came across an article he wrote about it. I recommend reading it http://filemakerhacks.com/2014/08/08/shaking-the-dependency-tree/

          • 2. Re: Quick ExecuteSQL questions: global fields and refresh

            Thanks for the input and link, Evan--I appreciate it.

            Yes, as a global field it's natively unstored (as far as I can tell). It just seems that other developers have had great success with using SQL so I must be missing something lacking in my education.

            • 3. Re: Quick ExecuteSQL questions: global fields and refresh

              The issue is the "funky rules" that global calculation fields use to control when they refresh. I find it confusing too and usually avoid using them. You might have better luck using your query as the second parameter of a set field step and then use a script to assign the query results each case where you are now trying to use Refresh Window.

              To Quote Darren Terry (I can can recall his recent visit to DIGfm at FileMaker Inc Headquarters correctly), A stored calculation field will update each time a field in the same records referenced by a calculation is modified.

              My take on ExecuteSQL then is that such an expression doesn't refer to any fields except any referenced in the option parameter list (you have none). But global calcs have their own rules that deal with evaluating when the most recent record with fields referenced by the calc change and since there are no such fields in your calc, I'm not finding any path that will make them evaluate.

              My suggestion is to remove the global storage option, but specify that the calc be unstored. That should evaluate consistently for you.