The Today function: come home, all is forgiven.

[Vaughan]

Folks

For some time I've been advocating the use of Status(CurrentDate) as replacement of the Today function. At the back of my mind I've been wondering why FMI would leave the Today function in FMP at all, other than for legacy backwards-compatiblity. After all it causes more throuble that it's worth.

Well, I worked out why it's still there last night.

I have a database where each record has a date field. This database is basically used as a value-list generator for another database, where I want a list of available dates to be displayed through a value list.

All was working well until I realised that in a few months the list of available dates would be huge, so I decided to filter the value list. One filter was to only display dates in the value list that are current in the future (hide all past dates). So I created an unstored calculation field "If [Date > Status(CurrentDate), TexttoDate(""), Date] to filter it (nothing special there). Can you see the problem? Hint: FMP cannot index unstored calculations, and unstored fields cannot be used in value lists. Bugger.

So I unstored the calc and it indexed OK, but now it didn't re-calculate itself. Ever. Bummer

So I tried using a global field instead of Status(CurrentDate) setting it at startup etc but the global field makes the calculation unstored again. Damnation.

That's when I tried Today function. (I was running out of polite expletives at that stage anyway.)

TODAY is *really* cool!

It's got the charasteristics of both stored and unstored calculations -- it is a stored calculation (so it indexes) but it recalculates itself when the database is closed and re-opened. It solved my problem beautifully. But it does have the limitation that it does not recalculate without a restart at midnight, and I dunno how it functions in multi-user mode, or when hosted with FM Server. However my solution is going to be single-user (it uses replaces a lot) so it's limitations are fine.

So there you have it.

[BobWeaver]

Hmm, the same thing just occurred to me too, when I was answering a question about why a date calculation wasn't updating. I guess there's a use for everything.

Which reminds me; I just found a really great use for repeating fields.

[kenneth2k1]

Now say sorry to those you've been short with - HAHA.

Ken

[Anders]

Hey

BobWeaver don't do a Fermat, there is enough space in the margin!

[Vaughan]

Well, bring it on, Bob! We've slaughtered one sacred cow, another ain't gonna hurt!

[bbaliner]

Thanks for the tip, Vaughan (sorry, I just realized I misspelled your name earlier).

I wonder why it would need a midnight restart, would it not change with the computer internal date?

And what's the usefulness in Status(CurrentDate) if Today is better?

PS I'd really like to see Bob's post about repeating fields, 'cause until now they were damned by everyone:))

[BobWeaver]

Uh yes, repeating fields. I hope this doesn't open up a can of worms but here goes. I've had a couple of applications recently where the user generates a transaction which requires the creation of several records. There are several transaction types, and each type requires the generation of several different specific records. So, I have a template file with um.. err.. mumble.... repeating fields (gaaahhh, I said it!). There are also non-repeating fields for information that is specific to the transaction type such as a transaction ID field. The repeating fields contain information specific to the individual records that have to be generated. Each repetition contains the data for one record.

I have a script in the main file that takes the transaction ID entered by the user, does a Go to Related record in the template file and then imports the related record from it breaking the repeating fields into individual records. So basically, in one operation, it generates all the required records for the transactions.

The advantage to this method is that all template data for a specific transaction type is in a single record in the template file. The user occasionally needs to create a new transaction type. So, he simply creates a new record in the template file and fills in one repetition for each record that must be generated when the transaction is created. I looked at a number of other methods of doing the same thing, but repeating fields seem to be simpler for the end user to work with in this situation, and their usual faults don't really apply here.

Regarding the use of an import in a multi-user environment, Filemaker's technical database is rather ambiguous on this issue. I'm still testing this, but so far it seems to work alright.

[Vaughan]

Imports in multi-user... I'm *sure* it didn't work. But just this past couple of weeks I've been doing it frequently with great success. One possible difference is that the files are hosted by FM Server 5.5.

[Fitch]

FYI, imports in multi-user worked fine for me even with server 3.

From Today to repeating fields to imports... how far off-topic can we go?

[Moon]

Just as Bob Weaver and Vaughan have "discovered" ( REVEALED is a better verb), there are no "bad" or obsolete functions in FileMaker; at least not that I have found. Vaughan points out a valid use of the TODAY function, and Bob describes one very good use of repeating fields. I'd like to point out another: global repeating fields. One global repeating field can do the work of literally hundreds of non-repeating globals simply by using the GetRepetiion function to pick out the global's contents. A nice thing about this is that you can define one global, put it on a developer layout and label what each repetition's content to be in a text label. It is a simple matter to set up all sorts of calculations, scripts and relationships without having to refer to more than one global field. Matt Pertrowski uses this technique in his excellent FileMaker Solution Framework package, and I and others have been using the same technique for years.

The caveat against using repeating fields for pemanent data storage still holds; repeating fields are a bear to use when searching and sorting, but for ephemeral data, which globals are designed to contain, repeating fields makes development a whole lot simpler.

Thanks Vaughan and Bob for taking a little of the bias out of these two venerable features of FileMaker.

[DykstrL]

I have to add my agreement to Moon about using Global repeating fields. I use them for multiple conditional buttons, and for keeping track of which layouts the users are on where they can 'jump' back to where they started or just to the previous layout. Global repeating fields are easier to keep track of than several globals.

[Anatoli]

Yes, I am also using globals for "housekeeping" and they are great!

Back to TODAY vs. Status(CurrentDate).

Will TODAY survive to the next FM version? IMO the TODAY is like stored field somewhere in database. And the database has to be closed and opened again to refresh the TODAY.

We can replace this handy TODAY with Script, which will Set Field "my_Today" with Status(CurrentDate). The only problem will be to make sure, that the script will be executed every day. It can be subroutine in start-up script in solution, or subroutine in call to "Main Menu" script, or it can be executed from dedicated Machine running "Jobs Loop"...

What do you think?