1. The Sum() function works on related tables, repeating fields, or one or more specified fields of the current record. It does not return the sum of a column in the found sets of the Layout's table. In your case, you can add a duplicate TimeClockEntries table occurrence (TO) to the Relationships Graph and relate it to your TimeClockEntries table using the cartesian (x) operartor with the ID's. Then use the Sum() function on the related (duplicate) TO.
2. If you have FMP 12, you may want to try out the new ExecuteSQL function. It has some limitations but may work well for you in this case.
I didn't mention that my timeclock table has a foreign key to my employees table (employee ID). The problem that I am having is I'm not sure how to only show those employees with timeclock events in a time period AND get the sum of total time for that employee within that time period.
I am using a calf field for the sum function inside the employees table, and I thought by connecting the utility table to the timeclock table (and filtering through relationships), and then connecting the employee table to the timeclock it would only sum those events filtered. In my portal of the employees table it does only show the employees which have time data within the filtered relationship but the calculation sums up all clock events.
I'm still very confused as to where I would put the sum calculation and how to structure the to's. I thought I understood fielmaker better than this... :(
You're not alone being confused… ;-)
What table occurrence is the portal based on?
Is the calc field for the Sum(TimeClockEntries::Calc_TotalTIme) on the Employees record unstored?
Can you post an image of the relationships graph between Utilty, Employees, and TimeClockEntries?
Here are some pictures. There is a pic of the layout which contains my portal.
The portal ends up showing those employees with time clock entries within the dates correctly, BUT the sum calculation adds up ALL timeclock entries. I want the sum to only add up the filtered timeclock entries...
Thanks in advance for your help.
Sorry for the delay.
The first thing I notice is that the CALC_Filetered SUM Hours field in the Employees table is based on TimeCalcEntries 2. That TO is not filtered by the Filter_Start and Filter_End. It will calculate the total hours for an employee not just the sum of the hours within the date range. Try setting the calculation to TimeCalcEntries::CALC_TotalHours instead.
Also, the relationships to the Employees appear as many-to-many instead of many-to-one. I would delete the relationship between TimeCalcEntries and Employees and add a duplicate TO of Employees (Employees 2). Next, relate TimeCalcEntries to Employees 2. Then base the portal on Employees 2 instead of Employees.
I'll try to create a similar test file this morning.
I updated the CALC_TotalHours formula to sum the total hours from the TimeCalcEntries TO instead of TimeCalcEntries 2. Nothing new happened - it's still totalling all timeclockentries (i.e. not filtered). I have previously tried this with no success.
I updated the relationships to be many to one. This was many to many because I didn't set the ID fields to unique in my test file (they are set to unique in my actual database).
What I have after updating the two items above is what I believe to be correct. Everything I understand about databases and the way filemaker works is not making sense at this point.
Note: To rule out other calculation issues, I made the field CALC_TotalHours in my Timeclockentries table a number field, and manually put values in for it. In the production version, it will calculate by way of subtracting timestamps, etc. Just wanted you to be aware.
If you want my file I can send it to you.
Good news. I'm getting the right answers for the Sum (I think) by relating the Utility to the Employee with a one-to-one relationship.
I'll post the images in a few minutes.
I entered just one employee but with time clock entries for yesterday (3 hours) and today (8 hours).
When I set the start/end timestamps on the Utility to Dec. 9th., it shows 3 in Employees 2::Hours.
When I set the start/end timestamps to Dec. 10th., it shows 8 hours.
With start set to Dec 9th. and end to Dec 10th., it shows 11 hours.
I'm using "Employees" as the global Table Names in both Utility and Employees but any constant value would do.
I kind of see what you are trying to do. Which fields are you using in your layout? (i.e. from what TO's)
I tried the sum field from either the Employees TO or the Employees 2 TO, both of them seem to give the same result.
The results are good except when my dates range are set to capture all the timeclock entries. In this case, the sum is the sum of all the entries, not just for that employee.
Are you getting the same result?