3 Replies Latest reply on Aug 6, 2014 11:11 AM by philmodjunk

    Finding Records created in past 30 days

    schmity

      Title

      Finding Records created in past 30 days

      Post

           I am trying to create a solution that we can use to track points for employee attendance.  I have everything setup to enter the points into the database manually and it seems to be working.  If an employee is absent or late, they accrue positive points (this is done manually)

           Employee Table -->Points Table

           If an employee goes more than 30 days without accumulating any points for being late/absent, they earn 1 point back, up to a max of -2.  (So their point’s total, at the most, only be -2 or greater.)

           I'm trying to write a script to calculate subtracting points automatically with the following conditions:

             
      1.           If the employee’s points total is at -2, nothing happens (exit script)
      2.      
      3.           If the employee has accrued any positive points in the previous 30 days, nothing happens (exit script).
      4.      
      5.           If the employees total is greater than -2, and there were no records for positive points added in the last 30 days, a new record is created for -1 point.
      6.      
      7.           I would like this to automatically run every day.  Would I need to run the script on the server (it is hosted on FM Server 13)?

           I'm not sure how to attack this for conditions 2, 3 and 4.  Just looking for some suggestions to point me in the right direction.

            

           thanks!

        • 1. Re: Finding Records created in past 30 days
          philmodjunk

               I see an Employee Table with presumably one record for each employee and a "points table" where you create records to assess "penalty points".

               I will guess that you have a date field in points that auto-enters a creation date. If that is the case, we can use a calculation field and a relationship to match to all points records for a given employee that were logged over the last 30 days.

               But I see a possible issue with this part of your proposed system that needs clarification:

               

                    If an employee goes more than 30 days without accumulating any points for being late/absent, they earn 1 point back

               So it is clear that after 30 days of on time attendance with no absences, the employee gains a negative point if they have not already reached the limit of -2. But, assuming this produces a score that hasn't reached that limit, what happens on the very next day assuming that they show up on time?

               In other words do they gain back 1 point on day 30 and the second on day 31 or do they gain back the second on day 60? (two 30 day periods of perfect, on time attendance.)

          • 2. Re: Finding Records created in past 30 days
            schmity

                 It would be 30 days from the last negative point.  They can only earn a negative point for every 30 days of perfect attendance, up to -2.

            • 3. Re: Finding Records created in past 30 days
              philmodjunk

                   So on day 60, they'd earn the second point if still within the limit.

                   Let's assume the following fields in your employee table. Add those that you don't already have. Substitute your field names for mine where possible:

                   __pkEmployeeID (serial Id field to be used as primary key)
                   Date Of Hire (date field)
                   Date Last Point Earned (date field, auto-enter calculation enters Date of Hire, use Replace Field Contents to update current records with Date of Hire, keep "do not replace existing value" selected for this field.)
                   Date Bonus Check (Unstored calculation field with date as the specified result type: Get ( CurrentDate ) - 30 )
                   DaysSinceBonus (Unstored calculation field with date result type: Get ( CurrentDate ) - Date Last Point Earned )

                   Then you can add a new table occurrence of points named Points|BonusCheck and link it to Employee like this:

                   Employee::__pkEmployeeID = Points|BonusCheck::_fkEmployeeID AND
                   Employee::Date Bonus Check > Points|BonusCheck::Date

                   This will match to points records for a given employee that have been awarded in the last 30 days

                   Now add this calculation field named PenaltyPointsCheck: Sum ( Points|BonusCheck::points ) to compute the total points assessed in the last 30 days for that employee

                   Your script might then work like this:

                   Enter Find mode []
                   Set Field [Employee::PenaltyPointsCheck ; 0 ]
                   Set Field [Employee::DaysSinceBonus ; ">30" ]
                   Set Error Capture [on]
                   Perform Find []

                   This finds all employees that have earned a bonus if they are not over the limit. The script can then loop through the found set of records and use
                   If [ sum ( Points::Points) > -2 ]

                   to determine if a record with a bonus point of -1 should be added to the Points table. (make sure that this new record is dated so that it is not included in PenaltyPointsCheck.

                   

                        Would I need to run the script on the server

                   That's not your only option, but I would definitely use a server scheduled script to do this before midnight of each work day.