Solved

Showing multiple start and endate separetly in a view

Posted on 2003-11-18
20
343 Views
Last Modified: 2013-12-18
Hi

This keeps getting better

In a calendar view I need to add the contents of a Wherabouts form
This form has 4 startdate and enddate fields to enter different dates for business travel

I have thought about creating a script that would populate a hidden form for this purpose ,looking at each of the pairs above and then populating a start and end field separately for each of the Whereabouts.

Is there a simpler way of accomplishing this from a view using the original form
This also has to exclude Weekends and Holidays in the Calendar view

Regards


James
0
Comment
Question by:adspmo
  • 8
  • 8
  • 4
20 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9775874
Enable Show multiple values as seperate entries in the calendar's first column and enter this formula

Start := [12-1-2003];
End := [1-1-2004];
Holidays := [12-25-2003];

rem "days we dont want";
remove:="1":"7";

rem "list of all dates in range";
dates:=@Explode(@TextToTime(@Implode(@Text(start:end);"-")));
rem "list of all weekdays in range";
days:=@text(@Weekday(@texttotime(dates)));
rem "remove those we dont want";
weekdays:=@Replace(days;remove;"");
rem "extract weekday dates";
t := @texttotime(@trim(@Replace(dates;dates+weekdays;"")));
@TextToTime(@Trim(@Replace( @Text(t); @Text(Holidays); "")));

~Hemanth
0
 

Author Comment

by:adspmo
ID: 9776051
Thanx I'll ive it a try

Also ,just so you know, I have taken over a faairly complex web enabled application so lots more fun coming down the pipe

Cheers

James
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9776102
For some reason, I thought the calendar type view automatically supported multiple entries as separate items, even if you didn't turn the option on.
0
 

Author Comment

by:adspmo
ID: 9779462
Hi

What would I use as a key for this lookup

I was thinking something along the lines of @IsMember(dateRangeTextList ) or a similar calculation

dateRangeTextList := @Explode(@TextToTime(@Text(StartDate) + " - " + @Text(EndDate)));
holidayDateList := @Text(@DbLookup( "" : "NoCache" ;""; "Holidays" ; 1));



James
0
 

Author Comment

by:adspmo
ID: 9780589
I am using a field named StartDate as the key

It is the first column in a View named Holidays. The column is Sorted Ascending. StartDate is the field name in the Holidays 1st column

It is returning Entry not found in Index

holidayDateList := @Text(@DbLookup( "" : "NoCache" ;""; "Holidays" ; StartDate;1));
@Prompt([OK]; "Value of LastName"; holidayDateList)

0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9781030
DBLookups doesnot work in views.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9781068
Check the help documentation on DBLookup it says

"Usage
This function does not work in column or selection formulas, or in mail agents."

If you want to retrieve the values of holidays from a view. Do this

Create a form (say HolidayList) which acts like profile doc and holds Holiday date List (say hDates)

In the open script of the view,
profilename := "HolidayList";
holidayDateList := @Text(@DbLookup( "" : "NoCache" ;""; "Holidays" ; StartDate;1));
@IF(@isError(holidayDateList); @Return(0); "");
@SetProfileField( profilename ; "hDates" ; holidayDateList );


And now in the column formula use this to get the list back
Holidays := @GetProfileField( "HolidayList" ; "hDates")

This might work for you.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9781135
Well that wouldn't work too. Only way is to create a field within the doc and store the holiday values...
0
 

Author Comment

by:adspmo
ID: 9781172

What I would like to know is do I have to have all the dates for holidays in one field.

Currently I have a document that contains Country and Start and End Date for the Holiday


This is returning only one value the first one it finds How do I get it to return all dates within a range

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


Here it is in context
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);
workDayCount := @Elements(finalDateList);
@Prompt([OKCANCELLIST] : [NoSort]; @Text(workDayCount ); ""; ""; finalTextList)




0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9781482
Is this on view or form ?

Is the view Holidays categorized on Country ? If so make it a flat view and see if you can get all the values
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:adspmo
ID: 9781552
This is in a field on a form
0
 

Author Comment

by:adspmo
ID: 9781579
And it is a flat view first column Country
third column dates


Canada    Thanksgiving   10/10/2003
Canada    Christmas        25/12/2003
Canada    Boxing Day      26/12/2003


In the above scenario if I take holidays tha lapse over Christmas and Boxing day they should be subtracted from the Vacation

So instead of 5 days I would be charged 3

James
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9782104
SO you are getting just one holiday instead of 3 right !

If so I believe your key column is messed up or key value ? Make sure that the Country column is trimmed. And also trim the Country in DBLookup formula


Country := @Trim(cur_country);
holidayDateList := @Text(@DbLookup( "" : "NoCache" ;""; "Holidays" ; Country;3));
0
 

Author Comment

by:adspmo
ID: 9782821
I can get it to find the right number  after I created a field on the form and looked up there.

0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9782935
So what is the problem here then... I am getting lost here.
0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 250 total points
ID: 9783938
adspmo,
> holidayDateList := @Text(@DbLookup( "" : "NoCache" ;""; "Holidays" ; Country;3));
Just to make sure what's coming back ins what you think is coming back...

Create a field named LookupHolidays.  Make it computed for display, type datetime, allows multiple values.  Formula is:

@DbLookup( "" : "NoCache" ;""; "Holidays" ; Country;3)

See if it returns the expected list of dates.
0
 

Author Comment

by:adspmo
ID: 9787574
Yea it returns all the holidays. What I would like it to do is to check the time range and to return only the holidays if any that are between the startdate and the end date

James
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 250 total points
ID: 9787764
Then do this

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);
workDayCount := @Elements(finalDateList);
@Keywords(finalDateList; holidayTextList); ------------ THis will give you the holidays present within the start and end date range
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9805068
No it won't.  It will always return null.  Because by reduction, there is nor overlap between finalDateList and holidayTextList.  Even if you were using the correct variables, it would be, perhaps @KeyWords(finalTextList; holidayTextList) and might need @TextToTime aound that.

The correct variable you are looking for is dateRangeTextList, not finalTextList or finalDateList.

Personally, I never use @KeyWords because it is buggy.  The equivalent using @Replace is:

dateRangeTextListNoHolidays := @Replace(dateRangeTextList; holidayTextList; "");
dateRangeHolidayTextList := @Replace(dateRangeTextList; dateRangeTextListNoHolidays; "");
holidayTextListInRange := @Trim(dateRangeHolidayTextList);
holidayDateListInRange := @TextToTime(holidayTextListInRange)
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9805070
And, of course, there is no need to do ay of this!  As I mentioned, the full formula processing will work even with holidays that are out of range; they will simply be ignored.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

17 Experts available now in Live!

Get 1:1 Help Now