[RBG7-L] - Re: How can I do this in RBase

[Dawn Oakes]

Marc Maybe this is me being simple, but could you archive the data and carry a beginning balance entry for everything older than, say 5 years or something?  Transfer the older data to another table, accessible from a different menu item?  How often are they looking at data older than 5 years?     What's that 80 / 20 rule?  80% of your problems are caused by 20% of your clients? or something to that effect <g> Dawn From: Marc [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 09, 2005 5:02 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: How can I do this in RBase Thanks Bob and Jim   I use an end of the day process that re-calculated the balances.  I went to this method because of slow computers. Many offices have 3-5 yr old computers and they are slow.   If you pull up an account that has 10 years of data and you have to re-calculate the balance it slows down the time to load the form.  Also, when any record is changed or you move from row to row it takes too long (1-2 sec) to re-total the balances. I know this is not slow or any fault of RBase, just cheap doctors with cheap computers.   I always get side tracked because of the 10% of offices that want something different.  What they are wanting would be a major change to the db structure and 90% of my users wouldn't want or need it.   Thanks Marc             ----- Original Message ----- From: [EMAIL PROTECTED] To: RBG7-L Mailing List Sent: Wednesday, February 09, 2005 3:46 PM Subject: [RBG7-L] - Re: How can I do this in RBase As is normal, there are always several ways to accomplish the same goal.  For instance, some prefer not to "store" the balance, but calculate it every time they access a customer. Others want to keep a running daily transaction history, and want it recorded each day, so a "day end batch" job runs and updates a "daily totals" table.   Please note that your question about editing a record in the "middle" is valid, if you are keeping a running total.  Once you edit the "middle" record, all subsequent rows would have to be updated.  This can get very messy.  Thus the reason why many running balances are calculated in reports and not actually kept as a record.   Using triggers, one does have to consider several issues. (Such as if you wanted to import a large amount of records, the triggers would fire after each, etc.)    But to accomplish what you are asking, one way is to not look at the previous record, but actually sum the entire account balance. That way, no matter if it is an insert or an edit, the balance will always be correct. You do have After Edit and After Insert triggers that could run a stored procedure to update the customer balance. In most cases, this would be recorded in the Customer Master table in a field such as "CurrentBalance" and thus only maintained in the one spot, not in each transaction record.  Using this method is usually straight forward and easily maintained.   Again, there are many ways to accomplish the same goal!   -Bob         -------------- Original message -------------- Bob   I have never done a trigger before and they tend to scare me.  Would it be hard to keep the rows sorted so it always picked the "last" balance?    Also, what if the user edited a row in the middle, the trigger would have to update that balance and update the balance for all the records below?   Marc   ----- Original Message ----- From: [EMAIL PROTECTED] To: RBG7-L Mailing List Sent: Wednesday, February 09, 2005 1:56 PM Subject: [RBG7-L] - Re: How can I do this in RBase One way (not a computed column though) is to use an After Insert trigger to run a stored procedure.  The stored procedure would could obtain the previous balance and add the new transaction to it. (Substract,  multiply or what ever you need)    -Bob   -------------- Original message -------------- > Hi > > Can I have a formula for the Balance column that > takes the balance from the line above plus the > charge from this line, minus the payment > and write off to get the new balance? > > I would love it if this could be a computed column. > > Custnum Charge Payment Write off Balance > 11 $20 0 0 $20 > 11 $40 -$20 0 $40 > 11 $60 -$20 $10 $90 > > > > Thanks > Marc >