Repeating Field Calculations

[The Mad Jammer]

I wish to use repeating fields to perform calculations for an expense account application. I have 3 fields F1, F2 and F3 each of which have 10 reps. F1 represents actual costs incurred , F2 represents Advances paid and F3 represents the difference between F1 and F2. Each repitition represents an expense (i.e. rep 1 is Hotel, rep 2 is Car rental, rep 3 is Airfare etc.) The reps are consistant for all 3 fields (i.e. rep 1 represents Hotel expense in all 3 fields etc..). I want all the reps in F3 to be calculated (i.e. F1(x) - F2(x) = F3(x).

First, how can I define the individual repititions of F3 to perform the calculations required automatically.

Secondly, I want the last (or nth) repitition of each field to be the sum of the foregoing repititions of the field(i.e F1(10) = Sum(F1(1...9)) and I also want that repition to be closed for input so that the total will always be dislpayed as the aggregate of the foregoing repititions.

Any way to do this? This is like a spreadsheet emulator.

Thanks

The Mad Jammer

[BobWeaver]

Simply define F3 as a calculated field with 10 repetitions and the formula:

F1+F2

It's not practical to make the 10th repetition the sum of the previous ones. It's easier to simply create 3 new calculated fields Sum1, Sum2 and Sum3:

Sum1=Sum(F1)

Sum2=Sum(F2)

Sum3=Sum(F3)

Then position these sum fields under your repeating fields.

And I should add the standard disclaimer. Repeating fields are a holdover from FileMaker's non-relational days. If you use them, you will be severely restricted in what you can do with the data in them (finding, sorting and reporting). So, you might want to use a related file and a portal instead.