4 Replies Latest reply on Feb 19, 2014 4:59 PM by TimWilliams

    Find what date range a date belongs to

    TimWilliams

      Title

      Find what date range a date belongs to

      Post

           3 tables

           EmployeeInformation-----<TimeRecords>-------PayPeriods

           EmployeeInformation::EmployeeID_pk=TimeRecords::EmployeeID_fk

           PayPeriods::PayPeriodID_pk=TimeRecords::PayPeriodID_fk

           I have a time card layout in EmployeeInformation table with a portal to time records. An employee can press an in or out button to update their daily time at work. The PayPeriods table is a list of start and end dates for pay periods with an associated pay date. 

           When an employee clocks in, a date field is populated with today's date. I would like for that date to be compared to the PayPeriods table to find which pay period it falls in with the end goal being the portal will default to the current pay period but an employee has the option of selecting records associated with previous pay periods without having to enter search dates. Something like a "previous" and "next" button above the portal to time records.

           I'm not sure if the tables are related correctly for this to function. I can't seem to find a way to relate the date field in time records to the date range in pay periods. I would appreciate any advice.

           Thanks

        • 1. Re: Find what date range a date belongs to
          philmodjunk

               How did you set up the date range in pay periods? If you have two date fields: PeriodStart and PeriodEnd, then this relationship will match by range:

               TimeRecords::Date > PayPeriod::PeriodStart AND
               TimeRecords::Date < PayPeriod::PeriodEnd

               This is just one possibility. there are other methods that can be used to match by date range.

          • 2. Re: Find what date range a date belongs to
            TimWilliams

                 Thank you!

                 The date ranges were set up as you assumed. Setting up the relationship the way you stated allowed me to see what date range the time card date falls in. What would be my next step in keeping the time records portal filtered by a selected pay period date range? I've trying to do it with a calculation in the portal set up filter but can't figure it out.

            • 3. Re: Find what date range a date belongs to
              philmodjunk

                   I wouldn't use a portal filter due to problems that then have to be solved to update what you see in the portal.

                   I'd use a relationship similar to the above but with start and end date fields that can be edited. If the pay period is always the same number of days, the End date field can be a calculation field that adds a specific number of days to the start date in order to compute the end date. If you pay BiMonthly, this can still be done but the calculation becomes a bit more complex due to the different numbers of days in different months.

                   In any case, your "next" and "previous" buttons can use a script to assign a different Start date to that start date field in order for the portal to then show a different set of records in that date range.

              • 4. Re: Find what date range a date belongs to
                TimWilliams

                     Thanks for pointing me in the right direction!