Alpha DevCon 2018
Results 1 to 15 of 15

Thread: Simple Balance Forward problem

  1. #1
    Member
    Real Name
    Bruno Yim
    Join Date
    Mar 2001
    Posts
    44

    Default Simple Balance Forward problem

    The records in this db consist of budget information like payee, due date amount, bal forward,etc. I start out with a record containg the beginning balance and as I enter transactions, I need to post a new balance forward for each tarnsaction. I have no problem creating a running balance forward using the post field rule of adding or subtraction to a balnce forward. However my problem is showing the balance forward for each transaction in the db. IE, if the BF was $1000 and I entered an income transaction of $50, the BF for the record should show $1050. Instead, that figure appears on the Balance Forward of the first record in the db. In other words, I'd just like to have a running BF for each transaction entered. Hope I'm able to explain what I'm looking for. Also I said in the beginning it was simple, and I know if I looked deep enough I can either find the answer in Action Script or perhaps Xbasic. Maybe someone who has the posting example, etc. that can help me.

    Tia
    Bruno Yim

  2. #2
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,907

    Default Re: Simple Balance Forward problem

    Hi Bruno,

    Take a look at a few of these threads...I think one or more may have the answer you are looking for. I used the words... running total, checkbook .... for the search.

    http://msgboard.alphasoftware.com/al...earchid=290315
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  3. #3
    Member
    Real Name
    Bruno Yim
    Join Date
    Mar 2001
    Posts
    44

    Default Re: Simple Balance Forward problem

    MikeC

  4. #4
    Member
    Real Name
    Bruno Yim
    Join Date
    Mar 2001
    Posts
    44

    Default Re: Simple Balance Forward problem

    MikeC
    That link is no longer available. Thanks anyway because I was able to take your advice and use the search. Still I was not able to get any info from the post that were there. There seem to be other people having the same balance forward problem like I do. Hope someone out there does have a solution.

  5. #5
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,907

    Default Re: Simple Balance Forward problem

    Bruno,

    Sorry about the bad link--I was sure I double-checked it but anyway here is one of the threads that seems to indicate an answer for you in a couple of ways---you probably noticed the reference to Dr. Peter Wayne's book in which he guides a user through creating a checkbook--sounds like it might be a worthwhile investment for you.


    http://msgboard.alphasoftware.com/al...l%2C+checkbook
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,954

    Default Re: Simple Balance Forward problem

    Quote Originally Posted by by5751 View Post
    The records in this db consist of budget information like payee, due date amount, bal forward,etc. I start out with a record containg the beginning balance and as I enter transactions, I need to post a new balance forward for each tarnsaction. I have no problem creating a running balance forward using the post field rule of adding or subtraction to a balnce forward. However my problem is showing the balance forward for each transaction in the db. IE, if the BF was $1000 and I entered an income transaction of $50, the BF for the record should show $1050. Instead, that figure appears on the Balance Forward of the first record in the db. In other words, I'd just like to have a running BF for each transaction entered. Hope I'm able to explain what I'm looking for. Also I said in the beginning it was simple, and I know if I looked deep enough I can either find the answer in Action Script or perhaps Xbasic. Maybe someone who has the posting example, etc. that can help me.

    Tia
    Bruno Yim
    I have never been able to understand why it is necessary to store this type of information , but then I don't use this type of data so it is probably just me.

    The balance forward, on a given date, should always be obtainable as a calculation.

    balance_forward = beginning balance - transactions

    for a given payee as of a given date?

    If payee is the unique identifier field for the payee
    If beginning_balance is the field name for beginning balance
    If t_date is the field name for the transaction date
    If transaction is the field name for the transaction amount
    If there is one record for each payee with the beginning balance, multiple records for transactions.

    On a form, report, browse, etc

    Code:
    tablesum("tablename","payee = "+s_quote(payee),"beginning_balance")-tablesum("tablename","payee = "+s_quote(payee)+".and. t_date <= "+s_quote(date()),"transaction")
    should yield the current balance forward (today's date) for the currently visible record on a form, each record in a browse, each record in the detail section of a report or if placed in a group header or footer breaking on payee, for that payee - when used as the expression for a calculated field.

    If you want the balance forward as of some other date, just create another calculated field and substitute that date for date() in the expression. You could refer to a variable if you liked.....

    Code:
    tablesum("tablename","payee = "+s_quote(payee),"beginning_balance")-tablesum("tablename","payee = "+s_quote(payee)+".and. t_date <= "+s_quote(var->somedate),"transaction")

  7. #7
    Member
    Real Name
    Bruno Yim
    Join Date
    Mar 2001
    Posts
    44

    Default Re: Simple Balance Forward problem

    MikeC
    The links are exactly what I was looking for. Many thanks for your great help. Hope one day I can return the favor.

  8. #8
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,907

    Default Re: Simple Balance Forward problem

    You're welcome.

    There are so many ways to accomplish things that for sure try out Stan's as well as the others....if it fits with what you are trying to do that is.

    Good luck!
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  9. #9
    Member
    Real Name
    Dave Martens
    Join Date
    Aug 2006
    Posts
    75

    Default Re: Simple Balance Forward problem

    Just a thought here, but storing the balance forward is undesirable as Stan notes, but for a reason that he didn't expressly state: it represents duplicative information in your database, which is (simplistically) "bad design style."

    Admittedly, calculating the balance forward for each transaction is a little bit involved. Displaying it quickly may be a challenge. Yet, the reason for good design style is that it tends to save time and reduce bugs in the long term.

    For example, if you compute and store balance forward for each transaction, you must be prepared to adjust all those stored balance forward results when transactions are entered into your checkbook out of chronological order.

    Also, to expand on my earlier point (and Stan's really), the balance forward is duplicative because it implicitly includes the value of each transaction, which is stored separately. Having two versions of the same information requires that those versions be coherent (changes to one form require changes to the other).

    I suspect the means for computing the balance forward at each transaction, as a real time computation, is apparent if you want to go that way. Of course, I merely offer this as a philosophy for longer-term success.

    Good luck,
    Dave

  10. #10
    Member
    Real Name
    Bruno Yim
    Join Date
    Mar 2001
    Posts
    44

    Default Re: Simple Balance Forward problem

    Dave:
    Thank both you and Stan for your philosophical advice. As noted, to me this was what I thought was very simple. If I did this in Cobol 50 years ago I would have no problem coming out with a solution, but in todays programming Windows, method, etc. I seem to be way out of my league. I am trying to develope my own very simple budget system without all the bells and whistles until my boss(wife) wants some additional feature/upgrade. Right now I wanted to view my transactions via a browse and not yet on a report. Later maybe. Currently, in my household we handle approximately 16 inc/exp transactions. When I receive a statement in the mail, power, gas, telephone, credit card, etc. all I want to is keep track of these transactions when I enter them in the DB. I will enter the usual information, date received, payee, amount to be paid, due date, etc. The system will automatically back date 10 days from the due date, so I am sure to send out my payment to the payee within time of the due date. Since these transactions are entered into the system at any time of the month, I wanted first, to show the beginning balance of the month, then as transactions are entered in the system I would like to show the running balance on a transaction basis. I have no problem updating my balance forward field to show the running balance, It is the balance forward for each transaction that I am interested in. That information may seem trivial for you or Stan but it does allow me to see where I stand throughout the month. That is what I meant by being simple.

    Thanks very much
    Bruno

  11. #11
    Member
    Real Name
    Dave Martens
    Join Date
    Aug 2006
    Posts
    75

    Default Re: Simple Balance Forward problem

    Hi Bruno,

    I'm glad to hear your perspective and that our advice was taken as intended - as mere advice. You seem to have a clear and sensible idea of what you want to do and why. I have no doubt you can and will reach those goals.

    Best of luck
    Dave

  12. #12
    Member
    Real Name
    Rudy Pataky
    Join Date
    Apr 2008
    Location
    Toms River, NJ
    Posts
    171

    Default Re: Simple Balance Forward problem

    Hi Bruno,
    Rudy here from NJ. I'm trying to create a eunning balance in a browse using Peter Wayne's book and I still cannot get it to run. I get syntax errors, variable type mismatches errors etc.

    Did you ever get your app to run correctly? I would like to set up a button called Re-calculate Disbursements and when I press it, have it start with rec 1 and propagate the balance forward until the end of the table.

    Would like to take the ending balance from the 1st record in the embedded browse and use it as the starting balance in 2nd record and so forth and so on.

    I'mm 66 years old and worked with Cobol and CICS for many years before retiring. Like you, in Cobol a snap.

    I've attached a screen shot of my form. To get it to work, I vopy thr bal_fwd fro the 1st and paste it to the open balance of the 2nd entry and the formula for now calculates "bal_fwd=opn_bal+deposits-Chk_amt". Doing it this way until I can figure out gow to automate it. Hope you can help.

    Thanls,
    Rudy

  13. #13
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,163

    Default Re: Simple Balance Forward problem

    I just ran across this thread while searching for something else and thought I would add in my 2 cents.

    I do not store running totals in my dbf's as per Stan's advice. To get the starting balance on my invoice form I do the following:

    1. On the form in question, I create a button for Printing. This button prompts for the Consumer (or whatever field you like) using an XDialog and stores that to a variable. I then prompt via XDialog, for two dates, one for the starting date and one for the ending date. You can set these up any way you wish. For mine, I used a date in the far past to start and "=date()" for the current date. The user can then make their selections.
    2. On the report form I add a calculated field as follows:

    balfor = tablesum("pna","consumer='"+var->hac+"'.and.between(gdate,{11/11/1111},var->lac)","amount")

    pna = the table in question.
    hac = the result of the consumer prompt
    gdate = the date you are using in your dbf
    {11/11/1111} = a starting date in the past before all possible input
    lac = the end date obtained from the prompt
    amount = the dbf field for the amount

    That should do it.

  14. #14
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Re: Simple Balance Forward problem

    Thanks, Charles. Your contribution may be a big help to other travelers who wander by and discover this thread. -- tom

  15. #15
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,954

    Default Re: Simple Balance Forward problem

    That will work if var-> lac is global, but (opinion follows) it is more proper and portable/flexible to use

    balfor = tablesum("pna","consumer='"+var->hac+"'.and.between(gdate,{11/11/1111},{"+dtoc(var->lac)+"})","amount")
    or
    balfor = tablesum("pna","consumer='"+var->hac+"'.and.between(gdate,{11/11/1111},"+s_quote(var->lac)+")","amount")
    There can be only one.

Similar Threads

  1. Simple filter problem
    By DoubleShufty in forum Alpha Five Version 8
    Replies: 4
    Last Post: 08-03-2007, 01:59 PM
  2. Forward projection calcs. - are these possible in
    By Robert Rough in forum Web Application Server v6
    Replies: 3
    Last Post: 09-07-2005, 06:58 AM
  3. Forward and Back buttons (like IE)
    By Jay Talbott in forum Alpha Five Version 5
    Replies: 8
    Last Post: 05-30-2004, 09:05 AM
  4. Carry forward - Field/Record values
    By Santhanam Peranaidu in forum Alpha Five Version 5
    Replies: 2
    Last Post: 01-31-2004, 10:26 AM
  5. simple problem
    By stevemreid in forum Alpha Five Version 4
    Replies: 2
    Last Post: 07-29-2002, 08:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •