problem with field

[janedoe]

hi,

we have a problem with a summary field in that it recalculates the total in a related file, the field is not displayed in this related file, and none of the fields used in the summary/calculation (for Points_s) are being updated...

the user is then forced to wait while the Points_s field is recalculated. this is problematic when the file in which these fields is stored contains ~50,000 records.

in general the Points_s field is used in reporting to determine how many points a person has accumulated based on various flags.

details of the field are as follows:

--

Points_s = Summary field, Total of Points_c

--

--

Points_c = Calculation field, Unstored

( Level * 1.5 ) * ( Tenure / 52 ) +

Case (

ISW = "Y" and Status = "On";

4;

ISW = "Y" and Status = "Off";

1;

0

)

--

--

Level = Number field, Indexed

--

--

Tenure = Calculation field, Unstored

( End Date - Start Date ) / 7

--

--

End Date = Date field, Indexed

--

--

Start Date = Date field, Unidexed

--

--

ISW = Calculation field, Unstored

If (

PatternCount ( Ready; "Independent" ) > 0 or PatternCount ( Ready; "ISW" ) > 0;

"Y";

""

)

--

--

Ready = Text field, Indexed

--

--

Status = Text field, Indexed

--

hopefully this makes sense.

i would appreciate any feedback.

thank you,

jd.

*Note: Some of the field names/values have been changed to simplify the query.

[comment]

I believe you could speed things up considerably by turning Tenure, ISW and Points_c into STORED calculations. There are a few minor optimizations that could be applied to the calcs as well, but that's hardly the issue here.

You mentioned a related file - but didn't elaborate on the relationship.

[janedoe]

hi,

the related file is a filenotes file, the relationship is based on the person id, so does not use any of the fields mentioned earlier...

jd.

[janedoe]

hi,

we have changed all the calcs to be stored. however, we are still seeing the summary dialogue

any thoughts?

jd.

[comment]

What dialogue? You didn't mention any dialogue before. And I still don't know what part the relationship plays here.