2 Replies Latest reply on Apr 2, 2015 12:19 PM by NicholasWelander

    Refreshing a calculation field with SQL



      Refreshing a calculation field with SQL


      I have a calculation field that uses ExecuteSQL to determine its value. The SQL finds a specific field and the calculation field is the result. I've tested the SQL in data viewer and it works. Also, when I set or change the calculation and save the change, it reflects the correct value. However, when I change the referenced field from 1 to "" or back, the change does not show. The calculation is unstored and the result is a number.

      Would an auto-enter work better? Is there another solution? Worst case, I can script the checkbox to manually change the field and any other fields, but I'd rather not.

        • 1. Re: Refreshing a calculation field with SQL

          auto-enter would be worse. Scripting a refresh object or refresh window step might be needed, but first make sure that you've committed the data change back to the database. As an initial test, after changing the value in the field, click an open area of the layout to commit the change and see if the SQL field updates.

          You also might try this trick:

          ExecuteSQL ("SELECT .... " ; field separator ; record separator ; YourTable::YourFieldhere )

          From what I understand about FileMaker's dependency tree, including an explicit reference to a field outside of the quoted SQL string should force the query to re-evaluate each time the value of YourTable::yourFieldHere is modified (and committed).

          • 2. Re: Refreshing a calculation field with SQL

            I had previously wondered, given that my field was only referenced inside my SQL statement, if a change wouldn't trigger a recalculation. I was also going to ask if adding an extra argument after my other arguments would cause any issues, but it seems that it doesn't. So yes, the explicit reference is what I needed to trigger the calculation and, yes, it does require a commit records afterwards. Thank you.