Is there a way to "add" (calculate) fields on a layout like a spreadsheet?

[CCBtx]

Ok. I've only spent a day trying to figure this out,

I have 2 tables:

Customer Table

Field 1 = Name

Field 2 = Cust Key

Transaction Table

Field 1 = Date Stamp

Field 2 = Transaction Type

Field 3 = Amount

Field 4 = Balance

Field 5 = Cust Key

Field 6 = Summary Amount (Total of Field 3)

I am trying to print a report (previewed layout) that will print 1 line item per customer per day.

1. Cust Name (sort)

2. Beginning Balance

3. Total of Transactions

4. Ending Balance

I can get 1, 2 and 3. I can't get #4.

In my report, in order to only print 1 line per customer, I am using a trailing summary in place of the body. I do not have a body.

So... when I point at Item 2 (in the trailing summary section), it gives my the first record in table 2... which happens to be the beginning balance. When I point at Item 3, it summarizes just like it is supposed to.

I am at a loss on how to get item 4.

Options or ideas?

I was thinking that I could just "add" fields 2 & 3 to get 4... but that does not seem to work (or I dont know how)

I am wanting to do this on the fly for any given date, therefore, adding the "current balance" to table 1 isnt really a viable option.

Edited October 21, 2006 by Guest

[comment]

I am not quite sure how you're getting the beginning balance, but the ending balance should be available by a summary field (running total of Amount).

[CCBtx]

Not really. The transaction file has all the transactions for a year... identified by date. So, my find statement just pulls the transactions for a particular day.

The first transaction has the beginning balance for that day and the ending transaction has the ending balance for that day.

In a trailing summary, if I display a field in the transaction record, if seems to give me the 1st record of the sort.. not the last, i.e., the beginning balance.

[comment]

I am somewhat confused here. You speak of trailing summary - but if you want a line per customer, you should have a sub-summary when sorted by customer. And I still don't understand from where your balance is coming. Can you perhaps attach a sample file?

[CCBtx]

Yes. I'll try. Your right. I mean sub-summary