It appears that you have this relationship:
Projects::projNum = TimeLog::projNum
If you have filemaker 11 or 12, you can use a summary field in TimeLog and filtered portals to display department specific totals for the hours worked on a given project, but if you then need to use these values in calculations, you'll need to use a relationship based method of computing these totals as the filtered portals are pretty much "display only". (If you have FileMaker 10 or older, you'll need to use a relationship based method anyway.)
The filtered portal method:
Define a summary filed, sTotalHours as the total of your Hours field in TimeLog
To see the total Labor hours for your project, put a one row portal to TimeLog on your Projects Layout. Define this portal filter expression:
TimeLog::Department = "Labor"
Put sTotalHours in this single portal row.
Now duplicate this portal but change the portal filter expression to filter on different departments or it's possible to set up a field with a value list of departments and then a single portal can be used, but you first select the desired department from the value list. (This takes a bit of extra fiddling to get the portal to update with each selected department, but saves screen space for other uses.)
The filtered relationship method: (Good for calculations and versions older than Filemaker 11)
Define a calculation field, constLabor as a calculation field. Put "Labor", the department name in quotes as the sole term of it's calculation and select "text" as the calculation field's return type.
In Manage | Database | relationships, make a new table occurrence of TimeLog by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as LaborTimeLog.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
Projects::projNum = LaborTimeLog::projNum AND
Projects::constLabor = LaborTimeLog::department
You can use LaborTimeLog::sTotalHours to get the total labor hours or you can use Sum ( LaborTimeLog::Hours ) to compute the total Labor hours for this project.
You'd repeat this process for each deparment, or, much like the filtered portals, you can use a field with a value list of departments in place of the const... fields and use just one relationship, but can get each total by selecting a department in this field's value list.
PS. there's also a way to set up a summary report on a layout based on the TimeLog table that shows a breakdown of total hours for each department for either just one project or multiple projects. This approach is much simpler to set up but isn't a method you can use with a layout that is set up as a "dashboard" or some such--it works from a layout dedicated to just showing the summary report.
Thanks...the filtered relationship seems to do what I want. The only issue I am having is more of a refresh thing. When I make a change to the time log (which is in a portal), my Sum() field in my main Project table does not update unless I click inside that field on screen. I have other sum fields in this table that seem to update without issue. The only difference with this one is that I'm referencing a self-join table. Could this have something to do with it?
I'd guess that the record was not yet committed, otherwise, it should update like the others. What happens if you click a blank area of the layout after edting in the portal?
(How did you come up with a self join here? What I posted sets this up without self joins...)
The field does not update if I click outside the object on a blank area. It will only update if I click inside the field or if I change records and come back to that record in my Projects table. The other thing I failed to mention is that all of this is happenning inside a tab control. Not sure if that would make a difference.
I guess I must be confused as to what a self-join is then. I thought by definition a self-join is a relationship where both match fields are in the same table???
I went back and changed the sum in my Project field to look at total hours in my TimeLog table. Now obviously it gives me total hours regardless of department, but the field updates instantly. It seems to have something to do with me trying to calculate hours from the filtered relationship set.
THe portal filter is the issue.
A self join is a relationship between two occurrences of the same table--which would be the case if both match fields are defined in the same table, but we don't have that here as we have a link between two different tables: Projects and LaborTimeLog.
The filtered portal would be a factor here. You indicated that you were using the filtered Relationship method so I did not consider that possibility. The filtered relationship does not use a portal filter and your sum would be returning the correct values if you were using that method.
Also, make sure that your sum function is defined in a calculation field--not a number field with an auto-entered calculation.
But, if I use the filtered portal method, How do I get the resulting data into a field in my projects table? I need to used that result in calculations.
My sum field in the Projects database is a calculation field and is set to unstored.
Apologies. I sumhow read your last post as referring to a filtered portal instead of the relationship and was trying to figure out which method you used. Ignore that post please.
The sum function calculations should update automatically. The only thing I can think of that you haven't checked is what return type is specified. Please make sure that number is selected as the calculation field's return type.
No problem. I checked and in the calculation the type is set to number. It is strange because the calculation will update also if I change layouts and come back to the orignal one.
I'm sure I've followed your instructions to a the letter. I created a duplicate of the timelog table and set up the relationships as you mentioned in your original post. I then created the constant field in the Projects table with a calculation field with "labor" in it. I just can't seem to get the formula to recalculate.
I'm not sure why adding this duplicate table causes issues. I even duplicated the timelog table, and only added the
Project::ProjNum = TimeLog2::ProjNum relationship to try to get it to behave the same as my original
Project::ProjNum = TimeLog::ProjNum relationship.
My formula is simply :
sum(timelog2::hours) (does not refresh immediately)
sum(timelog::hours) (the original table) updates immediately.
It seems as though filemaker doesn't like duplicate table.
What version of FileMaker are you using?
version 12 (12.0v1).
I have discovered a work around which I really hate to do unless I can figure out a better way of doing this.
Under the TimeLog portal on my layout, I created a script trigger for "OnObjectExit". Below is the script
RefreshWindow[Flush cached join results].
I have no idea why I should have to do this, but it seems to work. Any thoughts on this?
I created a demo file and noted the following behavior:
If I added a new record in the portal and entered data. Clicking the background (which commits the record) updated my total. If I changed the value in an existing record, it did not.
I then played around with different combinations of commit record and refresh window until I determined that this script would consistently update my totals:
I suggest using the OnObjectSave trigger to perform this script any time you edit a field in the portal that will change one or more of these totals.
Thanks for all the help!