too many calculation fields bog down fmpro?

[joecollver]

I've noticed after adding around ten to fifteen unstored calculation fields to my lineitems database that fmpro was running significantly slower than before. I would consider changing the calculation fields to number fields with a calculated value, but then they wouldn't update continously, which is what I need them to do. None of the calculation fields have anything more complicated than a case statement inside them, so why is this computer (pentium 4) getting so bogged down by them?

[LiveOak]

The controlling factor is the DISPLAY of unstored calculation fields. If you have all 15 fields displayed for a record in list view, 15 calculations will need to update for each record displayed. This is where the slow down happens. Even slower are summary fields which must calculate over a found set. As a general rule, don't display any more unstored calculation fields than you absolutely have to in a list view.

-bd

[joecollver]

The problem is still there even after I eliminate some of the unstored calculation fields from the layout. Is there anyway I can make these fields number fields with a calculated value, then create a script that recalculates all the values? sort of like a refresh button for all the calculations?

[kennedy]

Leave the calc fields as they are, but don't display any of them. Instead, display some new fields that do lookups on the calc'd fields. Have the lookups triggered on a field that you set to invoke the "recalc".

[joecollver]

sounds like a great way to do it, but how exactly do you make the lookups "triggered on a field" ?

Thanks for your help!

[kennedy]

Define a self-relationship that uses the "trigger" field as the match field. Whenever that trigger field changes, any lookups done via that relationship will be re-looked-up.

So, then define your lookup fields to lookup the calc'd fields through the self-relationship.

Make sense?

[joecollver]

i tried this approach but it doesn't seem to be working. i set up the self-relationship like you said, and tried making 5 lookups to test with. they look up the value through the self-relationship, but no matter what I try all of these lookup fields start as empty and remain empty even when I run the recalculate script (set field(_trigger, _trigger))

I thought I did everything like you said.. am I missing something?

[joecollver]

I just tried putting data in the look-up fields manually, then running the recalc script to see if they would change. They didn't. Is my recalc script the problem then?

[joecollver]

one thing that it might help for you to know is that all of my fields are in a lineitems database that i'm editing through a portal in another database. my recalc script is not in the lineitems database.

[kennedy]

one thing that it might help for you to know is that all of my fields are in a lineitems database that i'm editing through a portal in another database. my recalc script is not in the lineitems database.

I can't think of why that would be a problem... though, all my uses of a trigger have been within a single file. I can't think of why your auto-enter lookups aren't firing. Hopefully some of the smart guys will have some ideas.

In the meantime, it may be worth trying having your recalc script just do an external perform script to a recalc that is in the lineitems file that does the set of the trigger.

[joecollver]

i tried that too, without success.

what i ended up doing is changing the lookups to normal number fields, then have the recalc script manually set each field to the value in its corresponding unstored calc. It's an ugly solution, but it'll have to do for now.

thanks again for the help

[LiveOak]

Why is it always after you try to fix the leaky faucet for 10 messages that you find out "Oh, I shoud tell you, a 747 crashed into the house"!

The relookup scheme will work, but the lookup fields, self relationship, and script needs to be in the item file. If you display any unstored calculations in a portal, they need to be refreshed and the delay will still occur.

-bd