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 )
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.
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::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::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!
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.
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:
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!
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?
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:
- Recover does not detect all problems
- Recover doesn't always fix all problems correctly
- 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.
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.
What is the script step that executes to produce this error message?
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.
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...
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.