2 Replies Latest reply on Jan 22, 2014 2:47 PM by JonathanOxborrow

    Finding billable hours



      Finding billable hours


           I've got a database that keeps track of client jobs, employee hours (including sick time and hours working on jobs), and a few other things.  I need to display the percent of time spent weekly by an employee working on client jobs.  There are a specific number of non-billable job numbers set aside for for employees to log sick time, vacation time, etc.  So I need to find out how to get the total number of hours spent working on billable jobs over the total hours worked in any specific week.  Hopefully the description makes sense.

        • 1. Re: Finding billable hours

               Relationships, finds, sorts, filtered portals and ExecuteSQL can all be used to access such subtotals from your records.

               Which is best to use will depend on how this data is organized in your tables and your personal skill set (Know SQL?).

               A summary report, for example could be set up where you perform a find to pull up the desired set or employee hours records (for a given range of dates, for example) and then sort your records by a field that identifies which entries are billable or non-billable. and then summary fields in sub summary layout parts can show the total billable and non-billable time.

               A field to discriminate between records recording billable and non billable hours might use a calculation similar to this:

               Not IsEmpty ( FilterValues ( List ( 1 ; 2 ; 3 ) ; JobNumber )

               might identify non-billable hours if 1 is the job number for sick leave, 2 = vacation and 3 = Jury duty...

               Such calculations can also be used in portal filters and as match fields in relationships.

          • 2. Re: Finding billable hours

                 Thanks for all the help PhilModJunk.  The calculation for filtering will really help, I think that's been my biggest difficulty so far.