7 Replies Latest reply on Oct 7, 2011 10:30 AM by dg3321

    How to create a running balance in a portal

    dg3321

      Title

      How to create a running balance in a portal

      Post

      I have a layout which contains a basic bookkeeping ledger where one column records expenses and another records credits.  I have a third column which is to show the running balance.  How should the script read for the third column in order to automatically enter the running balance as each credit/expense is added to the portal?

      Thank you.

        • 1. Re: How to create a running balance in a portal
          philmodjunk

          I know the following works in a list view or table view layout and I think it also will work in your portal but you'll have to try it out and see:

          Define cBal as Expense - Credits.

          Define sBalance as a Total of cBal and specify that it be a running balance.

          cBal need not be visible on any layout, you can just add the Expense, Credit and sBalance fields to your portal.

          • 2. Re: How to create a running balance in a portal
            dg3321

            That worked, thank you!

            I did however have to define cBal as credits - expenses b/c expenses - credits gives me a negative number.

            • 3. Re: How to create a running balance in a portal
              dg3321

              It's working well, however i noticed that when i'm in the portal, the balance field goes blank when i assign a category to the credit/expense and then when i click out of the category, it shows the balance.  Is there any way to avoid that?  Should i be putting in the credit / expense figure prior to entering a category?

              • 4. Re: How to create a running balance in a portal
                philmodjunk

                Thanks for catching and correcting my mistake in cBal.

                The screen will go blank when you open a record for editing and then recalculate when you commit the record. That's not an issue you can get around with this approach.

                GetNthRecord is another function you can use to access the value of the previous field, but it sets up a recursion limit that may cause problems if you have a large number of records in your portal and I think you'll see it go blank also, but you can give it a try.

                You might have to set up a script to do the running total instead of a summary field in order to keep it from temporarily blanking out like this.

                • 5. Re: How to create a running balance in a portal
                  dg3321

                  Ok.  How should that script be worded?

                  • 6. Re: How to create a running balance in a portal
                    philmodjunk

                    You can use a number field in place of the summary field.

                    Then Go to Portal Row [Next] can access the previous row.

                    If ( Get (RecordNumber ) ; GetNthRecord ( Balance ; Get ( RecordNumber ) - 1 ) ; 0 )

                    Might also work here  to access the total from the previous record.(I know it does in list and table views, test to see if it works in a portal).

                    Look before you leap here. While this can eliminate the field going blank, you now have to be very vigilant with your interface design, scripting an update for any user action that might need to recalculate your running total.

                    Imagine the looping script you'd need to use if you deleted a portal record in the middle...

                    • 7. Re: How to create a running balance in a portal
                      dg3321

                      Ha, interesting.  Ok.  Yes, overall i want to keep this as simple as possible for the user so i may consider keeping it as is, as it is doing what i need to to do.  I'm simply nit picking here...