Solved

Rolling date column in a Lotus Notes view

Posted on 2013-11-29
28
976 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
Comment
Question by:Terminex
  • 14
  • 14
28 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
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

by:Terminex
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

by:Sjef Bosman
Sjef Bosman earned 500 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

by:Terminex
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

by:Terminex
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

by:Sjef Bosman
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

by:Terminex
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

by:Sjef Bosman
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

by:Terminex
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

by:Sjef Bosman
Sjef Bosman earned 500 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

by:Terminex
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

by:Sjef Bosman
Sjef Bosman earned 500 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

by:Terminex
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

by:
Sjef Bosman earned 500 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)))

Open in new window

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Terminex
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

by:Sjef Bosman
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

by:Terminex
ID: 39697775
Thanks for that.

Australia.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39697822
Working late ... ;-))
0
 

Author Comment

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

Where are you?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
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

by:Terminex
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

by:Sjef Bosman
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

by:Terminex
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

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

Author Comment

by:Terminex
ID: 39705670
Thanks.
have sent it.
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 500 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

by:Terminex
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

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

Sjef (Dutch, short for Joseph)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now