Script a calculation to recalculate?

[Tenchi]

My Student database has these fields:

stu_Class: holds the 4-digit year in which a student is expected to graduate: Number; indexed, strict, numeric only, maximum 4 characters

Grade: calculates the equivalent grade of the student based on the year in stu_Class;

Calculation, indexed

Case(stu_CompYear - Status(CurrentDate) <= (0*365), "Graduate",

stu_CompYear - Status(CurrentDate) <= (1*365)+1, "12",

stu_CompYear - Status(CurrentDate) <= (2*365)+1, "11",

stu_CompYear - Status(CurrentDate) <= (3*365)+1, "10",

stu_CompYear - Status(CurrentDate) <= (4*365)+1, "9")

After November 30 students need to be promoted by one grade; I need to force the field Grade to recalculate. Unless there is another way to force the update, I want to use a script to do this:

New field: g_stu_Class: Global field similar to stu_Class

Script:

Set Field ["g_stu_Class","Year(Status(CurrentDate))"]

If ["g_stu_Class = Year(Status(CurrentDate))"]

Replace ["stu_Class","Current Contents"]

End If

If ["g_stu_Class = (Year(Status(CurrentDate)))+1"]

Replace ["stu_Class","Current Contents"]

End If

.

.

.

etc. so that stu_Class = 2003 will be replaced with 2003 and cause Grade to recalculate from 11 to 12.

Is this a viable solution, or am I thinking along the wrong lines?

Thanks.

[Vaughan]

Firstly I suggest you change the expression "stu_CompYear - Status(CurrentDate) <= (0*365)" into "stu_CompYear - Year(Status(CurrentDate)) <= 0" so you don't get screwed by leap years.

A calculation could get the grades to update without a script by checking whether the current date is Nov 30 or later. If it is, it adds one year, effectively increasing the grade up one.

The calculation for this would be:

If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate)), stu_CompYear + 1, stu_CompYear)

To put it into your main calculation:

Case(

If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate)), stu_CompYear + 1, stu_CompYear) -

Year(Status(CurrentDate)) <= 0, "Graduate",

If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate)), stu_CompYear + 1, stu_CompYear) -

Year(Status(CurrentDate)) <= 1, "12",

If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate)), stu_CompYear + 1, stu_CompYear) -

Year(Status(CurrentDate)) <= 2, "11",

If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate)), stu_CompYear + 1, stu_CompYear) -

Year(Status(CurrentDate)) <= 3, "10",

If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate)), stu_CompYear + 1, stu_CompYear) -

Year(Status(CurrentDate)) <= 4, "9"

)

Now I reckon you could *really* simplify this if you substituted "13" instead of "Graduate"... the calculation becomes 13 minus the number of years...

13 - (If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate))), stu_CompYear + 1, stu_CompYear) - Year(Status(CurrentDate)))

If you really wanted to, you can still display "graduate" by testing whether the result is >= 13:

If(

13 - (If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate))), stu_CompYear + 1, stu_CompYear) - Year(Status(CurrentDate))) >= 13, "Graduate",

NumToText(13 - (If(Status(CurrentDate) >= Date(11, 30, Year(Status(CurrentDate))), stu_CompYear + 1, stu_CompYear) - Year(Status(CurrentDate))))

)

I just tested this and it works... it has the additional "feature" of calculating grades right down to zero (and beyond!) if the date is far enough into the future!

[CobaltSky]

The issue here is that the calculation fields you are using are stored (and indexed) calculations, which do not automatically update when the system date changes.

Normally, calculations using Status(CurrentDate) (or other status functions for that matter) should be unstored so that they recalculate automatically every time the data is called for. I assume that in this instance, however, it is desirable that the field be stored and indexed to facilitate efficient searching and/or so that it can be used as the foreign key in a relationship.

This being the case, one answer to the problen would be to use the 'today' function rather than 'Status(Current Date)' function. 'Today' automatically forces a recalculation every time the database is opened - however if the number of records is large, this can slow down the start-up and may be a nuisance, given that you only need recalculation to occur once a year.

An alternative solution, then, would be to create a trigger field (a number field) and nest the existing function inside a further case function which will enable you to run a script once a year to force an update. To do this, you should change the formula to:

Case(trigger,

Case(stu_CompYear - Status(CurrentDate) <= (0*365), "Graduate",

stu_CompYear - Status(CurrentDate) <= (1*365)+1, "12",

stu_CompYear - Status(CurrentDate) <= (2*365)+1, "11",

stu_CompYear - Status(CurrentDate) <= (3*365)+1, "10",

stu_CompYear - Status(CurrentDate) <= (4*365)+1, "9"))

---

Then you will require a script which you can run once a year, along the lines of:

Show All Records

Replace[No dialog, "trigger", "trigger + 1"]

---

...where the additional number field is called 'trigger', and the "Replace with calculated result" option is selected for the 'Replace' script step. The suggested formula will increment the trigger field value by 1, forcing recalculation of the Grade formula. Note that you'll need to run the script once to get the new formula to calculate for the first time.

[Vaughan]

The big challenge withn Today function is that the database must be closed and re-opened once a day for it to recalculate -- a pain if it is permanently hosted on FM Server s it needs to be opened in FM Pro to recalculate.

[CobaltSky]

Hi Vaughan,

What you say is true, but 'tenchi' only needs it to recalculate once a year - and that shouldn't be too onerous