Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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
0
adspmo
Asked:
adspmo
  • 8
  • 8
  • 4
2 Solutions
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now