List Of Years

One strength of Notes development is the way it gives us so many approaches to achieving the same goal. Lots of different languages and methods to choose from. The formula language for one is great. It gives us simple ways of achieving seemingly complicated ends.

On the flipside it's easy to forget all the different methods involved, as there are so many of them and you can go for so long without needing any one particular technique. For example, at the moment I'm struggling to remember how to do something which I'm pretty sure is both possible and quite simple. It's just that, with the Formula engine and the tricks that rely on it, there's often a lack of intuition involved and it's more a case of remembering exactly how it's done.

Anyway, at risk of turning this in to my very own forum, I have another brain teaser for you. On a form I want a field called Year, which lets you choose a value anywhere between five years ago and ten years in the future. So, now, it would include 2001, 2002, ... 2005, 2006, 2007 .... 2016. Make sense?

How do I do this in Formula? At first I thought it would be a simple job, but I can't seem to get the function I need. Here's what I came up with and from where I can get no further:

  @Text(@Adjust(@Now;-5;0;0;0;0;0)) + " - " 
  + @Text(@Adjust(@Now;10;0;0;0;0;0))

The result of this is a text list of every single day for a period of fifteen years. All I want are the years. I tried wrapping the whole thing with a @Unique(@Year()) but that didn't work. I'm stuck.

I know I could probably use a @For or @While but that seems like cheating. I like the simple @Function tricks that seem to defy logic, but always give us what we want.


    • avatar
    • Peter
    • Tue 7 Feb 2006 05:43 AM

    Hi Jake,

    this should do the trick:

    @Unique(@Text(@Year(@TextToTime(@Explode(@TextToTime( @Text(@Adjust(@Now;-5;0;0;0;0;0)) + " - " + @Text(@Adjust(@Now;10;0;0;0;0;0))))))))



    • avatar
    • Marie
    • Tue 7 Feb 2006 05:49 AM

    Something like :

    min := @Year(@Now)-5;


    • avatar
    • Jake
    • Tue 7 Feb 2006 05:59 AM

    Thanks guys. Both work but one is so so much quicker than the other. I'm sure it's obvious which one, but worth mentioning that the date-based solution has a definite lag when opening the form.

    Marie's solution is perfect and demonstrates the original point I was making about the Formula language.

    It seems perverse though. I can see how it works but I would never have arrived at that solution myself.

    Am I right in saying that these tricks are non-intuituve and simply a case of remembering how? Or am I being stupid?

    Ok, another problem. Take any year in the past and give me a list of the years between now and then. Say the year was 1999. I want 2006, 2005, ..... 2000, 1999. The 1999 here is the variable. Can Marie's solution be adapted to allow a variable number of years to be added to the list?

    • avatar
    • Marie
    • Tue 7 Feb 2006 06:15 AM

    I'm not sure I understand correctly that one. You mean the range of year is not predetermined and somehow the list has to generate itself ? I have a solution if @for is accepted otherwise ??? Usually for something like that I have such a list stored in a profile document way larger than expected (between 0 and 100 let say).

    • avatar
    • Jake
    • Tue 7 Feb 2006 06:25 AM

    Hi Marie. It would be easy with an @For, but I want to be clever about it (well, get somebody else to be clever for me).

    In this case the start year varies for each document and is stored in a date field on it. The end date of the range is always the current date. So if the document's stored date was 2002 the computed list would be 2002, 2003, 2004, 2005, 2006.

    • avatar
    • Mats Hasselquist
    • Tue 7 Feb 2006 06:29 AM

    I think you're on the right track, most things can be done without loops in formula but is a good time to use it.





    For your first problem I came up with almost exactly the same solution as Marie, but she was quicker :-)

  1. This should work...

    StartYear := 1987;

    @For(n := @Integer(StartYear);

    n <= @Year(@Now);

    n := n + 1;

    years := years:@Text(n));


  2. looks like Mats Hasselquist beat me to the same solution.... just added the @integer incase you were pulling from a text field...

    • avatar
    • Marie
    • Tue 7 Feb 2006 06:40 AM

    OK. For a case like that, I think I would go with my list stored in a profile doc. So it would give something like (I suppose MYREFFIELD is a date).

    range := @getprofilefield("basics"; "numrange");

    min := @year(MYREFFIELD);

    list := min*+range;


    • avatar
    • Marie
    • Tue 7 Feb 2006 06:46 AM

    With a @for I would generate the range, like that :

    min := @year(MYREFFIELD);

    len := @year(@now)-min;




    • avatar
    • Mats Hasselquist
    • Tue 7 Feb 2006 06:55 AM

    An alternative if you don't like to loop but it's limited to 100 years.






    • avatar
    • Marie
    • Tue 7 Feb 2006 07:00 AM

    Just a quick add. If you want to prevent unworking date range (for example MYREFFIELD > @year(@now)), we could do the following :

    bounds := @sort(@year(MYREFFIELD):@year(@now));

    len := bounds[2]-bounds[1];




    • avatar
    • Manu
    • Tue 7 Feb 2006 07:43 AM

    this would basically do the trick;

    some lines can be reduced, but I left them in for readability's sake

    numberofyears can be calculated (to cover the second question, startyear can be calculated to cover the first question








    • avatar
    • Jake
    • Tue 7 Feb 2006 07:55 AM

    Thanks guys. I have my solution now. Thanks to Manu, whose solution I think was the "cleanest".

  3. I don't think you're being stupid Jake. It's just that there are clearly more clever people than your or I when it comes to this kind of thinking. :-)

    I thought that first solution from Marie was fairly brilliant. Never would have thought of that all day myself.

Your Comments


About This Page

Written by Jake Howlett on Tue 7 Feb 2006

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 »


Here are the external links posted on the same day.

More links are available in the archive »

More Content