4 Replies Latest reply on Sep 4, 2014 9:01 AM by philmodjunk

    Staff Holidays

    philmcgeehan

      Title

      Staff Holidays

      Post

      I currently have a Staff table and a related StaffHolidays table.
      In the StaffHolidays table I can add a record with a start date and an end date.
      I also have a PublicHolidays table so it can account for them as well. This all works reasonably well and I can work out how many work days a member of staff has taken.

      However, I now want to be able to select a date range, and it tell me the holidays taken for each member of staff between those dates,
      e.g.

                                                                                                                                                                                                                                   
       FromTo
      Range01/07/201431/07/2014
      Holiday27/06/201405/07/2014

      So, I want to calculate the amount of days holiday within the date range.
      in this example it would be 5 days.

      Hopefully that makes sense, and somebody has done something like this before, or know a better way of managing this.

      Thanks in advance!

        • 1. Re: Staff Holidays
          philmodjunk

          It would be simpler if you used a script so that the user can specify a range for the holiday, but the script then generates one record in Holiday for each holiday date. Then you can match on the range of dates and count the related records. It would also allow you to display the holidays in a typical calendar format.

           

          • 2. Re: Staff Holidays
            philmcgeehan

            Yes, that does seem a better way especially when it comes to using the dates for other calculations.

            So, now I can ask the user to enter a range of dates and a script goes through and creates a new record for that employee for each day between the dates they have entered.
            In the loop, when I create the holiday records, I assign each record the same batch UUID, for filtering and searching.

            How can I display just the start date and end date in a portal from the employee layout?
            I have created a new self-join TO of Holidays from the UUID field, and have a c_Min and c_Max dates. But in the portal it is showing multiple days, with the same start and end date.

            e.g. Start 01/09/2014 - End 04/09/2014 the HolidayUUID portal will show that 4 times.

            How can I just show 1 record per Holiday UUID in the portal? So the above example will just show up once?

            • 3. Re: Staff Holidays
              philmcgeehan

              Nevermind, I've just filtered the portal to display only if DateFrom = c_Min.

              Is that the best way to do it?

              • 4. Re: Staff Holidays
                philmodjunk

                Perhaps you need two tables, one as you originally structured it and one with the individual dates... You create the record in table 1 and the script creates related records in Table 2.