Title
Omit records in a portal based on date range
Post
Im making a time clock for my employees. I have a record for each employee and a portal that lists all the shifts that relate to that employee. So it shows each shift date, the clock in time, the clock out time and to total time for that shift. above that portal I have the total shift time for all the records shown in the portal. I want to filter that portal so It only shows the records relating to that employee for the current week, id also like to have a start date and end date drop down menu so that I can see the shifts between those two dates.
any ideas?
I would not use a week number global for filtering the portal by week. You would then also need a calculation in the Schedules indicating the week number for every Schedule date but also, as your Schedules records grew and crossed more than one year, filtering to the week number would group two years for an employee in the same week number.
I would use global dates for the filtering. gStart and gEnd in Employee file (both global dates). Then your relationship would be:
Employees::EmployeeID = Schedules::EmployeeID
AND
Employees::gStart <= Schedules::ScheduleDate
AND
Employees::gEnd >= Schedules::ScheduleDate
You can have the gEnd date pre-populate with the ending Saturday (depending upon the date started in gStart) but the User could still manually change gStart and also the gEnd to whatever date they wish (in case they wish to view a larger range such as a full month or year). Auto-enter calculation on gEnd (to pre-populate) would be: gStart - DayOfWeek ( gStart ) +7 (and be sure to uncheck 'Do Not Replace Existing Value...' right below the Calculated Value Specify button.
UPDATE: Your gStart could be a calculation of Get ( CurrentDate ) but you wouldn't ever be able to filter the portal by any other date. As for using drop-downs, of course you could use them. You could also attach a drop-down calendar on the global dates.