4 Replies Latest reply on Feb 23, 2015 6:16 PM by openspace

    How to refresh SQL calc fields?

    openspace

      Hello, I have the following sql inside of a calculation field. The function works correctly, but it does not refresh the data when records are added or changed. For instance this field calculates how many active members there are. When I remove or add members the result does not update.

       

      I've tried refreshing the field, but the only thing that seems to update it is to open the manage>database window. I'm running filemaker 13.

       

      SQL = ExecuteSQL ("SELECT SUM (\"Member Count\") FROM Memberships WHERE \"StatusExpiry\" = 'Active' " ; "" ; "")] ;
        • 1. Re: How to refresh SQL calc fields?
          erolst

          Set the field's storage type to “Unstored”.

           

          btw, to get a record count, you can simply use

           

          ExecuteSQL ( "

            SELECT COUNT (*)

            FROM Memberships

            WHERE \"StatusExpiry\" = 'Active'

            " ; "" ; ""

          )

          • 2. Re: How to refresh SQL calc fields?
            openspace

            Thank you! That worked perfectly.

             

            I had the field type set to global as I was trying to duplicate a field from a filemaker starter solution. I wonder why there's would work as a global field and mine would not.

             

            Field (Global): Total for invoices in fm13 invoices starter solution

             

            Let

            (
            [
            month =Company Dashboard::Current Month ;
            year =Company Dashboard::Current Year ;
            status =Status Filter ;
            decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;
            SQL =ExecuteSQL(
            "
            SELECT SUM ( a.\"Total\" )
            FROM \"Invoices\" a "  &
            If (
            IsEmpty ( status ) ;
            "WHERE a.\"Month\" = ? AND a.\"Year\" = ? AND a.\"Status Order\" IN ( 0 ,1, 2 ) " ;
            "WHERE a.\"Month\" = ? AND a.\"Year\" = ? AND a.\"Status Order\" = ? "
            )
            ; "" ; ""  ;
            month ; year ; status
            )
            ] ;

             

             

            If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 )

             

             

            )
            • 3. Re: How to refresh SQL calc fields?
              erolst

              openspace wrote:

              I wonder why there's [sic] would work as a global field and mine would not.

               

              Because their calculation references database fields in its native Filemaker part, which your calc doesn't.

               

              Is this the way the calc is formatted originally in the Starter solution? That's barely readable …

              • 4. Re: How to refresh SQL calc fields?
                openspace

                Haha, yep. straight copy and past.

                 

                Thanks for your help once again erolst. Much appreciated.