8 Replies Latest reply on Aug 11, 2010 10:07 AM by philmodjunk

    Recalculating down a column

    Æthernaut

      Title

      Recalculating down a column

      Post

      Hello,

      I have a tabular database in which a column, "InspectionHrs", is a sum based upon other column values as well as the inspectionHrs from the day before (i.e. the row above).

      I've noted that if I change the value of a single record, it won't recalculate the fields of the records below it.  Does anybody know how I can induce recalculating on other records?

      Thanks for the help.

        • 1. Re: Recalculating down a column
          philmodjunk

          You haven't indicated how your calculation field is defined. Is it a stored or unstored calculation field? A number field with an auto-entered calculation?

          Personally, I prefer to use a Summary field, with the "Total of" and "Running Total" options selected for this type of thing. This type of field will automatically update as you find different records, choose different sort orders, create and delete records...

          • 2. Re: Recalculating down a column
            Æthernaut

            InspectionHrs is an auto-entered calculation that is not stored (couldn't even if I wanted to, which I just might later). And yet it exceeds the running total capabilities of the Summary field options because it is reset to zero whenever InspectionDone is yes for the day.

            • 3. Re: Recalculating down a column
              philmodjunk

              InspectionHrs is an auto-entered calculation that is not stored

              That's a contradictory statement. Auto-entered calculations are always stored (though indexing can be turned off.) And it won't update when referenced values come from other records or tables as you've discovered.

              Try making this a calculation field instead of an auto-entered calculation, click the storage options and make it an unstored calculation.

              It may still be possible to use a summary field if you can group your records by a common value such as a Job Number or date and then use the Restart Summary for Each Sorted Groupoption.

              • 4. Re: Recalculating down a column
                Æthernaut

                Correction, it isn't auto-entered anymore: I had already converted it into a non-stored calculation field as one of my previous attempts to fix this problem.

                I'll play with the summary field suggestion and see if that works, but I don't understand how just yet since each date (i.e. day) is unique.

                • 5. Re: Recalculating down a column
                  philmodjunk

                  I'm suprised it doesn't update then. You might need to try a script with Refresh Window to see if that forces an update. You might post the actual calculation expression here. That might give someone an idea as to what is going on here...

                  • 6. Re: Recalculating down a column
                    philmodjunk

                    Using this sample expression, I've been able to see the values update automatically when I delete a record:

                    A + B + If ( Get ( RecordNumber ) > 1 ; GetNthRecord ( cRunningTotal ; Get ( RecordNumber ) - 1 ) ; 0 )

                    The field is defined is cRunningTotal

                    • 7. Re: Recalculating down a column
                      Æthernaut

                      Here is the calculation for Inspctn.Hrs (which I have previously been calling InspectionHrs in this thread):

                      If ( Inspctn.Done?="Y" ; 0 ; 

                      If ( Get ( RecordNumber ) =1 ;

                      Hour ( Flight.HrsfortheDay ) + Hour ( Inspctn.Hrs.Prior );

                      GetNthRecord ( Inspctn.Hrs ; Get ( RecordNumber ) -1 ) +Hour ( Flight.HrsfortheDay ) ) )

                      I think you may be on the right track regarding the refresh window script because when I minimize/unminimize the window. the values in later records have been modified.  How might I do that?

                      • 8. Re: Recalculating down a column
                        philmodjunk

                        There are two script steps you can play with: Commit Record and Refresh Window.

                        You can experiment with script triggers to perform scripts with one or the other of these steps whenever one of these fields is exited or modified (best option depends on field format). For deleting a record, you could put a button for deleting the record that uses a script to delete the record and include whichever of the above two steps work for you to get the field to refresh.