7 Replies Latest reply on Jan 5, 2015 12:45 AM by mbeck65

    ExecuteSQL inside calculation fields

    mbeck65

      Hello everybody,

      I'm not able to use the ExecuteSQL function inside a calculation field.

      I mean, if I define a calculation field which is the result of an ExecuteSQL function the field is evaluated only the first time when a new record is created and the value is not refreshed if the data involved in the SQL statement inside ExecuteSQL change...

      Am I doing something wrong?

      Thanks a lot for your answer/suggestions/ideas.

       

      Max

        • 1. Re: ExecuteSQL inside calculation fields
          mtwalker

          Do you have the calculation options set to unstored?

          • 2. Re: ExecuteSQL inside calculation fields
            brianc

            Go to Manage > Database, select your calc field, click 'Options...', at the bottom right of the calculation window, click 'Storage Options...' ; Check the box: 'Do not store calculation results -- recalculate when needed'.

            This will cause your calc field to be evaluated upon entering browse mode or navigating between records on any layout where it is visible. If it is not visible it will be evaluated when a script attempts to reference it.

            • 3. Re: ExecuteSQL inside calculation fields
              wimdecorte

              I would highly discourage using ExecuteSQL() in a field calculation if you tables with many records.

               

              As I have shown in my Devcon presentation: there is a very real and very painful performance hit if the user has an open record in the target table.  See screenshot of my demo file.

              Even doing a simple select with an open record carries a huge penalty.  Whereas when the user does not have an open record, performance is blazingly fast (2-5ms).  Versus 8,000ms if the table has around 60,000 records AND the user has an open record.

               

              2015-01-02_12-50-33.png

               

              Bottom line: in order to guarantee good performance you need to have control over the open records.  And you can't with calculated fields.  You don't know when the calc will fire.

               

              For the same reason I stopped using ExecuteSQL() in visibility calcs, conditional formats,...

              basically anywhere but scripts because you need to get as much control over the commit state as you can to preserve good performance.

              • 4. Re: ExecuteSQL inside calculation fields
                Mike_Mitchell

                Good information, Wim. Thanks.

                • 5. Re: ExecuteSQL inside calculation fields
                  steve_ssh

                  Mike_Mitchell wrote:

                   

                  Good information, Wim. Thanks.

                  +1

                   

                  Thank you.

                  • 6. Re: ExecuteSQL inside calculation fields
                    rrrichie

                    Filemaker has in some there sample solutions a little "trick" it uses globals to set an initial value on new records.

                    Then in a "new record" script for example first fill the globals, with that execute sql for example.  Then create the record.

                     

                    This wouldn't handle the changing of the values after the record is created though, but since you are talking about how it doesn't update in a script, I'm sure the script itself could do the executeSQL?

                    • 7. Re: ExecuteSQL inside calculation fields
                      mbeck65

                      Thanks Wim for this information!

                      Actually the environment that you describe is different from my personal case, but I'll be very careful not to run into this type of situation.

                       

                      Max