Follow-up: Colour-Code Views Based on Document Age

Prompted by Andy at Notes411.com I decided to revisit the colour-coded view trick and simplify it a little by using a column with a Programmatic Name. The formula for the column which sets the row's background-colour is now:

"<tr style=\"background-color:rgb(255,"+val+","+val+");\">"

$1? This is the "programmatic name" of the first column in the view. This column is hidden, sorted descending and has a programmatic name of "$1". It's formula is:

1 + ((@Date([24/11/2008])-@Date(@Created))/(60*60*24));

The logic for working out the document's age is now in it's own column. So what we've done is taken the presentational code out of the nightly agent and put it back in a column of its own. This has the added benefit of simplifying the nightly agent, which is now:

Set folder = DEXT.Database.GetView("Demos.Colour.Graded")
folder.Columns(0).Formula = |1+((@Date([| + TodayDT.DateOnly + |])-@Created)/(60*60*24))|
Call folder.Refresh

Still not as simple as it could be though. What I'd have liked to have done is have a hidden column which is simply today's date. All the nightly code would need to do is update the date. All the "logic" would stay in the view itself.

The trouble is that, unless I'm sorely mistaken (and I hope I am), referring to columns by their programmatic name is a little buggy. If what you put in the column is anything like a date then you just won't be able to refer to it programmatically from another column. All very odd behaviour, so I gave up trying in the end.

The one big flaw in all this (apart from the need for a nightly agent!) is that it's only ever going to be accurate to the day. If you have a view where you want to differentiate between a document that is 12 hours old one that is 12 minutes old then it won't work.

When you want to get more accurate that days then you'll need to use LotusScript in the form of a WebQueryOpen agent. More on this next time...


    • avatar
    • andy
    • Mon 24 Nov 2008 07:44 AM

    I was having another look at this faormula - @Date([21/11/2008]) and added it into a column, and today it works...

    As did the @adjust($1;0;0;5;0;0;0); in the adjacent column.

    Seems sometimes if you have just copied the column it gets the formula confused... creating a new column appears to have a different effect.

    You can also delete the Programmatic value and the next $value is assigned by domino.



    • avatar
    • Jake Howlett
    • Mon 24 Nov 2008 03:06 PM

    Thanks Andy. I'll have another play and see what happens.

    • avatar
    • Mike
    • Mon 24 Nov 2008 05:23 PM

    I don't think it is the formula that gets confused but rather the fact that the programmatic name of the column also gets copied. This means two columns (or more) can have the same values, even with different formulas. Notes seems to use the values for the left-most column with that programmatic name.

    I've certainly lost quite some time debugging views for this very reason!

    cheers, Mike

  1. Andy's right about not copying columns - nightmare. Especially with display value as icon , or anything that evaluates a similar formula for that matter.

    Also I try to avoid default programatic name ($1 etc) by giving columns a meaningful name on creation.

    The wqo sounds like the way to go.

    Nice idea anyway Jake...Hope you sort it.

  2. Why not calculate the age for the web using javascript. You don't have to change the view and you can even base color coding based on hours and minutes.

    You could put this in script just before the end body tag or use one of the DOMReady methods or scripts from any of a number of frameworks to make sure that the javascript code is executed before table is rendered (for speed an so the user does not see a table without color coded rows). You could also limit this to a column instead of rows or use a bar as in bar chart to visually describe how old the document is.

    At work I have a project where the user needs to see the data in relation to a stop light. Red for past the SLA, yellow for getting close and green for still within the SLA. In this case javascript works great.

  3. Sigh, I need to get better at proof reading. If you really want to go old school, though it is not recommended practice you could put a script element in the first column and use document.write (shudder) to color code your rows.

    • avatar
    • Jake Howlett
    • Tue 25 Nov 2008 02:39 PM

    Hi Tanny. Indeed JS probably would be the sensible option in this case. I just don't know if it's the right way. Easiest isn't always best. In my mind HTML is for data. CSS is for presentation and JS is for logic. Using JS for presentation would have to be second to using CSS.

    I always strive to create sites that work as much as possible when JS is turned off. It's not that I don't use JS. I just don't use unless it unless I *have* to.

  4. Just discovering your website. I’m new to database design, and working with Domino. Pity me.

    I’m trying to figure out how to color code inside a view … I know … I’m a wild one.

    In all sincerity, I’m so happy to finally find the kind of information I’ve been searching for, even if I can only pretend to make sense of it for the moment.

    Many thanks for all your efforts, and information.



Your Comments


About This Page

Written by Jake Howlett on Mon 24 Nov 2008

Share This Page

# ( ) '


The most recent comments added:

Skip to the comments or add your own.

You can subscribe to an individual RSS feed of comments on this entry.

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 »

More Content