In order for your calculation field to update, it would have to be an unstored calculation field. This could, however, make for slow finds and sorts on this field.
On the other hand, a script could find all records where a text field does not store "expired" but has yearfield < year ( get ( currentDate ) ). It could then update the text field to be "expired" for those found records. Such a script would only need run once a year to update your records.
Considering that, as you correctly note, this only has to be done once per year, it might even be easier to search for the combination of expiration year < current year and replace all those with "expired". Manually, not with a script.
But what I'd really like is a script that is run automatically every time the file is opened, telling FileMaker to redo the calculation for the field which either contains nothing, or the word "expired". It would be useful to know how to do this, because I may want to use the mechanism in other cases.
How do I make the calculation field "unstored"?
Running the script every time the file is opened is possible, but pointless. The calculated value will only change on the first of each new year. I've pointed that out because you might not have realized how seldom this value will actually change.
If the field is of type calculation, you find it in Manage | Database | Fields and double click it to open the calculation dialog. (If you get a dialog with tabs such as "auto-enter" and "validate", it's not a field of type calculation and you need to change the type.) In the calculation dialog, there's a storage options button that you can click to open yet another dialog where you select an option named "do not store..."
Running a script that loops through records will also cause record locking. If any records are being edited when the script runs, it will prevent them from being updated. Go with an unstirred calculation like Phil recommends.
Any script that does this type of mass update--whether by looping script or Replace Field contents should be run when it is impossible for another user to lock any of the records. Often, this can be done by a script that runs late at night when no other users are accessing the file, but that depends on who can access the file and when.
Probably a rookie comment/question but if your contract for purchase is 25 years and would assume 25 years from date of purchase, wouldn't it be more accurate to use the actual DD/MM/YYYY so you could begin contact a set amount of time prior to expiration?
I have a similar solution that uses a simple OnFirstWindowOpen script trigger to set the current date to recalculate a status field (calculation field) so I can contact clients at 30/60/90 days. My solution only contains a few thousand records but seems to recalculate the file very quickly.
The field I am trying to update is a bit of a luxury. It's supposed to bring up "EXPIRED" in red letters for all records where the ownership has expired. If I simply wanted to know the status for an individual record, I could just look at the expiry year which is in a separate field. If I wanted a list of expired records, I could just do a search in that field.
I realize that working through the entire database every time it's opened would use a lot of CPU power and take time. I am looking for a way to trigger the recalculation automatically. whether tis happens every time I open the file, or every time I open a record, or even semi-automatically by clicking a button to run a script which would only be needed once per year.
Grave ownerships are by whole years; a full date format would be redundant.
And if someone acquires ownership on December 31st, their first "full year" is 24 hours?
This is why I have pointed out that your calculation can only produce a new value once every January.
Yes, if you acquire the ownership one minute to midnight on december 31, the first year will last one minute. That is exactly how it works.
And I am completely aware of that the recalculation is only needed once per year.
At the present time, all those ownerships which expired on january 1st, 2017 are still not marked as "expired" because the recalculation did not happen.
I've already pointed out what you can do. Running the script every time you open the file when it will only be able to change the values once a year is pretty silly.
In File Options, the OnFirstWindow trigger can be selected to run a script. If you are hosting the file from server, a schedule can run it once a day--early in the morning after midnight. Such a script can add a record to a "log" table set up for that purpose. A field in that record can auto-enter today's date when created. Your script, whether run once a day or each time the file is opened, and check the date in the most recently created record and either exit without doing anything or iit can add a record to the log and do the needed update.
But I'd try just using an unstored calculation first and only go to this option if I get delays that I don't like.
Would a simple text box on the layout with a 'Hide object when" calculation of (Year (Get(CurrentDate))) - (Yourtable:ExpiryYear) ≤0 work?
Not sure how this would impact efficiency, but as it is just evaluating one calc, on each alyout enter/record load, it should be fine.
You couldn't use it in a find or sort on it.
Just wanted to recapitulate how this was ultimately solved. Philmodjunk and bcameron together guided me to this. Thanks guys!
I created a new date field with global storage, to contain today’s date. I called it ”today”.
Then a script:
1. Erase [table::today]
2. Assign field [table::today; Get (CurrentDate)]
3. Execute record/searchpost
I then set this script to be triggered every time the file is opened.
So every time I open the file, the ”today” field will be changed to contain today’s date, in every record.
To know if an ownership has expired, I need to compare today’s year with the contents of the numeric field ”year_of_expiration”. So the field called ”expired” has this calculation:
If (Year (today) ≥ year_of_expiration; ”EXPIRED; ””)