logo

How-To: Shade Your Views Based on Document's Age

Let's say you have a view of support tickets that are awaiting processing. The older the ticket gets the more important it is that somebody does something about it.

graded How do we convey this to the user though? Well, there's various methods, but yesterday I came up with a new way, which I think you'll like. Imagine each row of the view is a varying shade of red, based on the document's age. The older the document the redder the row.

You can see an example of this in the screen-grab to the right and in this DEXT demo page. On the demo page you'll need to scroll down to see it in effect.

How Does It Work?

In terms of CSS this works by virtue of the fact you specify a colour as three numeric values representing the mix of red, green and blue. You might be used to seeing something like this:

<tr style="background-color:#ff0000;"><td>I should be bathed in red</td></tr>

But did you know you can also do it like this (in all browsers!):

<tr style="background-color:rgb(255,0,0);"><td>I should be bathed in red too</td></tr>

An RGB value of 255,0,0 is red, whereas 255,255,255 is white. You can specify any shade of pink by passing 255 for the Red value and any number for Green and Blue, as long as they're both the same. See this table as an example:

rgb(255,0,0) Red
rgb(255,60,60)
rgb(255,127,127) Pink
rgb(255,200,200)
rgb(255,255,255) White

The smaller the number you specify for Green and Blue the redder the colour. Can you see where this is going?

How This Works In Domino

What we need to do in our view is have each row calculate a number between 0 and 255. Brand new documents need to come out of the formula with a value of 255, whereas really old documents need to compute to 0.

The view itself needs to be treated as HTML and one of the columns would have a formula like this:

days_passed:=1+(@Date(2008;11;20)-@Date(@Created))/(60*60*24);
factor:=20;
val:=@Min(@Round((1/(days_passed/factor))*255); 255);
"<tr style=\"background-color:rgb(255,"+@Text(val)+",+@Text(val)+");\">

As you can see (hopefully) the G and B values of the row colour are worked out as a "percentage" value of 255, based on how many days have passed since it was created.

The number you multiply 255 by should be somewhere between 0 and 1. If it's 0 then you get red. if it's 1 then you get white. So it might seem the opposite way round to what you'd expect.

Note that I've introduced a "factor" in to the formula above. What I found was that you need to spread the shading of your rows out over the range of dates you expect to encounter. The factor needs to be about the same order of magnitude as the top end date expected. The best way to work this out is to play with it in your real world scenario.

Using Dates In View Columns

By now you might have noticed the view uses today's date in a column formula. Although the way I've done doesn't invoke the problems with indexing that using @Today does you might be wondering how it gets updated, as the date appears hard-coded.

Remember I've talked before about how you can use scheduled agents to update view selection formula. Well, you can do the same thing with column formulas too! Imagine this code running every night at shortly past midnight:

Set TodayDT = New NotesDateTime(Now)
tmp = |days_passed:=1+([+TodayDT.DateOnly+|]-@Date(@Created))/(3600*24);
factor:=20;
val:=@Min(@Round((1/(days_passed/factor))*255); 255);
"<tr style=\"background-color:rgb(255,"+@Text(val)+","+@Text(val)+");\">"|

Set folder = database.GetView("TicketsByDate")
folder.Columns(1).Formula = tmp
Call folder.Refresh

It's as easy as that. There's little wonder I'm so fond of Notes.

Summary

Used in the right place this technique can add much usefulness to the user and help them immediately recognise documents in need of attention. How you use it is down to you (it doesn't have to be a support ticket system!). It doesn't have to be shades of red either. You can use shades of any colour, as long as one of the RGB values always stays the same. Brilliant.

Next blog entry will discuss the use of this technique with LotusScript.

Comments

    • avatar
    • Kerr
    • Thu 20 Nov 2008 05:28 AM

    Hi Jake.

    Really nice article. While I was reading it I thought there must be a way to get a hex value for a number in formula, but hunting around it appears not. Urgh! How come I never noticed that before? A simple addition of a new format string to @Text would work nicely. How about "RXX" where R stands for radix and XX is the base. e.g. @Text(255, "R16") would give "FF". I should IdeaJam that.

    So while pondering this and the rest of the article I thought of a different way of achieving the same result with different trade-offs. Specifically a way to avoid the calculating today's date in the column formula.

    Instead of putting the style in-line in the tr tag itself you could put a class in there. The class would have two values, a default name, something like "agedrow" and another with some representation of the date, like "ar20081101".

    Then you have a page with the style sheet. This would dynamically calculate matching class styles for the appropriate range of dates you want to factor across, and with the default being set to the max age value for those outside your range.

    Pros: No messing about with agents changing column formula. Simplified column formula. Never need to refresh the index. If you want to have the range / granularity to be hourly across a day, this would work.

    Cons: All the styles for the appropriate date range has to be calculated every time the css is loaded. If you have a wide range of days then this could get pretty heavy.

    Thoughts?

    Cheers

    Kerr

    • avatar
    • Jake Howlett
    • Thu 20 Nov 2008 05:47 AM

    Thanks Kerr. I was beginning to think I'd missed the mark with this one and had another "no comments" on my hands.

    As an aside - I came up with the concept while I was working on a PHP system for a customer. Needless to say but this was so easy to with PHP and MySQL. I even managed to convert to Hex using a very simple function and the inbuilt PHP dex2hex() method.

    As soon as I'd done my natural thought was "Can I do that in Domino?". As always the answer is "Yes, you can". But at what cost. A nightly agent exists in most DBs I create now to do things like update date-based views, so adding one more piece of code to update a column formula is no biggy and this is something I can imagine I would use in a real world Domino app should the need arise.

    Regarding your suggestion - are you saying that the CSS "page" has to know all the dates that exist? Does it do a lookup to the view so it know what classes to expect (e.g. "ar20081120")?

    Jake

    • avatar
    • andy
    • Thu 20 Nov 2008 07:07 AM

    Of course you also use the notes built-in column preference to display as Colour and this also works in the client, simply create a hidden column to calculate either the duration or as I do a Percentage and then create a column with the Display Values as Colors ticked, and use the following code...

    $17 is the Programmatic Value from the column calculating the duration / percentage.

    @If ( $17 <= 0 ;

    230:230:230 : 0:0:255 ;

    $17 <= 25 ;

    230:230:230 : 0:200:0 ;

    $17 <= 50 ;

    230:230:230 : 250:210:20 ;

    $17 <= 75 ;

    230:230:230 :250:150:0;

    $17 >=100 ;

    230:230:230 :220:30:0;

    230:230:230 :250:0:0

    )

    --- End ----

    If your feeling like you need a little graph to display this linearly ( is that a word ).. then you can also display this a a little bar graph, so in the next column....

    distance := @If($17>100; 100 ; $17) ;

    difference := Distance/10;

    remainder := (difference - @Integer(difference))*10 ;

    amount := @Integer(distance/10);

    @If (

    remainder >= 5 & Amount >1 ; "▐"+ @Repeat("█"; 1+amount ) + "▌";

    remainder < 5 & Amount >1 ; "▐"+ @Repeat("█"; amount )+"█" ;

    remainder >= 5 & Amount < 1 ; "▐"+"█" ;

    remainder < 5 & Amount < 1 ; "▐" ;

    "▐"+ @Repeat("█"; 1+amount )

    )

    --- End ----

    replacing the Column Font Style and the character which have probably been stripped here as characters for Blocks and Half Blocks from say Arial Char Code U+2588 and U+285C

    ... and yes it also works in a web browser, so now you have colour, and a vertical bar chart of the duration or SLA or whatever....

    andy

    • avatar
    • Kerr
    • Thu 20 Nov 2008 07:26 AM

    Hi Jake,

    The page would just generate the dates for the expected range.

    Say you wanted the range to be over the last month. The page would have a computed-text element that would spit out the styles for the month range, starting with today. something like:

    t:=@Text(@Today)+"-"+@Text(@Adjust(@Today;0;-2;0;0;0;0));

    dates:=@Explode(@TextToTime(t));

    dates:=@ReplaceSubstring(@Text(dates);"/";"");

    range:=@Count(dates);

    step:=255/range;

    vals:="";

    @For(c:=0;c<range;c:=c+1;vals:=vals:@Text(@Round((c+1)*step)));

    vals:=@Subset(vals;-range);

    @Implode("tr.ar"+dates+"{background: rgb(255,"+vals+","+vals+")}";@NewLine)

    before that on the page you would pu thte default style fo rthe rows e.g.

    tr.agedrow{background: rgb(255,0,0)}

    effectively setting all rows to red and then overriding specific good ones.

    One thing I noticed was that the factor in your method gives a period of white and then asymptotically approaches fully red as it gets older. What I've got here is just a straight linear progression from white on day one to red at the end of the range and older.

    Obviously these could be altered to suit either preference.

    Cheers

    Kerr

  1. So clever Jake...I love the use of scheduled agents to update view selection formula. YDM!

  2. Clever UI enhancement, Jake! I like this as a visual indicator.

  3. An alternative to using a scheduled agent to change the view column:

    You can use @TextToTime("Today") to avoid the indexing problem caused by @Today, but then the view index doesn't update properly. That can be fixed by creating a program document in the Domino directory that rebuilds the view once per day.

    • avatar
    • Jake Howlett
    • Fri 21 Nov 2008 04:13 AM

    Andy. I completely forgot about that Notes' ability to do this. Although looking in to it I don't think it's a method I'd like to use. It works by adding a bgcolor attribute to ALL TDs in the row and adding FONT tags inside all the cells. I know the user sees the same effect but it's not what a developer likes to see in the source.

    As for the $17 column reference trick. I can't get it to work with dates. If I add the formula @Date([21/11/2008]) in to a column called $17 and then add $17 in to the formula for the next column it's just blank. Although if I change the formula to @Date([21/11/2008])-@Created then I get a number displaying. Seems like it doesn't like dates. Odd?

    Either way I guess we still need to update the column each night if we want to compute the document's age relative to today's date.

    I'll revisit the code and simplify it based on your suggestion before I make the updated DEXT download available.

    Kerr. That's definitely a simpler solution but it seems a bit limited in that you have to assume a certain range for the documents. Although I guess the same is true of my solution where you have to introduce the "factor" variable depending on the scenario and time range. Horses for courses I guess.

    Jake

    • avatar
    • Kerr
    • Fri 21 Nov 2008 06:50 AM

    Hi Jake,

    Yeah, I was just having a play with the idea. "Is there a way to move age calculation out of the view, calculating it on demand?"

    One reason I have for that is that I work on some databases that have HUGE indexes that can take hours to build. Scrapping them and rebuilding is a pain.

    I've done this a lot using a document.write javascript call for each view entry, that can call a function on the page, but I know that you always like to see a none javascript version, so it was interesting to try out something new.

    Playing around with this yesterday it looks like I was hitting problems when the range got above 3.5 years, but other than that seemed to be fine.

    I'm trying to think of a way to put in a grace period at the front that isn't part of the defined range, but it's proving quite tricky.

    Cheers

    Kerr

    • avatar
    • andy
    • Fri 21 Nov 2008 06:54 AM

    I see what you mean Jake.. another "NOTES" undefined feature I guess, just as well I am computing the duration as a percentage first...

    another thing I did in the past, although it was just a try-and-see thing was to embed a javascript call to a function directly within the column .. eg.

    column 1 Formula...

    "[<script type=\"text/javascript\"> document.write( GetColor('"+@Text(@Created)+"') ); </script>]"

    and a javascript function on the page to process the information passed in and return a suitable colour as a response, may need you to workout the duration as a javascript function though, I just used it to do alternate colours in rows...

    JSHeader-JSCode

    var i = 0;

    function GetColor (text)

    {

    if ( i == 0 )

    {

    i = 1;

    return '<div style=" background : #FF0000;">' + text + '</div>';

    }

    else

    {

    i = 0;

    return '<div style=" background : #00FF00;">' + text + '</div>';

    }

    }

    hope it helps.

    • avatar
    • Kerr
    • Fri 21 Nov 2008 07:08 AM

    @andy, if you are going to use javascript in that way you are best moving the whole lot out to a function that writes the whole table line.

    So have the view spit out "drawEntry(val1, val2, val3, val4);", either in one column or split up over multiple columns as you prefer.

    Then you have a function on the page drawEntry, that has a document.write for the table row. You then just need to put script tags round where your embedded view element or $$ViewBody field is on the form.

    This will minimise the size of the data being passed down and the size of the view index.

    • avatar
    • Jake Howlett
    • Fri 21 Nov 2008 07:29 AM

    A "cleaner" approach along the lines of what Kerr suggested originally might be to have the view produce rows like:

    <tr class="agedrow ar20081121"><td>Row 1</td></tr>

    <tr class="agedrow ar20081018"><td>Row 2</td></tr>

    etc

    Then your page's onload/ready event can run some code to add colours to row. Something like this (depending on framework in use):

    function shadeRows(){

    $$("tr.agedrow").each(function(row){

    //work out colour based on date from arXXXXXX class part

    shade=computeColour();

    row.style.backgroundColor=shade;

    }

    )

    }

    • avatar
    • Tom
    • Fri 21 Nov 2008 02:23 PM

    This is a wonderful idea, thank you!

  4. I think I got something like this working in ExtJS once by hacking something especially nasty by using multiple renderers for each column in a ColumnModel. That reminds me -- I probably need to go back and find a better way to do that. :P

  5. If I were to schedule the agent to run every 5 minutes, do you think it would have any impact on the performance of the db?

    • avatar
    • Jake Howlett
    • Thu 11 Dec 2008 02:58 PM

    Impact on performance Carlos? In what respect -- do you mean would it make it better than having the code run each time there's a visitor? I guess so, but it depends if you're expecting visitors at a rate of more than one every 5 mins.

  6. I plan on using this technique in a loan app and the incoming requests need to have a decision by 10 minutes so I plan on scheduling the agent to run every 5 mins. Just wondering if there are any drawbacks to this technique.

    Thanks for the great tips.

    • avatar
    • Kerr
    • Fri 12 Dec 2008 05:46 PM

    Carlos, if I was you I'd consider at using javascript to asign row colour rather than have the index rebuild so frequently. My css generation method would also work for you in this case, adjust the range accordingly. Both of these methods will allow you to get minute by minute precision.

    Kerr

Your Comments

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


About This Page

Written by Jake Howlett on Thu 20 Nov 2008

Share This Page

# ( ) '

Comments

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