adspmo
asked on
How to show only the actual vacation days in a calendar view
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.
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.
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
Now create the third column which shows as description in the calendar view and hide First two columns.. and you are all set
ASKER
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(dateRangeTextL ist);
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
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(dateRangeTextL
dayOfWeekNumberList := @Weekday(dateRangeDateList
dayOfWeekTextList := @Text(dayOfWeekNumberList)
weekEndList := @Replace(dayOfWeekTextList
dateRangeWithComment := dateRangeTextList + weekEndList;
weekDayDates := @Left(dateRangeWithComment
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
Yes the columns data should be of Date/Time type.
ASKER
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.
https://www.experts-exchange.com/questions/20804266/How-to-Show-Multiple-Date-entries-in-a-Calendar.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/1 2/2003
The second column should show the duration from what I have read
I tried @Text ToTime(VacationDates) @Elements(VacationDates)
REgards
James
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.
https://www.experts-exchange.com/questions/20804266/How-to-Show-Multiple-Date-entries-in-a-Calendar.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/1
The second column should show the duration from what I have read
I tried @Text ToTime(VacationDates) @Elements(VacationDates)
REgards
James
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.
ASKER
I have sent you acopy of the db
Thanx a bunch
James
Thanx a bunch
James
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."
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."
ASKER
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 ))
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 ))
Received your db and by converting the last line to texttotime showed up all the documents in calendar.
ASKER
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
The lookup for vacation days is returning all the vacation days and is not filtering down the holidays by Country
James
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); "" ) );
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);
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); "" ) );
BTW, nice and appealing design .
ASKER
Thanx
ASKER
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); "" ) );
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yippee
Hemanth,
You are killing me on points with my own code here, no?
You are killing me on points with my own code here, no?
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