Averaging script

[MikeKD]

HI there,

I'm in the process of replacing a few calculation fields with stored fields populated with scripted calculations to speed up the database. I think there are a few calculations all referring to each other & one table is so slow it's almost unusable.

I've got stuck on this average - I thought it would be pretty straightforward - find the relevant records and average them.

For some reason, it's just averaging the first record record. The found set is correct. The calculation is the same as it was in the original calculation field.

What am I doing wrong please?!

Mike

AverageScript.tiff

[eos]

For some reason, it's just averaging the first record record. The found set is correct. The calculation is the same as it was in the original calculation field.

Average() doesn't work with a found set; your original calculation was (presumably) in Classes, where Marks::percentage is a related field, so Average() aggregated across the related set. To get your result, create a summary field ”Average of“ and capture that (after finding the desired records).

But I think you can simply use the original logic (assuming the related set of Marks for Classes is identical to the found set of Marks from your script):

# Go to Layout [ Classes ]
Set Field [ Classes::ClassMarkAvg ; Average ( Marks::Percentage ) ]

Done.

You see the idea: replace an unstored calculation field with the stored result of that calculation (at a certain point in time) – which means you can use the same calculation for that operation (if you use the same context).

PS: when posting your code as picture, why not add it inline, rather than as an attachment?

Even better, open the script, press Cmd-P, in the Print dialog select “Open in Preview” from the “PDF” button/popup, then copy the text from the document and paste it.

Edited July 2, 2015 by eos

[MikeKD]

Ah - thanks - that's actually very close to my interim solution!

I took the unstirred calculation out of the layout and just copied it to a stored field when navigating to that layout. The speed increase was amazing. It looks like this might actually be an expert authorised solution, rather than something I knocked by luck in the garden shed!

I do now need to have another look at another script that I thought was averaging a found set though.....

Thanks so much!!

(I'd forgotten the cut & paste trick - thought it seemed more of a drag than usual :-/)

Cheers,

Mike