1 Reply Latest reply on May 7, 2015 8:55 AM by philmodjunk

    Running Monthly Totals

    GrahamKeogh

      Title

      Running Monthly Totals

      Post

      I am trying to achieve a running monthly total per job of timesheet hours logged by employees. The time sheets are logged in a timesheet entry table, where each entry relates to a job number. Then on the related job table it shows a total of the amount of hours spent by all employees on that particular job.

      I want to add a field on the jobs table that shows the amount of hours spent in the current month, to see what jobs are busy etc. Just wondering what is the best way of achieving this?

      Thanks in advance

        • 1. Re: Running Monthly Totals
          philmodjunk

          You have multiple options as this is what I call a "selective sum" problem (You want a sum or other aggregate value, but only for some of the available records).

          A calculation field using ExecuteSQL could show this total via a Join and also a Where clause.

          A summary field defined in your timesheet field can be placed inside a one row portal on your job layout with a portal filter that limits the records summarized to those of the current month.

          You an set up a relationship to a new table occurrence of your Timesheet layout and use an additional match field that matches the current month to a month field set up in your timesheet table.

          Here's a sample calculation that could be used for both match fields:

          DateFIeld - Day ( DateFIeld ) + 1

          This computes a date for the first day of the month for all dates in the same month. In the Jobs table, make this an unstored calculation and substitute Get ( CurrentDate ) for "dateField". In your timesheet table, this would be a stored calculation that references the date field that records the date of the timesheet record's hours.