9 Replies Latest reply on Aug 10, 2011 2:19 PM by philmodjunk

    summing a field by a date

    mikedorn

      Title

      summing a field by a date

      Post

      The "Attendance" table tracks employee tardiness. I need to count the number of times this happpens before a specific date.

      Is this possible?

      My tracking field is "Tardy_Count = (Global Date::g_Filter_Year = Record_Year; If( Code = "t"; 1 ; 0 )). How do I make this formula perform the same way using a cut-off date from an employee review form (another table)?

      Thanks,

        • 1. Re: summing a field by a date
          philmodjunk

          Much depends on the exact structure of your tables and the data stored in them. I think this will work for you, but I'm assuming some things that may not be the reality for you:

          EmployeeReview::EmployeeID = Attendance::EmployeeID AND
          EmployeeReview::Date1 < Attendance::Date AND
          EmployeeReview::Date2 > Attendance::date AND
          EmployeeReview::constT = Attendance::Code

          The first clause matches records by ID number. The 2nd and third define a date range so that you can specify the relavant range of dates for a given employee review. The final clause limits the match to only those records were code = "T". This is done by defining a calculation field in EmployeeReview, constT that simply has "T" as its calculation and which is set to return text as it return type.

          Now a calculation field in EmployeeReview can use Count ( Attendance::EmployeeID ) to count the number of "tardies" for a given employee review record. You can also use a "count of" summary field defined in Attendance that counts a never empty field such as EmployeeID to produce the same count. You'd use Attendance::sCountField on your EmployeeReview layout to show this count.

          Note: You may already have a relationship between EmployeeReview and Attendance that you cannot change as it is used for other purposes. If so, you can create a new table occurrrence of Attendance to use for the above relationship.

          • 2. Re: summing a field by a date
            mikedorn

            Phil,

            Thanks for the reply.

            Before I try this solution, what things are you assuming about my reality?

            My three table relationships are:

            Attendance table is used in a protal to add, delete, or change attendance records. It is related to the Employees table by _kp_Emp_ID::_kf_Emp_ID. Reviews table is related to Employees table by a multi-key of _kf_Review_ID and Employee_Number and is viewed via related record from a portal on the employee layout. There are no direct relationship from the Reviews table to the Attendance table.

            Will any of this make problems either now or in the future?

            Right now these three tables are doing what I expected. A concern cropped up about the tardy count in the reviews report showing the ongoing count without regard to a date. The count keeps on going even after the date of the Employees Review. Certain data is used on the reviews to show three attnedance functions critical to employee ratings. Because these reviews are a permanent record the three attendance fucnctions need to be made part of the review at on the date of the review without stopping the Attendance tables tracking.

            I hope that this explanation helps you understand my solution.

            • 3. Re: summing a field by a date
              philmodjunk

              I'm assuming the fields and data types needed for the relationship example I set up. I'm assuming one record per employee per employee review. I'm assuming you have a new enough version of FileMaker that you can related multiple pairs of fields in the same relationship.

              You'll probably find that you'll need a separate occurrence of the attendance table to link to your employee review table.

              If "table occurrence" is a new concept, check out this tutorial on the subject: Tutorial: What are Table Occurrences?

              • 4. Re: summing a field by a date
                mikedorn

                Phil,

                Some of the fields you referenced neede to be made up in my solution. After that excercise and tweaking I got it working. I'm using FMP 11 Advanced. I do understand TO's but not to the fullest usage. 

                More help, please.

                Now, how do I do the same for this field:

                Call_In_Count = If ( Global Data::g_Filter_Year = Record_Year ; If ( Code="c" ; 1 ; If ( Code="c.5e" ; 1 ; If ( Code="c.5f" ; 1 ; If ( Code="c.5np"; 1 ; If ( Code="c.5v" ; 1 ; If ( Code="ce" ; 1 ; If ( Code="cf" ; 1 ; If ( Code="cv" ; 1 ; If ( Code="cnp" ; 1 ; 0 ) ) ) ) ) ) ) ) ) ).

                Will this work by using a found set then doing the math required?

                I've noticed in the table view that finding a set of data that meets my requirements, then adding trailing summary parts gives me what I would like to see on the Review report.

                Thanks for you help.

                • 5. Re: summing a field by a date
                  philmodjunk

                  It could be simplified:

                  If ( Global Data::g_Filter_Year = Record_Year ;
                       Case ( Code="c" ; 1 ;
                                 Code="c.5e" ; 1 ;
                                 Code="c.5f" ; 1 ;
                                 Code="c.5np"; 1 ;
                                 Code="c.5v" ; 1 ;
                                 Code="ce" ; 1 ;
                                 Code="cf" ; 1 ;
                                 Code="cv" ; 1 ;
                                 Code="cnp" ; 1 ;
                                 0
                                ) // case
                        ) // IF

                  But I have no idea what you are trying to accomplish here. Nor have you told me in what table this calculation is defined.

                  • 6. Re: summing a field by a date
                    mikedorn

                    The "Attendance" table tracks employee Call-ins the same as it tracks the "Tardiness". I need to count the number of times an employee calls-in within a specific start and stop date to show this field on the employee reviews form.

                    Each code is the time an employee is asking to be excused and how they would like to be paid for that excused time off. "C" is a call-in without any compensation, "C.5e" is half an emergency day, "C.5f" is half a floater day, etc.

                    • 7. Re: summing a field by a date
                      philmodjunk

                      Yes, but why do all these values return the same result in your calculation (1)?

                      What exactly do you want to do with the call in information?

                      Perhaps see a break down with a count of each type of call in for a specified date range?

                      • 8. Re: summing a field by a date
                        mikedorn

                        This part is just part of the above Attendance table and the employee Reviews  from the above posts.

                        Each employee is allowed to have a certain number of call-ins before the discipline procedure is initiated.

                        Each of the codes listed in above posts are a type of Call_in for an employee. So actually the part of the code that is counted is the letter "c". The other parts of the code are used to track other functions in the Attendance table. 

                        My Call_In_count field is decoding each occurence of the letter "c" which is equal to "Call-ins", that decoding is used in a SUM ( ) field calculation for the total number of "Call-ins". Perhaps there is a better way to decode and SUM these occurences.

                         

                        • 9. Re: summing a field by a date
                          philmodjunk

                          Then this calculation field, call it "cTardyFlag",  will return a 1 in all attendance records where the call in code starts with "c" and a 0 or null when not:

                          Left ( Code ; 1 ) = "c"

                          From the review table, Sum ( Attendance::cTardyFlag ) will then produce your count. I'm using Sum instead of Count as this field could return a 0 (False) when a code not starting with "c" is entered and that would be counted by the count function.