1 2 Previous Next 20 Replies Latest reply on Dec 10, 2012 8:56 AM by Vinny

    Relationship Filtering

    Vinny

      Title

      Relationship Filtering

      Post

           Hello,

           I have (3) tables:

           Utility (table for generic reports layout)

           Employees (ID, Name)

           TimeClockEntries (ID, ClockIn, ClockOut, Calc_TotalTime)

            

           I have a layout based on the utility table, with two filters (globals): StartDate, End Date.  I want to display a portal of Employees who had a time clock entry between the two filter dates AND a total number of hours (i.e. sum of the time clock entries).

           I can get the first part (Employees who had time clock entries between the dates), but I cannot for the life of me figure out how to do the calculation of Sum(Calc_TotalTime).  Every direction I have taken only shows a sum of ALL that employee's time clock entries, and not just the ones within the filtered dates.

           Please let me know if this is something you can help with.

           In other DBMS's I would have written a SQL string with a few where conditions and would be done with it.

           Thanks in advance.

        • 1. Re: Relationship Filtering
          Jade

               Hi Vinny,

               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.

          • 2. Re: Relationship Filtering
            Vinny

                 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... :(

            • 3. Re: Relationship Filtering
              Jade

                   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?

                   Thanks…

              • 4. Re: Relationship Filtering
                Vinny

                     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.

                • 5. Re: Relationship Filtering
                  Vinny

                       Another...

                  • 6. Re: Relationship Filtering
                    Vinny

                         And another...

                    • 7. Re: Relationship Filtering
                      Jade

                           Hi Vinny,

                           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.

                      • 8. Re: Relationship Filtering
                        Vinny

                             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.

                              

                        • 9. Re: Relationship Filtering
                          Jade

                               Hi Vinny,

                               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.

                          • 10. Re: Relationship Filtering
                            Jade

                                 Here is 1 or 3: the relationships graph

                            • 11. Re: Relationship Filtering
                              Jade

                                    

                                   2 of 3: the Sum calculation (Note: TotalHours is just a number field instead of a calculated field as a shortcut for the test.)

                              • 12. Re: Relationship Filtering
                                Jade

                                     3 of 3: the Layout

                                • 13. Re: Relationship Filtering
                                  Jade

                                       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.

                                  • 14. Re: Relationship Filtering
                                    Vinny

                                         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?

                                    1 2 Previous Next