7 Replies Latest reply on Nov 30, 2009 10:41 AM by martinpaulrice

    Running Summary

    martinpaulrice

      Title

      Running Summary

      Post

      While trying to figure out how to do something a bit more complex, I set up a trial table called Running Balance with just two fields:

       

      EnterAmount - Number

      Sum of Amount - Summary = Total of EnterAmount (running)

       

      If I enter a figure in EnterAmount, nothing appears in Sum of Amount if I tab out of EnterAmount, that is, everything in the Sum of Amount field disappears. If I then leave the layout and return to it, all the figures in the Sum of Amount field are again visible. But as soon as I enter an amount and tab, they're all gone again.

       

      I can see that this would be ok if all I wanted was a report, but what I'm after is to see the running total of the amounts entered in EnterAmount as I keep entering new records.

       

      So it seems as though I'm setting this up wrong. How do I need to approach these fields in order to get what I'm looking for?

       

      Thanks in advance. 

        • 1. Re: Running Summary
          Sorbsbuster
            

          I expected FM to perform exactly as you wanted, so I created a test file with the two fields as you defined, and... it does.  (I used FM9.)

           

          I created a field Amount as number, and SumAmount as a summary running total of Amount.  I put both on the layout in the body section.  When I enter an number in Amount, create another record, and enter a number in the second record, the Summary field displays the total the way I would expect.

           

          I moved the Summary field into the header, and it worked, too.  I tried it in list view and form view, and it worked perfectly.

           

          However it only updates upon committing the record, not upon simply tabbing from one field into another.  If that's all the problem is, you could script the commitment of the record once you amend or leave that Amount field (and easily if you're using FM10).

           

          Alan.

          • 2. Re: Running Summary
            martinpaulrice
              

            Alan, thanks for taking the time to figure this out for me. I redid the two fields exactly as you described how you created them. Unfortunately, I got the same results as I got before. By the way, I am using FM Pro 10 -- I should have mentioned that before.

             

            So I don't understand why yours worked and mine didn't. What happens is that I have to change the view to see it. For example, with no records, I enter 500 in Amount and then create a new record -- there's nothing in SumAmount. I switch to List View and I see the first record with 500 in Amount and SumAmount; and in the second record, into which I haven't yet entered an Amount, I see the blank Amount field and the SumAmount with 500. I put 500 into Amount and hit tab, I guess for no reason because it won't go into the SumAmount field, and the SumAmount disappears from both records. Then I create a new record again in List View and all the SumAmounts are still gone. If I then switch to Table View, the SumAmounts are there, also if I switch (without creating a new record) back to List View and to Form View the SumAmounts are there.

             

            I thought that a record was committed as soon as you created a new one (or tabbed out of it), but I'm really a newbie so I'm not too sure of that.

             

            So I tried to create a script, something I have no experience with at all to speak of, and it "sorta" works. Every time I enter an amount and then either hit tab or create a new record, I get the message "This action cannot be performed because this field is not modifiable." But when I hit OK in that dialogue box and it goes away, the correctly updated running total is there. That's what I meant when I said "sorta."

             

            The script is:

             

            Set Field [RunningTotals::SumAmount; RunningTotals::SumAmount + RunningTotals::Amount]

             

            I have the script triggered OnObjectExit. I tried it with OnObjectSave and got the same error message as OnObjectExit.

             

            Any suggestions/corrections would be greatly appreciated.

             

            Thanks. 

            • 3. Re: Running Summary
              Sorbsbuster
                

              Your script returns the error message because you are trying to 'Set Field' on a field that is effectively a calculation - you cannot set it independently.  I have FM10 as well, although I don't see that it will make any difference.  I will set it up in FM 10 and try it for you, if you confirm that I am doing exactly as you want:

               

              - Create 1 table with 2 fields

              - Field 1 is Amount (Number)

              - Field 2 is Summary Field, Running Total of Amount

              - Show both in the body section of the layout. (I'll put the Summary field in the header as well as the body)

              - Create a record.  Enter a number in Amount.

              - SummaryAmount will show the running total upon recond commit (ie: press enter, or click off the filed on to the body section somewhere.)

              - Create a second record.  Enter an amount in Amount.  The summary should show the total of both records (upon Record Commit, etc...)

              - Display this behaviour in Form View and List view modes.

               

              Don't worry about any scripts at this stage.

              • 4. Re: Running Summary
                martinpaulrice
                  

                Alan, it's working now, without the script. I went over your last message and saw that, indeed, the steps you described were exactly what I wanted, so I went back, turned off the script, and the only thing I did differently was click outside the field rather than tab or just create a new record.

                 

                It seems crazy, but all is fine and I really appreciate your help on this. 

                • 5. Re: Running Summary
                  Sorbsbuster
                     No problem.  It will work on smaller 'Found Sets', too - just the way you'd want it.
                  • 6. Re: Running Summary
                    philmodjunk
                       Simply clicking a blank part of your layout will trigger the record to commit and your running total will update. No script required, though the script does provide the user with a more responsive "feel" to your interface.
                    • 7. Re: Running Summary
                      martinpaulrice
                         Thanks, Phil, I appreciate the additional information.