8 Replies Latest reply on May 26, 2017 8:49 AM by fmpdude

    overlap rate of an employee


      Hello, i am new to filemaker. i am working on a project of leave management system. is it possible in filemaker?


      My idea is creating a employee table, a vacation table.


      employee table stores the data of an employee and the vacation table stores the data of leave of employee


      how could i calculate the overlap rate(with other colleague) of an employee in a department.


      Any approach is welcome

        • 1. Re: overlap rate of an employee

          Business rule: Overlap rate.


          So, for clarification:


          1. Please define that business rule first.


          2. Include an example of two employees' data and the results you want to get.

          • 2. Re: overlap rate of an employee

            Sorry for unclear description.

            overlap rate = number of people in a particular department taking leave on particular day/total number of people in a department *100%


            For example,


            John: take annual leave on 23/5/2017 to 28/5/2017

            Kitty: take annual leave on 25/5/2017 to 1/6/2017


            Total number of people in their department= 10

            so the rate should be 2/10*100% =20% on 24/5/2017


            is it better to calculate the rate per day?

            i know there is another approach that in a particular date range say from 23/5/2017 to 1/6/2017, there are 2 of them taking a leave so the rate should be 20%


            which one is better in FileMaker?

            • 3. Re: overlap rate of an employee

              That's a good clarification. Thanks.


              So, how about a simple ExecuteSQL statement?


              (click on image below if you can't see all of it.)

              Here, I've "hard-coded" the start and end dates (last two parameters on the right of the ExecuteSQL), but in your application, if this is correct, you would use fields so you could enter ad-hoc dates to find employees that fall into the desired date range.


              Finally, you'd need to do a little math to get your percentage with the "2" value above, but you now have the correct count of employees who fall in the date range.


              You could also do this without SQL.


              Does this work?  Depending on exactly what you need, you might need to use an "OR" instead of an "AND" in the expression above.


              HOPE THIS HELPS.

              1 of 1 people found this helpful
              • 4. Re: overlap rate of an employee

                yes. it is helpful. Thank you. I would like to ask how to calculate the total number of department. Could i have a field to calculate total number of people in a specific department. I try to use a counter field and summary field to calculate, but it is not working as not all the staff in a department take a leave. Do you have any suggestion?


                Do i need to modify the query? Will it overlap with other departments.

                • 5. Re: overlap rate of an employee

                  If your department count is for the table you're in, as in not a related table, use a Summary field in FMP.  If the count for department is a related table, then COUNT(Primary Key) should work fine.

                  • 6. Re: overlap rate of an employee

                    i create a department field in employee table to record the department of an employee. Then i create a counter field(calculation field) If department = "HR" ; 1, and summary field for this counter field, total people in HR could be calculated.


                    my report is based on vacation table which is related to the employee table. When i put the summary field in the layout, it is not the total number of people in HR. What was going wrong?

                    • 7. Re: overlap rate of an employee

                      The summary field on a report will show different values depending on where on the report you put it.


                      Drag the summary field around and you'll see what I mean.




                      Or, you could just create a calculated field with a simple SQL statement.


                      Something like ExecuteSQL("select count(*) from department"; "";"")

                      • 8. Re: overlap rate of an employee

                        Or, ...


                        If you have FMPA, you could create a custom function that calculates your percentage something like this:


                        Let (


                        pct_overlap =(ExecuteSQL("select count(*) from <table> where <using date logic shown in previous posting>";"";""; "<insert date1 here>"; "<insert date 2 here>") / ExecuteSQL("select count(*) from department";"";"")) * 100






                        Using that custom function technique, you wouldn't even need to create a field, calculated, summary, or otherwise. Just call the function in any field (or data viewer) anywhere you needed the overlap value.


                        HOPE THIS HELPS.