Showing multiple start and endate separetly in a view

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
adspmoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HemanthaKumarCommented:
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
adspmoAuthor Commented:
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
qwaleteeCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

adspmoAuthor Commented:
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
adspmoAuthor Commented:
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
HemanthaKumarCommented:
DBLookups doesnot work in views.
0
HemanthaKumarCommented:
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
HemanthaKumarCommented:
Well that wouldn't work too. Only way is to create a field within the doc and store the holiday values...
0
adspmoAuthor Commented:

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
HemanthaKumarCommented:
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
adspmoAuthor Commented:
This is in a field on a form
0
adspmoAuthor Commented:
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
HemanthaKumarCommented:
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
adspmoAuthor Commented:
I can get it to find the right number  after I created a field on the form and looked up there.

0
HemanthaKumarCommented:
So what is the problem here then... I am getting lost here.
0
qwaleteeCommented:
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
adspmoAuthor Commented:
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
HemanthaKumarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qwaleteeCommented:
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
qwaleteeCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.