Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Rolling date column in a Lotus Notes view

Posted on 2013-11-29
Medium Priority
1,089 Views
Last Modified: 2013-12-11
Hi,

Is it possible to create a column in a view that shoes a rolling month base on a date field.

For example,
If field A = "Monthly budget",
and field B = date field for the start of the budget,

Then could we get a recurring month to show up in the column based for each month for 12 months from the start date in field B.
So the categorized column in the view would show Feb , Mar, Apr, and so on.

We could then see based on one document, what each each monthly budget will be and not have to create a new doc for each month.

Is this possible?
0
Question by:Terminex
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 14
• 14
28 Comments

LVL 46

Expert Comment

ID: 39684999
Yes, I suppose it's possible:
- create a categorized view
- column 1, hidden, sorted, you put an expression that yields "yyyymm":"yyyymm":"yyyymm", i.e. 4 digits year and 2 digits month, for the duration of the project, e.g.
"201311":"201312":"201401"
- column 2, categorized, should contain a formula that yields "month year":"month year":"month year" or in a different format, for the duration of the project, e.g.
"November 2013":"December 2013":"January 2014"

When categorized, the document is shown for every single category.
0

Author Comment

ID: 39686521
Hi sjef,

The view will contain loads of different documents with different start dates for the monthly budgets for years to come.

It has categorized month column based on the start date field. So if the start date is 12 dec 2013, then it only shows up in the Dec category. management would like it to show in each month categorized month for 12 months from the start date.
I can't figure out the formula to populate a hidden field on the doc to contain "DEC":"Jan" ...etc using the start date field + 12 months
0

LVL 46

Assisted Solution

Sjef Bosman earned 2000 total points
ID: 39686915
Only "Dec":"Jan" etc. wouldn't be enough, you have to include the year otherwise the sorting won't work or it'll look weird in the view. What I don't understand is that you want to see only 12 months and not a whole project. I can understand that more than a year isn't really useful, but what if a project lasts for only 5 months? Do you want to see 12 months there as well? My suggestion would be to use the start and end dates in a document, assume the budget is split equally over all months. If there is no end date, you'd better fix one. Since you really have to avoid the use @Now or @Today in a view, it won't be easy to supply some date. Best is probably
@Adjust(StartDate; 1; 0; 0; 0; 0; 0)

Suggested formula (theoretically, so not tested!):

dates:= @Date(@Explode(@TextToTime(@Text(StartDate;"D0S0") + "-" + @Text(EndDate;"D0S0")));
months:= @Right("0"+Text(@Month(dates)); 2);
years:= @Text(@Year(dates));

result:= @Unique(years + "\\" + months);

If you need names of months:

result2:= @ReplaceSubstring(result + ".'; "01.":"02.":"03.":"04.":"05.":"06.":"07.":"08.":"09.":"10.":"11.":"12."; "Jan":"Feb":"Mar":"Apr":"May":"Jun":"Jul":"Aug":"Sep":"Oct":"Nov":"Dec");
0

Author Comment

ID: 39689176
Hi sjef,

I have fixed an end date just to see how this would work.

Using the above formula (note- second line I replaced "Text" with @Text)

For a doc with start date 1/12/2013 and end date 1/12/2014 the results in column are

result = "ed"
result2 = "Incorrect data type for operator or @Function: Time/Date expected\ed"

So I'm not sure why the error or where the result "ed" is coming from?
0

Author Comment

ID: 39689197
Hi sjef,

Have found that the @explode is causing the error "incorrect data...etc".
But not sure why.
0

LVL 46

Expert Comment

ID: 39689405
Heh, "ed" is the last two characters of "Time/Date expected". :-)

What type are the values of StartDate and EndDate in the document? If their values are not Date values, you can omit the @Text. But they do have to be correct date strings.

If you have to test a formula, put it in an agent, set StartDate and EndDate at the beginning, and use @Prompt to show the (intermediate) results. Don't forget to set Target to None.
0

Author Comment

ID: 39691695
Hi sjef,

The fields are date fields.
Have done a test for each component of the formula.
"dates" comes up with the error straight off.
0

LVL 46

Expert Comment

ID: 39691953
Try this line then:

dates:= @Date(@Explode(@TextToTime(@Text(@Date(StartDate) + "-" + @Text(@Date(EndDate)));

or maybe

txtdates:= @Explode(@TextToTime(@Text(@Date(StartDate) + "-" + @Text(@Date(EndDate));
dates:= @Date(txtdates);

or this, because @Date might just not accept text:

txtdates:= @Explode(@TextToTime(@Text(@Date(StartDate) + "-" + @Text(@Date(EndDate));
dates:= @TextToTime(txtdates):
0

Author Comment

ID: 39694807
Hi ,

Both versions;
@Date(@Explode(@TextToTime(@Text(@Date(StartDate) + "-" + @Text(@Date(EndDate)));

and

@Explode(@TextToTime(@Text(@Date(StartDate) + "-" + @Text(@Date(EndDate));

Come up with the error "Too many arguments for @Function".
0

LVL 46

Assisted Solution

Sjef Bosman earned 2000 total points
ID: 39694906
Oh, some parentheses are wrong... hence the error.

@Date(@Explode(@TextToTime(@Text(@Date(StartDate)) + "-" + @Text(@Date(EndDate)))));

and

@Explode(@TextToTime(@Text(@Date(StartDate)) + "-" + @Text(@Date(EndDate))));

More splitting up to make clear what's supposed to happen:

daterange:=@Text(@Date(StartDate)) + "-" + @Text(@Date(EndDate));
txtdates:= @Explode(@TextToTime(daterange));
0

Author Comment

ID: 39694946
Hi,

So putting it together :

daterange:=@Text(@Date(invoicedate)) + "-" + @Text(@Date(stopdate));
txtdates:= @Explode(@TextToTime(daterange));
dates:= @TextToTime(txtdates):

months:= @Right("0"+@Text(@Month(dates)); 2);
years:= @Text(@Year(dates));
result:= @Unique(years + "\\" + months);
result2:= @ReplaceSubstring(result + "."; "01.":"02.":"03.":"04.":"05.":"06.":"07.":"08.":"09.":"10.":"11.":"12."; "Jan":"Feb":"Mar":"Apr":"May":"Jun":"Jul":"Aug":"Sep":"Oct":"Nov":"Dec");
result2

It is the value "dates" that comes up with the incorrect data type error.
0

LVL 46

Assisted Solution

Sjef Bosman earned 2000 total points
ID: 39695039
I guess we don't need the additional @TextToTime...

daterange:=@Text(@Date(invoicedate)) + "-" + @Text(@Date(stopdate));
dates:= @Explode(@TextToTime(daterange));

months:= ...
0

Author Comment

ID: 39697235
that now works and shows up as the exploded dates.
The rest of the formula wont accept the value for "dates".

Comes up with incorrect data type.

ie..
months:= @Right("0"+@Month(@text(dates)); 2);
years:= @Text(@Year(dates));

both come up with the error now.
0

LVL 46

Accepted Solution

Sjef Bosman earned 2000 total points
ID: 39697730
The additional @TextToTime is required...

This code works, in a formula agent here:
``````invoicedate:= [3/8/2013];
stopdate:= [6/10/2013];

daterange:=@Text(@Date(invoicedate)) + " - " + @Text(@Date(stopdate));
txtdates:= @Explode(@TextToTime(daterange));
dates:= @TextToTime(txtdates);

months:= @Right("0"+@Text(@Month(dates)); 2);
@Prompt([OK];"months"; @Implode(@Text(months)));
years:= @Text(@Year(dates));
@Prompt([OK];"years"; @Implode(@Text(years)));
result:= @Unique(years + "\\" + months);
@Prompt([OK];"result"; @Implode(@Text(result)));
result2:= @ReplaceSubstring(result + "."; "01.":"02.":"03.":"04.":"05.":"06.":"07.":"08.":"09.":"10.":"11.":"12."; "Jan":"Feb":"Mar":"Apr":"May":"Jun":"Jul":"Aug":"Sep":"Oct":"Nov":"Dec");
@Prompt([OK];"result2"; @Implode(@Text(result2)))
``````
The prompts have to be removed of course, in your final version. By the way, how do you write your dates, what is 1 September 2013 in your local format? 1-9-2013, or 9/1/2013 or 1/9/2013?
0

Author Comment

ID: 39697751
Great that works!! Finally.

Now I just have to work out how to sort the months in the correct order.

Date format is 1/9/2013
0

LVL 46

Expert Comment

ID: 39697766
That you can do with a hidden, sorted column to the left of the categorized column, much like the current one, but not categorized and with the option to put multiple values on separate lines selected. The formula is the same as for the current column, except that you need the result value and not result2. Oh, yes, you can leave the backslash out if you want...

France? UK?
0

Author Comment

ID: 39697775
Thanks for that.

Australia.
0

LVL 46

Expert Comment

ID: 39697822
Working late ... ;-))
0

Author Comment

ID: 39697897
I contract, more systems admin than programming.

Where are you?
0

LVL 46

Expert Comment

ID: 39697915
Me France, 95% programming (50% XPages, 45% old-school Notes). See my EE-profile (and my website). You can create your own EE-profile if you want one, can be practical in case someone needs to know your environment (O/S, Notes/Domino versions, the like).
0

Author Comment

ID: 39705563
Hi sref,

I have tried to do the sort on the hidden column as you said.
Could not get it to work.
Even tried using result2 with this difference,

result2:= @ReplaceSubstring(months+ "."; "01.":"02.":"03.":"04.":"05.":"06.":"07.":"08.":"09.":"10.":"11.":"12."; "A":"B":"C":"D":"E":"F":"G":"H":"I":"J":"K":"L");

See attached pics for the hidden column ....one is with the above "result2"and the other is just using "result".
Have tried just using result:= @Unique(months); and that made no difference.
48.bmp
2013-12-09-182048.bmp
0

LVL 46

Expert Comment

ID: 39705604
Did you select the option in the hidden column to put multiple values on separate lines?

If you happen to have a small sample database, with a few documents and the view you're working on, you could send me a copy:
- open the ACL, default=manager,
- no encryption,
- close Notes,
- zip the nsf
- reopen Notes and send me the db (my mail address is in my EE profile)

Oh, by the way: try to attach jpg, gif or png images, as they are compressed to 10-15% of the same BMP image file...
0

Author Comment

ID: 39705614
Yes I made that selection.

Cannot find your mail address in the EE profile.
Only thing there is a web address to a French website
0

LVL 46

Expert Comment

ID: 39705649
Under About me, see "mails or Requests for Proposal".
0

Author Comment

ID: 39705670
Thanks.
have sent it.
0

LVL 46

Assisted Solution

Sjef Bosman earned 2000 total points
ID: 39705967
Thanks, received.

It seems impossible to do. I combined columns 1 and 2, so now it produces "03 March" in one column instead of 03 in the first (hidden) column and March in the second. I hope it's acceptable...
0

Author Comment

ID: 39710560
Thanks sref,

That's fine it works so that's the main thing. Thanks for all your help.
0

LVL 46

Expert Comment

ID: 39710791
You're welcome (even if you spelled my name incorrectly ;-))

Sjef (Dutch, short for Joseph)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Weâ€™ve all felt that sense of false security beforeâ€”locking down external access to a database or component and feeling like weâ€™ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many wâ€¦
###### Suggested Courses
Course of the Month11 days, 6 hours left to enroll

#### 719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.