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:
@Explode( @TextToTime( @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.
Skip to the 
You can subscribe to an individual
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))))))))
Greetings
Peter
Something like :
min := @Year(@Now)-5;
min*+0:1:2:3:4:5:6:7:8:9:10:11:12:13:14:15;
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?
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).
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.
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.
Start:=1899;
End:=@Year(@Today);
@For(n:=Start;n<=End;n:=n+1;List:=List:@Text(n));
List
For your first problem I came up with almost exactly the same solution as Marie, but she was quicker :-)
This should work...
StartYear := 1987;
@For(n := @Integer(StartYear);
n <= @Year(@Now);
n := n + 1;
years := years:@Text(n));
@Trim(years)
looks like Mats Hasselquist beat me to the same solution.... just added the @integer incase you were pulling from a text field...
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;
@Subset(@Text(list);@Year(@Now)-min+1)
With a @for I would generate the range, like that :
min := @year(MYREFFIELD);
len := @year(@now)-min;
range:=0;
@for(i:=1;i<=len;i:=i+1;range:=range:i);
min*+range
An alternative if you don't like to loop but it's limited to 100 years.
List:=0:10:20:30:40:50:60:70:80:90*+0:1:2:3:4:5:6:7:8:9;
Start:=1999;
End:=@Year(@Today);
Offset:=End-Start+1;
@Subset(@Text(Start+List);Offset)
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];
range:=0;
@for(i:=1;i<=len;i:=i+1;range:=range:i);
bounds[1]*+range
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
list:="0":"1":"2":"3":"4":"5":"6":"7":"8":"9";
fulllist:=list*+list*+list;
numberofyears:=16;
startyear:=1999;
shortlist:=@Subset(fulllist;numberofyears);
yearlist:=startyear+@TextToNumber(shortlist);
@Text(yearlist)
Thanks guys. I have my solution now. Thanks to Manu, whose solution I think was the "cleanest".
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.