14 Replies Latest reply on May 27, 2011 12:46 PM by philmodjunk

    summing over a date range from a different table

    kashaev

      Title

      summing over a date range from a different table

      Post

      I'm sure this will be easy for most of you...

      I have a time billing DB I'm trying to create.  I have a table of time entries based on employee number and project number...I'm trying to figure out how to create a script that will pull (by date range) all of the time entries for a specific project number and give me sums of the hours spent based on employees.  These sums will then be displayed on the invoice to the client and be used to calculate the total for the invoice.  

      I have the a

      Time entry Table

      Project Table

      Invoice Table

      Each of the tables are related by Project number.

      Any insight would be great..Thanks.

        • 1. Re: summing over a date range from a different table
          philmodjunk

          I don't see an employees table yet you say you want sums of "hours spent based on employees"? Do you just want a project total for the invoice or a breakdown for each employee that worked on the project during that time interval?

          Here's how to get a project total over a specified date range in your invoice record:

          Define Date1 and Date2 date fields in Invoices.

          define this relationship:

          Invoices::ProjectID = TimeEntryByDate::ProjectID AND
          Invoices::Date1 < TimeEntryByDate::Date AND
          Invoices::Date2 > TimeEntryByDate::Date

          TimeEntryByDate is a new table occurrence of TimeEntry.

          Then a Time total can be computed in a calculation field defined in Invoices as: Sum ( TimeEntryByDate::hoursworked )

          • 2. Re: summing over a date range from a different table
            kashaev

            Thanks,  

            Yes, I also have an employees table; however, I didn't think it would be relavant to this calculation because the emplyees are assigned to each project.  Each record of the project table has a set of assigned employees (Emp1, Emp2, Emp3.....up to 8). Yes, the goal is to have a breakdown appear on the invoice of each emplyees hours worked for a date range.  These emplyee totals will then be used to calculate the total for each project's invoice.

            It's this breakdown by emplyee by date range that I'm getting stuck on.

            • 3. Re: summing over a date range from a different table
              philmodjunk

              That need does complicate things quite a bit. To include the break down by employee, you'll need another table that serves as the join between the invoice and the timeEntry tables.

              Either that, or you'll need to pull all your EmployeeID's into 8 matching fields in invoices and define 8 different relationships like the above example but including one more pair of fields to match by employeeID , projectID and date range to TimeEntry--a much more laborious way to do this.

              First, I suggest a desgin change to your existing tables so that you have a more flexible way to assign employees to projects which will also set up the table we need for your invoices:

              Projects----<ProjectAssignments>------Employees

              Projects::ProjectID = ProjectAssignments::ProjectID
              Employees::EmployeeID = ProjectAssignments::EmployeeID

              With this setup, a portal to ProjectAssignments can be placed on your Projects layout to list all assigned employees. A portal to ProjectAssignments on the Employees layout can list all assigned Employees.

              (Coincidentally, this allows you to assign any number of employees to your project rather than being limited to a maximum of 8.)

              Now add this relationship:

              Invoices----<ProjectAssignmentsByDate------<TimeEntryByDate

              Invoices::ProjectID = ProjectAssignmentsByDate::ProjectID
              ProjectAssignmentsByDate::EmployeeID = TimeEntryByDate::EmployeeID
              ProjectAssignmentsByDate::gDate1 < TimeEntryByDate::Date AND
              ProjectAssignmentsByDate::gDate2 > TimeEntryByDate::Date

              The "catch" here is that the date fields need to be part of the relationship between the join table and the TimeEntry table. The global date fields gDate1 and gDate2 meet that need, but you'll have to add script triggers to update the global date fields with the Date1 and Date2 fields defined in Invoices. Now you can define sum calculation fields in ProjectAssignmentsByDate that compute total hours for each assigned employee.

              I'm not totally thrilled with using the global date fields in this way, but it should work. If someone reads this and can see a way to do it without the global fields, please chime in!

              • 4. Re: summing over a date range from a different table
                kashaev

                PhilModJunk,

                Thanks!  I've got the "ProjectAssignments" table set up and working within a portal in Projects.

                HOWEVER, I'm still confused by the second half of your post.  Are the "ProjectAssignmentsByDate" & "TimeEntryByDate" additional tables?   Sorry for being so THICK.

                -kashaev

                • 5. Re: summing over a date range from a different table
                  philmodjunk

                  These are additional table occurences of your existing data source tables. Table Occurrences are the "boxes" in Manage | Database | Relationships and they are listed on the right hand side of Manage | Database | Tables. Data Source Tables are listed on the left hand side of this tables tab and also listed in the tables drop down of Manage | Database | Fields. You can create any number of table occurrences of the same data source table.

                  You may find that this tutorial helps you understand this better: 

                  Tutorial: What are Table Occurrences?

                  • 6. Re: summing over a date range from a different table
                    kashaev

                    Thanks, I've been using relationships, it was just not clear to me that you were talking about a new table occurrance instead of a new table.

                    That worked great.  Thanks!

                    -kashaev

                    • 7. Re: summing over a date range from a different table
                      kashaev

                      OKAY...so I've go the date range summing working (thanks to PilModJunk).  However, when I create my invoice to review before printing I provide a cancel button that simply runs a script to delete the record and return you to the original layout.  But when I run that script it returns an error stating:

                      "This operation cannot be performed because one or more of the relationships between these tables are invalid."  and then closes the database.

                      How do I know what relationships are "invalid".  It seems strange that Filemaker lets you create a relationship if it's not valid.  Any method someone might have for tracking down an invalid relationship?

                      • 8. Re: summing over a date range from a different table
                        philmodjunk

                        What's even more strange is that it closes the database--which this error should not do. Neither should deleting a record trigger a complaint about an invalid relationship as far as can figure here. This may indicate that your file is damaged in some way.

                        If you have FileMaker Advanced, it would be very informative to run your script with the debugger enabled. This would allow you to see exactly where in your script this error occurs. Without advanced, you may want to try inserting some Show Custom Dialog steps at different points with a different message in each dialog so that you can see which dialogs appear before the error occurs so as to narrow down where it happens.

                        Given the possibility of file damage, you may want to recover your file and see what is reported. It's a good idea to test the recovered file to see if the problem recurs even if the recover process does not report finding any problems.

                        Things to keep in mind about Recover:

                        1. Recover does not detect all problems
                        2. Recover doesn't always fix all problems correctly
                        3. Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.
                        • 9. Re: summing over a date range from a different table
                          kashaev

                          Okay, I've run the debugger and done the recover.  I realized that the database was closing after the error because my script was set to close the window after deleting the record.  (oops)

                          Anyway,  The debugger said I have an Error "407" - "One or both match fields are missing (invalid relationship)"   This Layout just has the "invoice" table and the "EmployeeAssignment" table in a portal.  The Matching fields from that relationship are all present in the layout.  

                          Any thoughts?

                          • 10. Re: summing over a date range from a different table
                            philmodjunk

                            What is the script step that executes to produce this error message?

                            • 11. Re: summing over a date range from a different table
                              kashaev

                              There are only 2 commands in the script...

                              1. Delete Record/Request

                              2. close window (Current Window)

                              The arrow in the debugging window points at "close window" as the error.  However I will get the same error from trying to delete the record with the menu commands or on any of the "invoice" layouts.  

                              • 12. Re: summing over a date range from a different table
                                philmodjunk

                                Hmmm, maybe there's a cascading delete for a relationship that is now invalid.

                                You may need to search for all table occurrences of this record's data source table and double click the relatioship lines looking for any delete options that have been selected. You may even have a chain of such deletes where deleting one record in one table deletes related records in another and then the related records to that table...

                                • 13. Re: summing over a date range from a different table
                                  kashaev

                                  You Rock! That was it!

                                  It must drive you nuts to deal with us wanna-be database developers.

                                  Thanks...Don't go too far from your computer, I'm sure you will hear from me again.

                                  • 14. Re: summing over a date range from a different table
                                    philmodjunk

                                    On the contrary, I find it fun to help others. There is a definitely a "puzzle solving" aspect to this, but then I like complicated puzzles. Wink