How's this for a customer request: For each instance of a database (to be sold via an ASP model) they want to be able to dictate the exact format of how dates are displayed. For example "Mon, 13 Jul '05", "Monday 13 July 2005 2:04 PM", "07/13/05" or "13.05.05 14:04".

It's a trickier request than you might first think. I've always had trouble saying those three little words - "Can't be done" (well, ok four words). It's such a cop-out; There's almost always a way of solving even the most impossible-sounding request. So, how do meet this one? Well, it involved a few lines of code and a few more fields than should be necessary, but it is possible.

The Problem:

A stranger to Lotus Notes development who happened upon this article might wonder what the problem was. Surely displaying dates in a sensible format is easy? PHP developers must take this for granted. Well, the problem is Notes and dates. Is it just me or are they a pain in the backside.

Notes of course can store date and time values in fields. No problems there. The trouble is displaying them in the format you want, where you want, when you want. Think about it for a moment? Not so easy is it!

Imagine a standard Notes document, with a Date/Time Field on it, which records when it was created. Now imagine that wherever this date is displayed it should be in the format Friday 15 Jul '05, 16:39.

In the document this is easy(ish). Assuming you're using the actual Field in the place you want it to appear you can frig about with the Field properties to get something close. Here's how:

What if the field is hidden though or you want the date to appear in the same format in more than one place on the Form? These Fields settings are nice, but pretty much useless for anything other than that single instance of the field.

In the View it's a similar story. You can set a column to display dates in any format, like so:

Note: In the backend document the field value is always stored in the same standard format, no matter how you set up the display properties for the field or column.

If your database is simple and you're happy messing about with Field/column settings then you don't need to worry. What about my client request though!? They want to be able to edit the date pattern for each database and without having to make any design changes (especially not if it means changing columns in every view!).

Why would they want to do this? Well, imagine they sell one copy to a US client and one to a French customer. Both have a different way of understanding dates and times. One prefers the month before the day, while the other prefers 24-hour clock (military style). It's a complicated world when you start dealing with international times.

So I need a way to let the customer configure the database to show date/time values in a way that will make sense to the end user. Having thought long and hard about this problem I decided there was only one solution. We need to add a new text field, in which we store our formatted representation of the date.

Formatting The Date:

The only language in the Notes livery that allows us to format a date to any degree is LotusScript, which has a function called, funnily enough, Format. Similar to the date() function in PHP.

To get the field called Created in the format "Monday 18 Jul '05, 16:34", we simply call the following method:

Format(doc.Created(0), "dddd dd mmm 'yy, hh:nn")

Herein lies the problem. This is a LotusScript-only method. As you know, LotusScript is not, generally, available to us when displaying views and/or documents.

The Solution:

The only time that LotusScript is available to us on the web is during the open and save event of a document/Form. The code for these two events must reside in Agents - known as WebQueryOpen (WQO) and WebQuerySave (WQS) agents.

Taking the example of the field called Created, let's say we add another field called CreatedDateDisplay. This field is just ordinary text and is computed to its own value. We hide the date field (up at the top of the form) and in its place we put our new text field. Then we add a WebQueryOpen Agent that contains the following line:

The result is the same as leaving the original field in place and tinkering with its display properties. However, we actually want to store the date in this format in the backend document as well. To do this we need to call the same method during the WQS Agent to make sure it sticks. We do this for every document when its opened and saved. But we only do it if it's a new document. That's because it's the created date. If it were a field called "Modified" we'd run the code always.

All our documents now have a field called CreatedDateDisplay that is in the format requested. We can now reference this field in View columns and at any point on our Forms.

Do Views need the date in this verbose format though? Probably not. Probably best to add another field called CreatedDateDisplayShort, while renaming the first field to CreatedDateDisplayLong. For this "short" field we use a shorter date format pattern. We can use the first in View columns and the latter for display in documents.

Configuration:

As I stated earlier the client wants to be able to change this format for each database without needing to make changes to the database design. To cater for this I stored the format string for both long and short dates in what I call Application Setting documents. The WQO and WQS Agents look these documents up each time they run.

You could probably use Profile Documents on the web but I prefer not to. With Application Setting documents it's easy to show them in view format and let a "power user" edit them. Hopefully I'll get round to writing another article about Application Setting documents at some point.

An Example:

There's a sample online - here. It's very basic. Create a new document here. The date in bold red type is our computed text field, whose value was set by the WQO Agent. Before it's saved to the backend document you get the option to over-ride it using the "Format" field on that document. Enter a valid format, using the codes at the bottom of the form and press Save. The document will open and show the date/time in your format. Leave the field blank and it will use the default pattern, set in the Agent. If you open the view of all document from the link at the top you'll see the column using the "short" text field.

You can download the sample database here.

Summary:

This might sound like a lot of work to achieve something fundamentally basic, and it is. But it's the only way I could think of. It's probably something I will use on future projects, where it's not even a requirement. As with most things Domino it often pays to "think outside the box".

It's far from a perfect solution though. Don't get me wrong, I know it's a hack.

For starters, what happens when you change the format patterns? All old documents have dates in a different format! You'd have to write an agent to loop through them all and update values, which is a pain.

There are probably other issues I've not thought of - such as what happens in databases that are replicated across time-zones. That wasn't part of my brief though. For what I was asked to do this was the only solution. Hopefully it will help somebody else out too. Remember it doesn't just have to be about created dates. It can be used for any date field.

One other drawback is that it means having a WQO and WQS Agent for each Form. Is this really a bad thing though? At one point I would have said yes. Now I find myself creating Forms that always have both agents enabled. You never know when you might need them...