Summarizing Repeating Fields

[BobbyW]

I'm doing an expense report that resembles an Excel spreadsheet, in that there are vertical columns and horizontal rows. The layout consists mainly of repeating calculating fields and the columns must have a vertical total on the bottom and the rows a horizontal total for each row. This part works fine. The report is not tied down to a particular time period other than it cannot have records more than 60 days old, so one report could encompass up to 60 days of data AND each record data must be independent of the other records. Each entry is tied to an Accounting Code (1 thru 9). Example: 11/5/05 may be tied to Accounting Code 3 and 11/12/05 may be tied to Accounting Code 7, etc. My problem is that this report must also contain a summary section listed by Accounting Code. Example: the total of all entries tied to accounting code #1 would go on one line; totals of entries tied to code #2 would go on the next line, etc. I can't get FM to automatically seek out these entries, total the fields and display them in another section of the layout. Since each record must be independent of the others I can't do a "find" for certain codes because that will bring up all the records. I need to total each record individually. I've attached a copy of this layout if anyone cares to give it a try. The area in question is the "Summary" section contained within a black border.

Thanx in advance

BobbyW

Expense_Report.fp7.zip

[bruceR]

This is just one of the basic problems with repeating fields - you can't sort, select, or omit them individually. For the most part you should not be using repeats; especially so if they create problems for you. For instance if you wanted to report on a different period - quarters or whole years for instance - you're out of luck.

Edited December 11, 2005 by Guest

[BobbyW]

If I shouldn't be using calculating fields what should I be using? I can't think of any other way to do this except have separate fields for each entry line, i.e. Date1, Date2, Date3, Tolls1, tolls2, Tolls3, etc. Since there will be about 25 lines for entries that would be quite a mess. Any suggestions?

[bruceR]

This is FileMaker. It's a relational database and it's been relational for what, 15 years? You should be using a portal to related records.

See attached revised file.

Expense_Report.zip

Edited December 12, 2005 by Guest