Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to show only the actual vacation days in a calendar view

Posted on 2003-11-20
18
Medium Priority
?
447 Views
Last Modified: 2013-12-18
Hi Guys

I will try to keep this one on track

Now that we have the day count working what I need to show in the calendar view would be only the 3 days. Currently the name of a person shows up on Holidays and Weekends.

I am thinking maybe a field that show only the actual days after the holidays and weekends have been removed and then use that for the calendar.
But then that raises the whole issue of Calendar view requiements.


Any Ideas

Best Regards

James




To create a calendar view, you begin by creating a standard view. You can either complete Steps 1 through 10 for creating a standard view  or you can convert an existing view to a calendar view.
  1.     Create a new view or open an existing view.
  2.     Open the View Properties box.
  3.     In the Style field, select Calendar. A dialog box appears. Click yes to continue.
The first column of a calendar view defines the Date/Time the entry will display and should be hidden.
  4.     Click the first column. In the Column properties box, check Hide column.
  5.     In the Programmer's pane, choose one of the following and enter a value for the first column that will evaluate to both a date and time:
Simple function - Select a value from the list that evaluates to a date and time, for example, creation date.
Field - This displays a list of all the fields in the database. Select a date/time field from the form you will use with the calendar view.
Formula - Write a formula in the Programmer's pane that evaluates to a date and time.
  6.     Open the Column properties box and click the Sorting tab . Choose the sort option Ascending.
Note  If you have a field on your form that allows multiple values, and you want each value to display as separate entries in the calendar view, select the column sorting option "Show multiple values as separate entries" for the first column.  For example, the Mail template uses this technique for displaying repeating events. Otherwise, the entries display under the first matching date only.
  7.     Click the Date and Time tab  of the Column properties box and select Date and time.
The second column of a calendar view defines duration of the entry and should be hidden.
  8.     Create a second column or click  the second column of the existing view. The second column must map to a field or formula on the document that specifies, in minutes, the duration of the event. In the Column properties box, check Hide column.
  9.     Create a selection formula  for the view that will select the documents to be displayed in the calendar view, for example, _Calendar Entry.


0
Comment
Question by:adspmo
[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
  • 8
  • 8
  • 2
18 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9788886
Create a field showing only Vacation Dates and call it as VacationDates.

In The view First and Second Col formula should be VacationDates  and set the view of type calendar. Save it and view in client.

~Hemanth
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9788899
The above setup will show dates in the calendar.

Now create the third column which shows as description in the calendar view and hide First two columns.. and you are all set
0
 

Author Comment

by:adspmo
ID: 9789072
Hi
I followed your Instructions and the calendar comes up blank

Ist column is VacationDates
2nd Column VacationDates
Third Column RequesterName

VacationDates field is currently Text and it shows all the right dates with the following formula

StartDate := @Text(StartDate);
EndDate := @Text(EndDate);
Country := cur_country;
holidayDateList := @Text(@DbLookup( "" : "NoCache" ;""; "Holidays" ; Country;3));


dateRangeText := @Text(StartDate) + " - " + @Text(EndDate);
dateRange := @TextToTime(dateRangeText);
dateRangeTextList := @Explode(dateRange);
dateRangeDateList := @TextToTime(dateRangeTextList);
dayOfWeekNumberList := @Weekday(dateRangeDateList);
dayOfWeekTextList := @Text(dayOfWeekNumberList);
weekEndList := @Replace(dayOfWeekTextList; "2" : "3" : "4" : "5" : "6"; "" : "" : "" : "" : "" : "" + "IS A WEEKDAY");
dateRangeWithComment := dateRangeTextList + weekEndList;
weekDayDates := @Left(dateRangeWithComment; "IS A WEEKDAY");

holidayTextList := @Text(holidayDateList);
workDayDateTextList := @Replace(weekDayDates; holidayTextList; "");
finalTextList := @Trim(workDayDateTextList);
finalDateList := @TextToTime(finalTextList);
finalDateList


Should I @TextToTime the view?

I tried changing the field to a Date?Time and got and error Unable to Interpret

Regards James

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9789628
Yes the columns data should be of Date/Time type.
0
 

Author Comment

by:adspmo
ID: 9790166
The first problem is that the requirement is to show the values from two different forms on a Calendar view. I cannot get one to show properly let alone two forms. This is the only thing that is holding up the completion of this application.

Form One should show only the days in VacationDates field

Form two is more complicated

If the requested type is Sick or Other then only VacationDates Is used, If mission is selected then that is where there is multiple Start and End dates and we have discussed abit about displaying those in a view.

http://www.experts-exchange.com/Applications/Email/Lotus_Notes/Q_20804266.html


Can these(forms) be shown together in one view or should I use two calendar views(hope Not)


I cannot get this to work

I have set the columns to Date/Time

Show multivalues .... is selected

I have used @Text ToTime(VacationDates) as the formula

Vacation Dates shows only the actual days of vacation excluding weekends and holidays
22/12/2003;23/12/2003;24/12/2003

The second column should show the duration from what I have read
I tried @Text ToTime(VacationDates) @Elements(VacationDates)

REgards

James
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9790352
I am not sure what you are doing wrong.. let me take a look at the db.. compress it and send some holidays and test data.
0
 

Author Comment

by:adspmo
ID: 9790575
I have sent you acopy of the db

Thanx a bunch



James
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9795198
The day count code actually includes, as an intermediary calculation, the list of "working days" that are in the "range" -- i.e., the non-holiday weekdays that are within the range of dates listed by the employee as being out.

So, all you need to do is teh same calculation, but stopped at the intermediary step that has all those dates calculated.  (If you want to be efficient, you only calculate each part once, but don't bother for now -- just get it working).

So, create another field with only the finalDateList calculation at the end.  That is the list of "dates that count."
0
 

Author Comment

by:adspmo
ID: 9797394
I have done that with the vacationDates field.The only thing I am not comfortable with is the dblookup key I am using Country as the Key.
The view is 1st column Country, Sort Ascending ,then the holiday dates are in column 3. It is returning all holidays. The country column contains all the countries holiday Canada,United States, United Kingdom. I wouldn't think I need a separate view for each country


I need to show that in a view along with

d1 := @If( StartDate_1 = "" | EndDate_1 = ""; ""; @Explode( @TextToTime( @Text(StartDate_1) + "-" + @Text(EndDate_1) ) ));
d2 := @If( StartDate_2 = "" | EndDate_2 = ""; ""; @Explode( @TextToTime( @Text(StartDate_2) + "-" + @Text(EndDate_2) ) ));
d3 := @If( StartDate_3 = "" | EndDate_3 = ""; ""; @Explode( @TextToTime( @Text(StartDate_3) + "-" + @Text(EndDate_3) ) ));
d4 := @If( StartDate_4 = "" | EndDate_4 = ""; ""; @Explode( @TextToTime( @Text(StartDate_4) + "-" + @Text(EndDate_4) ) ));

@TextToTime(@Trim( d1 : d2 : d3 : d4 ))

0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9797585
Received your db and by converting the last line to texttotime showed up all the documents in calendar.
0
 

Author Comment

by:adspmo
ID: 9797643
Hi see my previous comments


The lookup for vacation days is returning all the vacation days and is not filtering down the holidays by Country


James
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9798254
Here is the modified version of the column formula which excludes holidays from the list and displays only vacation days

First include a COmputed field (multivalue) date type and call it Holidays... THis will be the list of holidays for this user in the country.. With following formula

Country := @If( @IsAvailable(cur_country); cur_country; @Return(""));
t := @Text(@DbLookup( "" : "NoCache" ;""; "Holidays" ; Country;3));
@If(@IsError(t); ""; @TextToTime(@Unique(t)) )

And now for the col formula.


d := @If( StartDate = "" | EndDate = ""; ""; @Explode( @TextToTime( @Text(StartDate) + "-" + @Text(EndDate) ) ));
d1 := @If( StartDate_1 = "" | EndDate_1 = ""; ""; @Explode( @TextToTime( @Text(StartDate_1) + "-" + @Text(EndDate_1) ) ));
d2 := @If( StartDate_2 = "" | EndDate_2 = ""; ""; @Explode( @TextToTime( @Text(StartDate_2) + "-" + @Text(EndDate_2) ) ));
d3 := @If( StartDate_3 = "" | EndDate_3 = ""; ""; @Explode( @TextToTime( @Text(StartDate_3) + "-" + @Text(EndDate_3) ) ));
d4 := @If( StartDate_4 = "" | EndDate_4 = ""; ""; @Explode( @TextToTime( @Text(StartDate_4) + "-" + @Text(EndDate_4) ) ));

vac := @TextToTime(@Trim( d:d1 : d2 : d3 : d4 ));

@TextToTime(@Replace( @Text(Vac); @Text(holidays); "" ) );


0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9798260
BTW, nice and appealing design .
0
 

Author Comment

by:adspmo
ID: 9798317
Thanx




0
 

Author Comment

by:adspmo
ID: 9799289

The follwing was copied in to both hidden columns It shows the d1 - d4 nicely It does post the required Holidays as BLANK, just got to get it not to post Weekends

d := @If( StartDate = "" | EndDate = ""; ""; @Explode( @TextToTime( @Text(StartDate) + "-" + @Text(EndDate) ) ));
d1 := @If( StartDate_1 = "" | EndDate_1 = ""; ""; @Explode( @TextToTime( @Text(StartDate_1) + "-" + @Text(EndDate_1) ) ));
d2 := @If( StartDate_2 = "" | EndDate_2 = ""; ""; @Explode( @TextToTime( @Text(StartDate_2) + "-" + @Text(EndDate_2) ) ));
d3 := @If( StartDate_3 = "" | EndDate_3 = ""; ""; @Explode( @TextToTime( @Text(StartDate_3) + "-" + @Text(EndDate_3) ) ));
d4 := @If( StartDate_4 = "" | EndDate_4 = ""; ""; @Explode( @TextToTime( @Text(StartDate_4) + "-" + @Text(EndDate_4) ) ));

vac := @TextToTime(@Trim( d:d1 : d2 : d3 : d4 ));

@TextToTime(@Replace( @Text(Vac); @Text(holidays); "" ) );
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 2000 total points
ID: 9799439
For weekend calculations you have to apply above weekend formula calc

So the complete formula would be this

d := @If( StartDate = "" | EndDate = ""; ""; @Explode( @TextToTime( @Text(StartDate) + "-" + @Text(EndDate) ) ));
d1 := @If( StartDate_1 = "" | EndDate_1 = ""; ""; @Explode( @TextToTime( @Text(StartDate_1) + "-" + @Text(EndDate_1) ) ));
d2 := @If( StartDate_2 = "" | EndDate_2 = ""; ""; @Explode( @TextToTime( @Text(StartDate_2) + "-" + @Text(EndDate_2) ) ));
d3 := @If( StartDate_3 = "" | EndDate_3 = ""; ""; @Explode( @TextToTime( @Text(StartDate_3) + "-" + @Text(EndDate_3) ) ));
d4 := @If( StartDate_4 = "" | EndDate_4 = ""; ""; @Explode( @TextToTime( @Text(StartDate_4) + "-" + @Text(EndDate_4) ) ));

vac := @TextToTime(@Trim( d:d1 : d2 : d3 : d4 ));

dateRange := @TextToTime(@Replace( @Text(Vac); @Text(holidays); "" ) );


dateRangeTextList := @Explode(dateRange);
dateRangeDateList := @TextToTime(dateRangeTextList);
dayOfWeekNumberList := @Weekday(dateRangeDateList);
dayOfWeekTextList := @Text(dayOfWeekNumberList);
weekEndList := @Replace(dayOfWeekTextList; "2" : "3" : "4" : "5" : "6"; "" : "" : "" : "" : "" : "" + "IS A WEEKDAY");
dateRangeWithComment := dateRangeTextList + weekEndList;
weekDayDates := @Left(dateRangeWithComment; "IS A WEEKDAY");

holidayTextList := @Text(holidays);
workDayDateTextList := @Replace(weekDayDates; holidayTextList; "");
finalTextList := @Trim(workDayDateTextList);
finalDateList := @TextToTime(finalTextList);
finalDateList


0
 

Author Comment

by:adspmo
ID: 9799730
Yippee
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9804940
Hemanth,

You are killing me on points with my own code here, no?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

721 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