How to Create Efficient Summary Fields?

[MarkWilson]

I am working on an millwork estimating system in FMP 5.5 on a primarily Windows 2000 Pro network.

I enter all the estimate items into a related file via a portal from the parent file. Estimate line items are entered into repeating fields in one or more of three different catagories: Stardard Products, Custom Materials, and Resale Items. Each categories has its own set of calculation to create a subtotal for the unit. Then I enter the hours for each of three categories: Shop, Paint, Install. The Standard Product has hours assigned by lookups. These two hours categories are added together.

With the basic data for each record, hours are Sum( )ed for the entire estimate and multiplied by the hourly rates for each hours category. Next a burden value is required. Burden varies for each hour or type category. To the SubTotal three percentages are added for supervision, drafting, and delivery resulting in another SubTotal. Finally, three more percentage values and/or one fixed dollar amount may be added: profit, warranty, % option, $ option, resulting in a Total.

In one of the reports I preview/print I show the single unit price and an extended price for multiples, and a price per unit of measure. These numbers included all the hourly rates, burben percentages, and markup values.

In summation, I have a lot of data multiplied by many differnt percentages based on the sums of related fields.

1)How can I improve the print speed?

2)How can I improve the viewing speed?

3)Is there a speed differnce between have a repeating field with 1000 reps versus 55 fields?

4)What is the best structure to improve calculation speed?

I used the largest estimate I have, 56 records, to test the print time. It took 5:42 to the first printed page. This is a lot to slow, I believe my employer could tolerate less than 1:00.

* I agree with most people here that repeating fields should not be used in most applications. If the calculations came be performed faster by creating more line item files, I will replace them. Currently, I find the repeating fields advantages in data entry.

Thank you,

Mark

[LiveOak]

Without seeing exactly what you are doing, it is a little hard to comment on which portions of it can be improve. Kind of like decreasing lap times on a sports car we can't see. Also, not too many of us that do this for a living have spent studying the speed of repeating fields (an approach we don't use).

-bd

[gdurniak]

you say "I have a lot of data multiplied by many differnt percentages based on the sums of related fields"

This will be slow, period. FileMaker is not designed for number crunching, ESPECIALLY when the fields are calculated, and / or related

One possible & clumsy solution would be to loop thru all report records, and set the necessary related data first, into number fields

[Søren Dyhr]

Since you outsmart us all on repeating fields, are you to carry the consequences, however must you know this, which acutally also applies to related records.

4)What is the best structure to improve calculation speed?

Never let a calc field reference another unstored calcfield, in tight spots use Lookup next higher or lower.

If you somehow over time decised to switch to portals and repeating fields, and speed still is an issue could this tecnique come ind handy, since it's been the way book keeping have worked since the late 1480 in the non arabic world ...whitout the strain on modern machinery. Google youself to the topic "Pacioli" the inventer of double sided book keeping.

--sd

[MarkWilson]

Gregory, that was my conclusion as well. In my tests using realted values they were three times slower than non-related values.

I have been considering the looping methot, but thought I would ask before I wrote all the scripts.

S