Calculation with Date as a key field: How to refresh?

[Paul_G]

Hi folks - I have a medical recall system set up. I have a portal to view the various recalls, with the Recall Status as a key field, so I can filter the portal to just show recalls that are currently due, recalls overdue, etc etc.

In order to be able to use the Recall Status as a key field, the calculation has to be stored - fine, since all the fields that make up that calculation are within the one table. The problem is that the status is dependent on the current date (due, overdue, etc), and because the field is stored Get(CurrentDate) part of the calculation doesn't 'refresh'.

If I go into the calculation and change it to Unstored, the date and calculation refresh, and the status is correct and up to date. But then the portal doesn't work because it's unstored. Then I change it back to Stored, and the portal works fine - until the next day when the date is out-of-date again.

Is there a way to either (a) have the date automatically refresh while still being able to have the status as a key field, or (: have a script/button that will force a refresh rather than me having to go in and change the calculation storage each time?

[dazlunn]

You would perhaps be better to use a global Date field rather than a calculation field in your circumstance. You can then use the 'Setfield' script function to update the field with the current date from a simple button, or in your startup script (if you have one).

Setfield Value: Get(CurrentDate)

Since you alluded to doing this by script I assume you would understand this?...

[comment]

Leave the status unstored, and use another way to filter the relationship. For example, to show only overdue recalls, make the relationship:

Parent::cCurrentDate > Recalls::DueDate

where cCurrentDate is an unstored calculation of Get(CurrentDate).

[Paul_G]

Both great ideas - I'll give it some thought - thanks.

[Paul_G]

Leave the status unstored, and use another way to filter the relationship. For example, to show only overdue recalls, make the relationship:

Parent::cCurrentDate > Recalls::DueDate

where cCurrentDate is an unstored calculation of Get(CurrentDate).

Although... wait a minute - doesn't that just mean that I have another situation of an unstored calculation used as a key field? In which case it wouldn't work, for the same reason my current key field doesn't work? (that is, the calculation needs to be stored to work, so the date changing doesn't update it) Sorry, not near my work computer now to try it out.

[comment]

wait a minute - doesn't that just mean that I have another situation of an unstored calculation used as a key field?

Yes, but it's on the "parent" side of the relationship and that is allowed.

[Paul_G]

Yes, but it's on the "parent" side of the relationship and that is allowed.

Oh - I didn't know that. Thanks!