Subtracting from a running total?

[sbsc40]

Our attendance system has points associated to job codes assigned to poor attendance. IE if you have an unexcused absence the code is U and the points are 20, if you leave early the code is E and the points are 5. These are tracked and lead to reprimands at 25 point intervals. That is easy enough to track in crystal but where I'm having trouble is that we also have a way to remove points. For 12 hour employees 5 points are subtracted every 14 shifts they work without accruing points, and for 8-10 hour employees 5 points are subtracted every 20 shifts worked without accruing points.


-So my question: Does anyone have any idea how I could go about tracking the points accrued, and subtracting points based on the parameters given only if the accrued points are over 0?

For further reference the tables/columns used in my report are:
Employeehours.employeeid
Employeehours.timein
Employeehours.timeout
Employeehours.jobcode

Employeelist.employeeid
Employeelist.firstname
Employeelist.lastname
Employeelist.datehire
Employeelist.defaultjobcode

Also: in my report I have to following formulas
- one relating the number of points produced by a code
- one displaying the ammount of hours worked a day (timein timeout difference)
- shift streak worked (count of days without a jobcode reffereing to poor attendence, problem with this is that it shows duplicates if there are two shifts worked in a day or if there is a jobcode which doesnt break the streak but still results in 0 hours worked IE vacation)
- a running total of points accrued.

Any help as far as how I could do this, whether or not I can do this, or what additional information I would need to provide to be able to do this would be greatly appreciated.

 

[sbsc40]

If posting examples from the report where points need to be removed would help just let me know and I'll get them up.

 

[lbass]

Yes it would.

-LB

 

[Madawc]

I think you could do it with a formula field, something like

if {code} = "U" 
     then 25
     else if {code} = "E" 
             then 5
             else if @RemovePoints
                     then -1
                     else 0

Do a running total on @TotalUp.


Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[sbsc40]

Thanks for the input Madawc! Do you have any idea what the @RemovePoints would look like?

http://imgur.com/lFJia

 

[lbass]

I see you data, but you haven't explained how your requirements relate to it. You should show the content of your formulas and also show the results you would expect from the sample data based on the criteria you intend to apply.

-LB

 

[Madawc]

You need to design @RemovePoints. Maybe display it separately to be sure it is right.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[BBST]

The @RemovePoints could be a simple formula. First, add @GoodShifts for tracking the last time an employee accrued points. Increment the formula for every shift:

@GoodShifts = GoodShifts  + 1

Then, I would add this code into the code Madawc proposed above whenever points are accrued:

@GoodShifts = 0

Then @RemovePoints could be something like:

if 12hourempl then
 if @GoodShifts > 13 then true
 else false
else
 if @GoodShifts > 19 then true
 else false
@GoodShifts = 0