Sum - Running total problem

[Tpaairman]

I'm almost done with my checking/billing file, but I have run into one more hitch.

Basically this part is a basic check register. There is a Transaction Amount field, which is for the amount of the transaction and a Balance field which shows the current balance. However I have an archive function that deletes transactions over one year old (it's contents get pasted in a text file) Long story there,but that part works great.

Forget all of the background on the archiving, though - here's the problem. I have two additional fields - Archive Adj - this is a number field set as a global, the other is Balance Adj.

Balance Adj is a calculation that is simply Archive Adj + Trnasaction Amt. So far we are working just fine. It does properly add the two together. Now we have Balance which is a Sum field set as a running total for the Balance Adj.

As long as the Archive Adj is at zero all is fine. but as soon as anything is in the Archive Adj field, the final balance is multiplying that amount times however many records I have an then adding it to the transaction amount. So let's say I have a transaction of $50, and I put a 1 in the Archive Adj. Now let's say I have 10 records. What I am supposed to see on the balance is $51, but what is actually showing up is $60 (my $50 transaction amt, plus $1x10records)

[comment]

It's because you're first adding your starting balance to the transaction amount (i.e. to EACH transaction amount), and only then doing the total. The correct order is to first summarize the transaction amounts, and only then add the starting balance.

[Tpaairman]

No, I'm not touching the actual balance until the last thing. Here's what I am doing:

Transaction Amount + Adjustment = New Transaction Amount. Then have a running sum of the New Transaction Amount.

The problem is FMP is multiplying the Adjustment by the total number of records.

Edited November 10, 2007 by Guest

[comment]

You say no, but you do exactly what I said. The 'New Transaction Amount' is calculated separately for EVERY Transaction Amount, thus adding the Adjustment to EACH record in the found set.

Instead, your summary field needs to summarize the Transaction Amount field, and the calculation field needs to add the adjustment to the result of the summary.

[Tpaairman]

Actually, I posted that, then had to go somewhere. I got about a block up the road an it hit me. I had misread what you were saying, but you are right. It is doing it because it is making the adjustment to every transaction.

[D Arch]

I am also working on a check register solution. I am having a problem with my summary calculations. Each time I re-sort the records (date-order or check-number-order or whatever) the "current balance" numbers are all wrong because they do not automatically recalculate. How do I force a recalc with the sort?