3 Replies Latest reply on Feb 11, 2009 3:19 PM by raybaudi

    forcing a calculation update

    CountryBoy1

      Title

      forcing a calculation update

      Post

          Have a calculation that involves several related fields from another table.  When

       changing any of the related fields, the calculation will not update.  However, if the

       (key) Index value of the table with the calculation is re-entered, then the calculation
       will update.  (The calculation is inside a case statement where the case value is

       not likely to change, but the parameters inside the case can often be changed.)

       

         Is there any way to always get the calculation updated when one of its related
       fields (from another table) is changed ?  Thanks for your help.

       

       

       

        • 1. Re: forcing a calculation update
          TSGal

          CountryBoy1:

           

          Thank you for your post.

           

          I am having difficulty duplicating this problem.  This is what I have done.

           

          1. I created a table (FIRST) with one field - Key (Text)

           

          2. I created a second table (SECOND) with two fields:

           

          Key (Text)

          Text (Text)

           

          3. I created a relationship between FIRST and SECOND based upon the field "Key".

           

          4. I entered five records into SECOND

           

          Key - Text

          1 - Blue

          1 - Green

          1 - Indigo

          2 - Red

          2 - Yellow

           

          5. I created a calculation field (Calc) in FIRST with the following formula:

           

          List (SECOND::Text)

           

          6. I go into Browse Mode for FIRST, add one record and enter "1" into Key.  In Calc, I get:

           

          Blue

          Green

          Indigo

           

          7. I switch back to SECOND, and edit the third record an change "Indigo" to "Purple"

           

          8. I switch back to FIRST, and Calc now shows:

           

          Blue

          Green

          Purple

           

          ----------

           

          Please provide additional information so I can try to duplicate the problem.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: forcing a calculation update
            CountryBoy1
              

            Dear TSGal

                 I am not familiary with the List function. From your example, it appears that the
            List function keeps up with changes in the Second table.  In my case, the calculations
            are in Second.   There are many Case statements in the calculation.  When a new

            record is created for Second, the calculation looks at a field in First to see which Case

            to execute. This field defines a certain "Period of Time". The Cases in the calculation also use Month, Day and Year fields in First in order to complete the calculation.

                 My problem is that if I change a Month, Day or Year field in First, it may NOT

            necessarily cause the "Period of Time" to change, and the script will not
            re-execute to make a new calculation.  What I really needs is for some kind of
            "event" to be generated that will force a new calculation whenever Month, Day or
            Year are changed.  Right now, the only event that causes the calculation in Second
            to execute, is, of course, to create a new record.  This is fine unless the user has

            entered a wrong date in First.  If they change it, the calculation in Second will not
            re-execute.

                 I think what I will do is just go ahead and make the calculation in First and

            then, to display it in Second, show it as a related field.  That way, any change for

            Month, Day, Year will automatically be recalculated in First and the new value will

            automatically be displayed in Second.

                 Let me know what you think.

                          Sincerely, CountryBoy1

             

             


            • 3. Re: forcing a calculation update
              raybaudi
                

              CountryBoy1 wrote:

               

              My problem is that if I change a Month, Day or Year field in First, it may NOT

              necessarily cause the "Period of Time" to change, and the script will not
              re-execute to make a new calculation.

               


              Remember that there is a nice function: Evaluate !

               

              Something like:

               

              Evaluate ( Period ; [ Month ; Day ; Year ] )