5 Replies Latest reply on Oct 7, 2011 12:22 PM by philmodjunk

    Stumped about timesheet solution..



      Stumped about timesheet solution..


      Hi all,

      I'm working on a database for my office. New to filemaker, somewhat familiar with SQL. I'm fairly confused and would appreciate any help.

      We have employees that work on grants. Every month I have to do a report that analyzes how much time they spent on each project and evaluation how much difference there was between what we had in the payroll system and what they actually applied effort to. It's kind of tricky because grants have pro

      My goal is a database solution where they can enter their time and have it calculate out the amounts for me. 

      Right now I have the following tables:

      AF, Grant, Salary, Employee, Project, Time, Monthly time.

      My first problem has been getting the monthly time to sum up correctly per employee per project.

      In Time I have timesheetId, employeeID, projectID, grantID, Hours, Date, Month.

      In monthly time I have monthlytimeID, employeeID, Month, Amount, project

      Right now I'm trying to calculate the amount per employee per month per project. In SQL it would be using select with where clauses but I'm totally unsure of how filemakers calculations work where specificing fields to sum. I've read a lot of posts and am confused. It seems that I need to use table occurances but those don't seem to work. I also tried using global fields mentioned in another solution but it didn't pan out.


      Sorry if this is a stupid question but it's been boggling my mind, I can do a filemaker advance database summary if thats helpful

      Thanks for your help

        • 1. Re: Stumped about timesheet solution..

          Sounds like you need a summary report that groups your Time records by common values so that sub summary parts and summary fields can compute subtotals, but I could be wrong here and there is more than one way to produce the totals you want.

          Here's how to get a report for a specified month's time records with total time computed for each employee further broken down by month. That will give you the basic idea and you can take it from there:

          Start with a list view layout based on Time.

          Add a Sub Summary part when sorted by EmployeeID (or when sorted by the Employee name field in the related Employee table).
          Add a Sub SUmmary part when sorted by ProjectID (Or the project name field in projects)
          If you do not want to see the individual time records, select the body layout part by clicking its label and delete it.

          Define a summary field that computes that total of Hours. Place this field inside the ProjectID (or Project Name) based sub summary part.

          You can put Fields from the related Employee table in the Employee based sub summary part as needed.

          Perform a find on this layout to find all records for a given month. You can use a date range: date1...date2 or wild cards to specify a specific month and year.

          Sort your records in a sort order that sorts them first by EmployeeID (or Employee Name) and ProjectID (Or projectName). You must sort by the same fields you specified in the "when sorted by" settings for the two sub summary parts or these parts will not be visible.

          Here's a tutorial on sub summary reports that you may find helpful. It's an old thread so post any questions about it here rather than in that thread as posts to it won't pop it up in Recent Items where I can easily spot it: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Stumped about timesheet solution..

            Great, I'm working on that right now. I was wondering how I can have the amounts stored that I transfer. I'll need to use them in some pretty convoluted calculations. I have to compare the percentages of effort per project against what they were budgeted as and move them accordingly. Once I realize the goal of correct calculations of the hours per project I need to divide the total hours worked in the month by an employee and have that data saved and compared with the budgeted amounts in my salary table. It's sort of a mind trip figuring out how to get this all to compute correctly.

            Thanks for your help,


            • 3. Re: Stumped about timesheet solution..

              I'm not sure what you mean by "transfer" here.

              You can use summary fields in calculations as long as you use the right functions and understand how they work. Summary fields produce some kind of aggregate value from either all the records in your found set or, under certain conditions such as the sub summary part, a sorted sub set of the found set.

              If you use a summary field in a calculation like you would a regular data or calculation field, it produces the found set based total. You can, however, get a sub total by using the GetSummary function. The GetSummary function looks can be used like this:

              GetSummary ( sTotalHours ; ProjectID )

              will return the same sub total used in the above summary report example, but now you can use it in a more complex expression that combines that sub total with other values. But just like the sub summary part, you have to sort your records by that project ID field. If you sort by Employee, then by project, you get a project sub total for a given employee just like the sub summary part.

              This is not the only option here. You can also compute such aggregate values via references to related records. Sum ( RelatedTable::Value ), for example computes the total of the field in Value over all the records in RelatedTable that are linked to the table where you defined this calculation field. You can look up aggregate functions in FileMaker help to find a number of useful functions of this class including average, max, min and a standard deviation function. You can also refer to a summary field defined in the related table to extract sub totals.

              And you can use a script to capture these subtotals and enter them in a different table. I wouldn't go that route, however, unless you have a definite reason for doing so. Storing such subtotals in their own table denormalizes your data (the same data is now stored in more than one place) and making sure that you have 100% agreement between the two tables is a real pain. We use such a summary table here at Modesto Junk Company, but we do so as a way to greatly speed up summary calculations and data searches that span multiple years of invoice data. The summary table condenses data from several thousand line item records generated each day to less than a dozen summary records so this makes 5 year cross tab style comparison reports pop up considerably faster than if we based them on the original line item table.

              • 4. Re: Stumped about timesheet solution..

                Thanks for your help Phil.. Sorry If I haven't been very clear about the way its supposed to work. I'm taking care of this project on top of my other analyst duties and its been really confusing since I'm used to doing this stuff in excel and access and mysql.


                AF= afID, Account Number, Fund Number, afconcat 
                Grant= grantID, afID, Subject, Grant Name 
                Salary= salaryID employeeID, afID, Amount 
                Employee=employeeID, first name, last name, title, email, phone number 
                project= projectID, project name, subject, grantID, afID 
                Time= afID, date, employeeId, Hours, Month, timesheetID, totaltime(summary) 

                Basically I have accounts/funds that I have grants attached to. I also have projects that may or not be related to a grant but must be related to an account and fund. 

                Each month our employees are paid off accounts and funds according to percentages of effort we estimate.. Because we deal with very strict government regulation with our finances we have to retroactively move their payroll according to their specific amounts of time per project. 

                What I'm hoping to do with the setup I have about is have them enter their time into an IWP form that goes into the time table and calculates out to give me monthly summaries per employee. Ideally these summaries would then feed into a report that compares their actual percentages of effort to the estimated and computes the exact dollar amount that I need to move. I have done this in excel before, but it's been difficult for me to wrap my head around in filemakers calculations. 

                I'm used to doing calculations with SQL where you use select and where clauses to determine what you're using in equations. Here sometimes I'm confused if its grabbing the exact data I want. Sorts/finds with table occurances seems to be the way to filter data but I can't get the concepts straight. I've spent time reading the "missing manual" and a filemaker advance book but I'm still not clear on how to do calculations with selected samples of data. 

                So in order to sum the hours in a month spent by a specific employee on a single project I should use the sub summarys. Then with the getsummary how do I specify the summary in the function that I wish to pull? I've read quite a bit but so far I've only ended up feeling more confused. 

                • 5. Re: Stumped about timesheet solution..

                  "Thinking SQL" often leads you in the wrong direction with FileMaker as it uses methods that are quite different from SQL.

                  I think you have this basic relationship structure:

                                                       |                             |
                                                       -------<Project           -----<TimeByAF

                  I've deduced this from which ID fields are named after which table. ---< means "one to many".

                  I used to work for an aerospace company where we logged our time by our EmployeeID and by the ProjectFundID so this seems a fairly similar situation.

                  One thing that I don't see in all of this is where you'd store any 'estimated' values that you intend to use to compare to and employee's "actual percentage of effort' (total hours on project for that employee?).

                  You seem to be feeling your way in the right direction here. "table occurrences" and Finds are two ways used to filter data, a filtered portal is a third.

                  Here's a description that may help you translate the SQL into FileMaker terms.

                  Unlike SQL that can be used to define relationships between tables on the fly (among other things), Relationships in FileMaker are defined in a "hard wired" fashion in Manage | Database | Relationships by linking "table occurrences" This roughly corresponds with a SELECT * SQL expression including the "join" clauses. The, WHERE and Sorted By clauses however don't have an equivalent here, instead Find and Sort operations either in scripts or with a user's menu selection perform that function on the records in the table and those linked to it via relationship.

                  In Visual Basic, you can use a declared object variable to refer to the "record set" produced by executing such a SQL query. In FileMaker, each layout refers to one Table Occurrence box in Layout Setup | Show Records From. Finds produce a "found Set" that is fairly similar to the record set you are likely familiar with. Each table occurrence has its own found set current record and sort order just like record set objects and you can refer to them by selecting a layout or portal that refers to it. (Think of Portals as MS Access type sub forms.)

                  For more on Table Occurrences, see this tutorial. If interested, there's a link to an older thread that takes an even more technical look at how they work:  Tutorial: What are Table Occurrences?

                  Then with the getsummary how do I specify the summary in the function that I wish to pull?

                  I gave an example in my last post. I'm not sure how to amplify that. How about you spell out one such calculation that you need to do and I'll see if I can't use it as an example in how to produce the desired result. GetSummary is not the only option here.