4 Replies Latest reply on Dec 2, 2010 11:01 AM by KiwiKim25

    Calculation on a running total



      Calculation on a running total


      I'm doing a fairly basic program that has a few calculations.  I have half of it down pat but need some help on the rest.

      Here's what I have:  We are looking at video of a rat.  When the rat enters sideA, and when it leaves sideA, we write those times down (Example 0:05:15 to 0:08:10).  These are organized by day, but there are multiple videos per day. 

      I have table 1 set up for basic information (date, time etc).  There is a portal to table 2. This includes 2 columns for "Time in" and "Time out".  Next to that I have a column that subtracts the two to give us the Time Spent in SideA.  This all works great.

      In another portal to table 2  I have 2 columns.  Video ID Number and Video Length.  We're all good there.

      Here is where I hit my snags.  My final portal to table 2 has 2 columns. 

      Total Time in SideA, with the calculation   Sum ( Time Spent In SideA)

      Percentage of Time in SideA, with the calculation   Sum ( Time Spent In Side Instance ) / Total Video Time Length

      I also have a field that is not showing: Total Video Time, with the calculation   Sum ( Video Time Length )  

      My problem is in the last portal with 2 columns, I actually want just one box with a running total of the Total Video time, Time in SideA and Percentage of Time in SideA.  What I'm getting is each time I enter data in the other entry places, this column makes a new line too (so it shows multiple field entries).  How do I fix this so it's just one box with my final calculations?

      I tried changing the portal for those 2 columns to 1 line, but it doesn't update the information when I enter new data in the first 2 portals.  What am I doing wrong?

      Here is a screen shot of what it looks like.  The bottom right columns are the ones I'm trying to fix (the numbers/times are random ones I typed to see if it worked).


        • 1. Re: Calculation on a running total

          I see two ways to do what you want, but a few details bother me and I'd like clarification on those points first to avoid confusion when you try to implement one of these suggested solutions:

          Sum ( Time Spent In SideA)
          Sum ( Time Spent In Side Instance ) / Total Video Time Length
          Sum ( Video Time Length ) 

          Are these exactly what you are using here?

          Sum has two basic formats:

          Sum ( field1 ; field2 ; field3 ) which is the same as just using field1 + field2 + field3

          Sum ( relatedTable::Field1 ) which computes the sum of Field1 from all related records in the specified relatedTable's table occurrence.

          Sum ( field1 ) will produce the same result as just entering Field1 in the specify calculation dialog.

          And what kind of field is Total Video Time Length? A summary field?

          Your two possible solutions:

          1. Define calculations in the layout's table (not the portal) that use the Sum function to compute your portal totals.
          2. Define summary fields that compute these totals in the portal table and change that last portal to be a one row portal to display either these summary fields or calculations that combine the values of several of these summary fields.

          With option 2, you may also need a triggered sript to refresh your window each time you save or exit one of the Time fields in the first total to make sure that these totals update promptly.

          • 2. Re: Calculation on a running total

            Hmmm I guess this means I misunderstood the Sum function.  I copied/pasted the formulas to my posting, so that's just how I had it. 

            My goals were this:  Add each ENTRY together for the Time Spent field (they spent 8 hours and 34 minutes that day in that part of the cage etc).  But just for this one day/record. Not all the records together (although I will need to do that too at some point in time).   This was for the "Total Time In Social" field. 

            Once that works, divide that by the sum of all the Video Length entries, to give me Percent of Time spent in SideA.

            Is there something else I should be using other than Sum?  I've been using The Missing Manual to help me, but I'm still feeling my way around.

            Thank you for all your help.  You are a life saver!!!


            • 3. Re: Calculation on a running total

              I gave you two options in my last post.

              1. The first uses Sum from the parent record (The table your layout lists in Show Records From in Layout Setup...)
              2. The second uses summary fields to produce the same result.
              • 4. Re: Calculation on a running total

                I've got it now!  Thank you very much!