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?
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
Hi sjef,
Have found that the @explode is causing the error "incorrect data...etc".
But not sure why.
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.
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.
ASKER
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.
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(@Da te(StartDa te) + "-" + @Text(@Date(EndDate)));
or maybe
txtdates:= @Explode(@TextToTime(@Text (@Date(Sta rtDate) + "-" + @Text(@Date(EndDate));
dates:= @Date(txtdates);
or this, because @Date might just not accept text:
txtdates:= @Explode(@TextToTime(@Text (@Date(Sta rtDate) + "-" + @Text(@Date(EndDate));
dates:= @TextToTime(txtdates):
dates:= @Date(@Explode(@TextToTime
or maybe
txtdates:= @Explode(@TextToTime(@Text
dates:= @Date(txtdates);
or this, because @Date might just not accept text:
txtdates:= @Explode(@TextToTime(@Text
dates:= @TextToTime(txtdates):
ASKER
Hi ,
Both versions;
@Date(@Explode(@TextToTime (@Text(@Da te(StartDa te) + "-" + @Text(@Date(EndDate)));
and
@Explode(@TextToTime(@Text (@Date(Sta rtDate) + "-" + @Text(@Date(EndDate));
Come up with the error "Too many arguments for @Function".
Both versions;
@Date(@Explode(@TextToTime
and
@Explode(@TextToTime(@Text
Come up with the error "Too many arguments for @Function".
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
So putting it together :
daterange:=@Text(@Date(inv oicedate)) + "-" + @Text(@Date(stopdate));
txtdates:= @Explode(@TextToTime(dater ange));
dates:= @TextToTime(txtdates):
months:= @Right("0"+@Text(@Month(da tes)); 2);
years:= @Text(@Year(dates));
result:= @Unique(years + "\\" + months);
result2:= @ReplaceSubstring(result + "."; "01.":"02.":"03.":"04.":"0 5.":"06.": "07.":"08. ":"09.":"1 0.":"11.": "12."; "Jan":"Feb":"Mar":"Apr":"M ay":"Jun": "Jul":"Aug ":"Sep":"O ct":"Nov": "Dec");
result2
It is the value "dates" that comes up with the incorrect data type error.
So putting it together :
daterange:=@Text(@Date(inv
txtdates:= @Explode(@TextToTime(dater
dates:= @TextToTime(txtdates):
months:= @Right("0"+@Text(@Month(da
years:= @Text(@Year(dates));
result:= @Unique(years + "\\" + months);
result2:= @ReplaceSubstring(result + "."; "01.":"02.":"03.":"04.":"0
result2
It is the value "dates" that comes up with the incorrect data type error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(da tes)); 2);
years:= @Text(@Year(dates));
both come up with the error now.
The rest of the formula wont accept the value for "dates".
Comes up with incorrect data type.
ie..
months:= @Right("0"+@Month(@text(da
years:= @Text(@Year(dates));
both come up with the error now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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?
France? UK?
ASKER
Thanks for that.
Australia.
Australia.
Working late ... ;-))
ASKER
I contract, more systems admin than programming.
Where are you?
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).
ASKER
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.":"0 5.":"06.": "07.":"08. ":"09.":"1 0.":"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
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.":"0
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...
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...
ASKER
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
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".
ASKER
Thanks.
have sent it.
have sent it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks sref,
That's fine it works so that's the main thing. Thanks for all your help.
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)
Sjef (Dutch, short for Joseph)
- create a categorized view
- column 1, hidden, sorted, you put an expression that yields "yyyymm":"yyyymm":"yyyymm"
"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.