We help IT Professionals succeed at work.

Create view with a date range seperating each date by month

kali958
kali958 asked
on

I have an attendance database that tracks associates times out of the office. Currently I do monthly reports by the start date of the request. That is fine for some areas, but others are running into an issue.

When someones time off runs over two months, they want to be able to seperate out that request to reflect the correct time off during the month. For example. Someone takes vacation from 6/28/2010 to 07/09/2010 and return on 7/12.

I have the code right now that will explode the dates and subtract out the weekends and holiday to get the correct number of days off. So, this would be a total days of 9 days out of the office, minus Sat. Sun and the observed holiday on 7/5.

I need to be able to create view or something, so that it would display the correct time used each month.  So it would say for June that the associate used 24 hours and in July they used 48 hours.  

I have a field right now to calulate the days with the code:

tDateRange := @Date(@TextToTime(@Text(DISP_Start) + "-" + @Text(DISP_END)));
tHolidayList := "01/01/2010":"05/31/2010":"07/05/2010":"09/06/2010":"11/25/2010":"12/24/2010":"01/01/2011";
tDateList := @If(tDateRange = ""; ""; @Date(@TextToTime(@Explode(tDateRange))));
tWeekday := @Text(@Weekday(tDateList));
tWkDayDate := tWeekday + "^" + @Text(tDateList);
tSats := @Right(tWkDayDate; "7^");
tSuns := @Right(tWkDayDate; "1^");
tList := @Trim(@ReplaceSubstring(@Text(tDateList); tSats : tSuns; ""));
tDays := @Trim(@Replace(tList; tHolidayList; ""));
@TextToTime(@Text(tDays))

But now I am struggling with how to extract the month from the list and have it display correctly. Each time I try to get the month to extract from this, I get either a Text Expected error or it will only convert the first date in the list to the month and ignores the rest.

I am only having this issue on actual full days out of the office. Since Partial Day requests are same day, this function is not needed.

Any ideas? I appreciate the help as usual! Thanks
Comment
Watch Question

Commented:
There are 2 issues here. One is to evaluate the # of days and the other is to display the #s on a view.
It may be easier to evaluate the # of days with @transform.
tDayCountList:=@transform(tDateRange;onedate;
   @if(@weekday(onedate)=1:7;0
   @text(onedate)=tHolidayList;0;
   1
)  
tDayCountList will now be a list of 0 and 1s. 0 if it is a holiday or weekend and 1 if it is anything else. @sum will give you the total.  
The bigger issue is displaying it in a view. Notes views just don't do aggregates well. I can only assume you want 1 line for each month. Simple enough to categorize by month but not so easy to create a column with just a number associated to the category. It would be a total for the whole document.
I have 2 suggestions.
  1. Create 1 document for each month.  This is the easiest for the view It really would be a simple categorized view with a summed column. It may need a change in how it is entered.
  2. Use a list in the view column. Using your example above, the list would look like this.
    "201006,48":"201007,48". This would be ugly in the view itself but may work if you are exporting into excel or other.

Author

Commented:
Would it be simpler to just use a hidden field on the form called months like the one that I have on the form called dates with the code on it?

Could I then use that field to pull the date list from the dates field and put the months there in a list?

I dont have to do this in a view if I can use the form.

Commented:
Using a form or view is your choice. The formula will work in a field also.

Author

Commented:
Then I am a little lost, I have a code currently that counts the total days out of the office, and then I have the dates field that displays the actual dates they are out of the office. is there away to create a month field that would take the value of the dates field just display the month of each date in the list?
Commented:
I am not too sure what you want but maybe this is it.
First the month field.
tMonthList:=@unique(@text(@month(tDateRange)));
tMonthList should be a unique list of the months in the range.
Now the count of days. Add another transform to the above formula.

tDayCountList:=@transform(tMonthList;onemonth;
  @sum(
     @transform(tDateRange;onedate;
       @if(@weekday(onedate)=1:7;0
          @text(onedate)=tHolidayList;0;
          @text(@month(onedate))=onemonth;1;
          0
       )
     )
  )
)
tDayCountList should be a list with the count of days that match the list in tMonthList. These should be easy enough to display side by side on a view.      

Commented:
Side by side on a Form is what I intended to say.

Author

Commented:
Awesome - thanks for the help.

Author

Commented:
Brilliant - that is totally what i was looking for. Now it displays the months and I can seperate out what I need to.

Thank you!

Commented:
Glad that it is what you needed. Thanks for the points.
I have not tested to code. Just typed it in free hand. Did it work out of the box? How much did you need to change?

Author

Commented:
I did not need to change much, just tweaked to meet my fields and display the way I wanted to.

I was getting way over complicated in my code and it was helpful to "talk" it thru with someone. But now I just have to get the view to display the data and I think I can do that. Since they are full day requests, I do not need to parse the time, since it is a default of 8 hours. I can do that in one column and just default the number but now i can at least group the dates by month for the manager.

Thanks again for your quick responses also!