Today Function Experience

[Bob White]

Just sharing a problem solution as I could not find anything on the subject here in the Forums. I had converted a small database from 6 to 7 which had time and date fields to record the time on a single record whenever there was an update of any data in that record. When I started the database in 7 for the first time, the date and time was updated on all 2000+ records. After saying several not so nice words under my breath, I found that FM7 had written the following script and set it to run at startup:

# The Today function was removed in FileMaker Pro 7.

# The 'Today' field and this script were generated by conversion.

# On startup, this script updates the value of the 'Today' field in all records if the date has changed.

Set Error Capture [ On ]

New Window [ ]

Perform Find [ Specified Find Requests: Omit Records; Criteria: RCA Membership::Today: "//" ] [ Restore ]

Replace Field Contents [ RCA Membership::Today; Replace with calculation: Get(CurrentDate) ] [ No dialog ]

Close Window [ Current Window ]

So beware and read those darned conversion reports before starting the database in 7. It works just fine with the field being defined to auto enter only during data entry, so I have no idea why the 7 conversion found it necessary to write a data destroying script. After deleting the script, I ended up having to import the dates from the old 6 file to recover this data.

[Ender]

It looks like that script does a faithful job of replicating how the Today function worked. I guess I don't see why this is a problem.

[LiveOak]

If you are still using the Today function (a holdover from FM 2 days) in FM6, you should convert to Status(CurrentDate) function whether you intend to convert to FM7 or not.

-bd

[Fenton]

BobWhite has discovered a hidden "gotcha" to do with a stored Today date. It is not such a simple thing as either Replacing a Today field with a script, as FileMaker does when it converts, or just using Get (CurrentDate). Each has its problems, but there are solutions.

1. Problem: Get (CurrentDate) must be unstored. So not suitable for Finds.

Solution: Write your Finds so they directly address the Date fields, rather than some calculation using Get (CurrentDate) to count days past something. The latter are good for visibility on a record, but make lousy Finds.

2. Problem: Replacing in a field to get a stored "today" date triggers modification date updates.

Solution 1: Build a separate "Tracker" calculation field that only changes when its specified fields are changed. [This is what Bob should probably do.] There are a couple high-quality tracker solutions available that do even more.

Solution 2: Create a separate "modification date" field, which reads a plain modification date field, but can be disabled by a global toggle. Switch it off when you do the Replace with today's date, then switch it back on. Use this field as your "modification date."

(There is a hidden "gotcha" here too, in that the global is on a record, hence that record get's triggered. The work-around is to switch to a zero found set, set the global, then Show All.)

[transpower]

For your next table: define zc_modifiedDate, Auto-Enter, Modification Date.

[Bob White]

Ender: Darn it! In rereading what I typed, it appears that I forgot to say what problem this was causing. The problem is that the user gets phone calls and emails AND needs to know when the last update was in order to respond correctly (and the update that the user cares about is only a single field). The script was bringing this field's date to the current date in all records, thus destroying the user's ability to fully respond.

Live Oak: I was not using the today function. That is why I consider it strange that conversion wrote the script that I mentioned.

Fenton and transpower: Good ideas all but I don't need them here. Thanks for the comments.

[Ender]

I was not using the today function. That is why I consider it strange that conversion wrote the script that I mentioned.

You must have been using the Today function in the original file, or it would not have created the script. The conversion process creates a date field called Today, and the script updates it daily, thus preserving the original functionality.

But as the others have said, it's better to use another method. It sounds like you have already found another method.