Link to home
Start Free TrialLog in
Avatar of Terminex
Terminex

asked on

Rolling date column in a Lotus Notes view

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?
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

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.
Avatar of Terminex
Terminex

ASKER

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
SOLUTION
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Hi sjef,

Have found that the @explode is causing the error "incorrect data...etc".
But not sure why.
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.
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.
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):
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".
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?
Thanks for that.

Australia.
Working late ... ;-))
I contract, more systems admin than programming.

Where are you?
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).
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
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...
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
Under About me, see "mails or Requests for Proposal".
Thanks.
have sent it.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks sref,

That's fine it works so that's the main thing. Thanks for all your help.
You're welcome (even if you spelled my name incorrectly ;-))

Sjef (Dutch, short for Joseph)