Solved

Showing multiple start and endate separetly in a view

Posted on 2003-11-18
20
341 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

18 Experts available now in Live!

Get 1:1 Help Now