logo

Date Fields in Your Format

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:

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

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...

Feedback

    • avatar
    • IanB
    • Fri 5 Aug 2005

    easier, built in way?

    would this help? [url]http://www-10.lotus.com/ldd/demos.nsf/lookup/Web+Preferences+Demo[/url]

      • avatar
      • Jake
      • Sat 6 Aug 2005

      Re: easier, built in way?

      It wouldn't help me really. In fact it's caused problems for me in the past on this site!

      It's a good idea though. It just wouldn't help me with the requirement that dates be in any given format.

    • avatar
    • Andrew Tetlaw
    • Fri 5 Aug 2005

    Don't forget @Text

    For display the same date - field value in different spot you can always fall back on the @Text modifiers like:

    @Text(@Now;"D1S3")

    If you want full days or month names you need to do the old select the month number from the name list.

    But I think you can get enough out of @functions to not require a WQO or WQS agent.

      • avatar
      • Jake
      • Sat 6 Aug 2005

      Re: Don't forget @Text

      Show me the @Formula to turn a date field in to "Saturday 6 Aug, '05 11:01 AM" and I tell you if I think you can do away with WQO/S ;o)

      And what about the user wanting to change date format for each database?

      Show the rest of this thread

    • avatar
    • Anthony
    • Tue 9 Aug 2005

    Shared Columns

    Given that Notes/Domino 7 is on the near horizon I thought it is worth mentioning that *shared columns* are a new feature and will resolve the change every view issue you described above.

    You may not get the perfect format but it is easier than changing every view or writing a value to the document.

    As we are not currently in an envrionment where this is possible I use the same method as you do. And as you said you get more control over the format of the date field that is displayed.

    • avatar
    • Mark
    • Wed 10 Aug 2005

    What about JavaScript ?

    If it's a web only application, why not use JavaScript to display dates in the correct format on the screen? We've been using this for quite a while now without any problems:

    - Store the date/time as you do normally in a date/time field, but add an extra computed field for every date/time field in which you store the date/time as a text value in a common format (ie yyyy/mm/dd hh:mm:ss, which sorts correctly also). - When displaying a date, use a JavaScript function to transform the text date/time in the format you'd like.

    This can easy be used everywhere you'd want to display the date.

    Sounds easier to me than adding a wqo/ wqs agent everywhere.

    • avatar
    • zomek
    • Tue 13 Sep 2005

    OT: constructing html element

    While testing your demo dbs I noticed one small flaw (?) in your technique of constructing base url address for base element - you omit web server's port number. So if web server use port number other than default, all the internal links on page become broken. I know it's not so common case, but still better to be prepared for the worst... ;-) I use formula: "<base href=\"http://" + Server_Name + @If(Server_Port = "80"; ""; ":" + Server_Port) + "/" + @WebDbName + "/\">" And of course, to be completely safe, one should check protocol used...

      • avatar
      • Jake Howlett
      • Tue 13 Sep 2005

      Re: OT: constructing html element

      True Zomek. Something I'm aware but rarely take in to account. Thanks for the reminder. I've slapped my wrist.

  1. Very Cool

    Jake, This is a very useful tool. Very cleaver! GOOD ON YOU MATE!!

  2. Question with different date/time

    Dear Jake: Let me to do a question....I need to implement in a form for Web two type hour fields , the first field computed take the local hour from Domino Server in my country and 2sd field is added manually a number+first field and it must appear the hour of other country, for example, local hour of Moscow, Russia....

    Please, can you help me? All the best for you, Carlos.

Your Comments

Name:
E-mail:
(optional)
Website:
(optional)
Comment:


Navigate other articles in the category "Miscellaneous"

« Previous Article Next Article »
Searching Domino Simplified   None Found

About This Article

Author: Jake Howlett
Category: Miscellaneous
Keywords: Date; Time; Format; Text; Month; Year;

Attachments

dateformat-v1.0.zip (37 Kbytes)

Options

View Online Demo
Feedback
Print Friendly

Let's Get Social


About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »