Solved

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

Posted on 2003-11-20
18
441 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
  • 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
 
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
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

 
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 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

12 Experts available now in Live!

Get 1:1 Help Now