Solved

Showing multiple start and endate separetly in a view

Posted on 2003-11-18
20
348 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
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.

 

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
 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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