5 Replies Latest reply on May 19, 2012 7:02 AM by BruceHerbach

    How To: Update a number field when calc in record changes?

    mr_scott

      Hello:

       

      My question is on how to ensure that a plain number field (CountCasesResult) is displaying the result of a calculated field (CountCasesCalc ) whenever changes occur to that calculated field - automatically. I'm trying to speed up a list-based summary report that I created with the help of Matt-squared (Matt Navarre, Matt Petrowsky).

       

      I was manually running a replace command, but ended up creating a new calculated field, which is becoming a real drag.

       

      I'd like this to be automatic, like FileMaker's indexing of fields for calculation purposes.

       

      Currently, I have a self-join in the data file, along with these 3 fields:

      CountCasesCalc - count of "case id" within the chosen analysis for the selected issue

      uniqueCasesCalc - count of unique cases on a selected issue

      CountCasesResult - Just a number field to hold the result.

       

      I also tried this as auto-enter, like my HostTimeStamp, but it is not updating when the referenced calc (CountCasesCalc) changes:

       

      Let (

      ~trigger = CountCasesCalc ; Case ( uniqueCasesCalc > 0 and CountCasesResult ≠ CountCasesCalc ; CountCasesCalc ; CountCasesResult )

      )

       

      The report seems much faster with the number field result compared to the unstored calculation I have now.

       

      Does this require a trigger in the data file? Am I looking for the impossible? Any suggestions will be very welcome.

       

      Thanks in advance.

      - - Scott

        • 1. Re: How To: Update a number field when calc in record changes?
          BruceHerbach

          Scott,

           

          An Auto-enter calc can be trigger by any field in the table/record. For best reliability make sure that the check box,  "Do not evaluate if all fields are empty"is cleared,  and the "Do not replace existing value" check box is cleared.

           

          So if the data is coming from the other side of a relationship,  you have to come up with a trigger field and have some mechanism to change the value of the trigger field.  This can be a step in a script that kicked ouf by a script trigger or part of a new record script.  If there is a single record the set field fir the trigger field will do it,  If it is a set of fields the replace field contents can do the whole set, as long as they are not locked by another user or another window.

           

          HTH
          Bruce

          • 2. Re: How To: Update a number field when calc in record changes?
            mr_scott

            Hi, Bruce:

             

            I know I don't have the "Do not replace…"checkbox selected (checked), and the "Do not evaluate…" aspect in the calculation dialog is also unchecked.

             

            I have a field that is unstored called "CountCasesResultCalc" that does what I want, but I cannot make it, nor the related fields "indexed" — so they are both "unstored" as well.

             

            I have a "Let" statement that I set to the field I want to replace, "CountCasesResultCalc", but also the field "CountCasesCalc" — but I get no data in the Number field set with the following "Auto-Enter" calculation:

             

            Let (

            ~trigger = CountCasesResultCalc ;

             

             

            Case ( uniqueCasesCalc > 0 and CountCasesResult ≠ CountCasesResultCalc ; CountCasesResultCalc )

            or

            Case ( uniqueCasesCalc = 0 and CountCasesCalc ≠ 0 ; 0 )

            )

             

            Is it possible that this probelm is due to fields in preceding calcs that are "unstored"?

             

            Thanks for responding. If you follow-up, please let me know what you see as being wrong or incorrect.

             

            Best regards,

            - - Scott

            • 3. Re: How To: Update a number field when calc in record changes?
              BruceHerbach

              Hi Scott,

               

              I think you are correct,  it is the unstored calc.  An unstored calc only

              updates when it is displayed on a layout.

               

              Is there a script that adds a new record?  If so create numerical field in

              the current table, use this field as the trigger field and the formula in

              the field do the count.

               

              HTH

              Bruce

               

              Sent from my mobile device... Please excuse typos.

              • 4. Re: How To: Update a number field when calc in record changes?
                BruceHerbach

                Hi Scott

                 

                I believe you are correct about the unstored calc.  They only update when displayed on a layout.

                 

                Is there a script that creates a new record?  If so create a numerical trigger field in the main table and have the script change the value.  Then have the formula do the count as a stored calc.

                 

                HTH

                Bruce

                • 5. Re: How To: Update a number field when calc in record changes?
                  BruceHerbach

                  Apologies for the double post.  Emailed it, but didn't see it so rewrote it.   Then it showed up.

                  Bruce