5 Replies Latest reply on Aug 13, 2013 8:36 AM by philmodjunk

    Getting hours worked



      Getting hours worked


           I have a table that saves all the hours worked from one person. The problem is I want to have it get it from that week/month/total and not have it only the total. Is there a way to get the records from that week or month. Thanks - Austin

        • 1. Re: Getting hours worked

               There are a number of possible approaches depending on what you want to see on the screen, but a summary report is one possibility. A summary report can list the hours worked by one person or several with subtotal--based "break downs" for each person (or just 1) for each week and/or month.

               A single summary field can compute the total hours and the sub totals. To get sub totals, you place the summary field in a sub summary layout part that is "sorted by" a field that has the same value for all the records in the group for which you need a sub total.

               One way to group your records by month is to set up a calculaition field, cMonth that is set with Date as the result type and a calculation such as:

               YourDateField - Day ( YourDateField ) + 1

               This computes a date for the first day of the month for all dates in that month so you can sort by this field to group your records by month so that a sub summary part "when sorted by" cMonth can be used with the summary field to get your monthly total.

               Here's a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Getting hours worked

                 Is there a way to put it into a layout sorta like this one?

            • 3. Re: Getting hours worked

                   The summary report format is easier to set up, but this can be done either with several relationships, filtered portals or by using ExecuteSQL() (FileMaker 12 or newer only).

                   All of the above will require separate records for each date worked with a number field or a calculation field (Hours) that returns number to access for the hours worked on that date as well as two tables: Employees: One record for each employee and HoursWorked--one record for each day that a given employee works.

                   For Filtered portals, define a summary field in the HoursWorked table to compute the total of Hours.

                   Use this relationship:

                   Employees::__PkEmployeeID = HoursWorked::_fkEmployeeID

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   Place a one row portal on your Employees layout to HoursWorked, Define this portal filter: Year ( Get ( CurrentDate ) ) = Year ( HoursWorked::Date ), and place the summary field inside this one row portal to show the total hours worked Year to date.

                   Use the same method with this portal filter: Month ( Get ( CurrentDate ) ) & " " & Year ( Get ( CurrentDate ) ) = Month ( HoursWorked::Date ) & " " & Year ( HoursWorked ) to show a monthly total. TO get the previous month, just subtract one from the month on both sides of the = in this expression.

                   The main limitation to this approach is that the totals in the summary fields are display only. The values cannot be accessed in a calculation--say in a field, script step or a conditional format on an object outside of the portal. You can still get total wages here, however, if you compute Hours * EmpWage in the HoursWorked table and use a summary field for that total as well.

                   To get values that are accessible for calculations, scripts, etc., you'd need to use one of the other two methods mentioned.


              • 4. Re: Getting hours worked

                     When I put the -1 after the month its not working :/ How is it suppose to look?

                • 5. Re: Getting hours worked

                       Month ( Get ( CurrentDate ) ) - 1 & " " & Year ( Get ( CurrentDate ) ) = Month ( HoursWorked::Date ) & " " & Year ( HoursWorked )