Use an if statement
if((Sick Time 2016 Carry Over + Facility Wkday Hrs + Fac Hol Wday OT Hrs + Homecare Wkday Hrs + Home Hol Wday OT Hr + Office Staff Regular Hours + Other Hours + Visits Wkday Hours + Visits Wkend Hours + # of Travel Hrs + # of Travel OT HOL Hrs + Office Staff Regular OT Hours) * .03334 > 40 ; 40 ; (Sick Time 2016 Carry Over + Facility Wkday Hrs + Fac Hol Wday OT Hrs + Homecare Wkday Hrs + Home Hol Wday OT Hr + Office Staff Regular Hours + Other Hours + Visits Wkday Hours + Visits Wkend Hours + # of Travel Hrs + # of Travel OT HOL Hrs + Office Staff Regular OT Hours) * .03334)
If the calculation is over 40 then amount default to 40 else it the result of your calculation.
calc = yourcalc;
as sipuls said ..
use Let statement to make the calc more easier.
Thank you for your response. I tried it, and it only works on a single entry. If there are multiple entries it still accrues more than the 40 hours on a YTD basis. Assuming that there are 52 weekly pay periods of 40 hours, the results need to stop accruing once the 40 hours of accrual has been reached on a YTD basis.
in that case you need a self relationship gathering data from related records, I guess.
Relating on financial year and employer ID.
I am not familiar with the "Let" statement or the required syntax. Can you elaborate or send me an example that I can follow?
Let ( ) allows you to simplify complex calculations and in the process make their operation more efficient. A good example is the If ( ) calc suggested by schamblee; in that calc, you would have FM adding up all those fields to see if they come to more than 40, then if they don't, adding them all up again just to post the answer. Using Let ( ), however, you can save the result of the initial adding up as a variable and then just use that answer if the If test fails.
Essentially, the Let ( ) function has two parts. In the first part you declare whatever values you want to use as variables, separated by semi-colons, with the complete set enclosed in square brackets. In the second part you post the result you want the calculation to deliver, which could be just one of the variables if you wish. Generically it looks like this:
firstData = <name of a field from the database, for example>
; secondData = <maybe another field>
; today = <some other piece of data you want to use, for example Get ( CurrentDate )>
; otherDate = <maybe another date>
; result = <the calc to deliver the result required: e.g. If ( today – otherDate > 7 ; firstData ; secondData )
So in your case, it might be:
sickHours = (Sick Time 2016 Carry Over + Facility Wkday Hrs + Fac Hol Wday OT Hrs + Homecare Wkday Hrs + Home Hol Wday OT Hr + Office Staff Regular Hours + Other Hours + Visits Wkday Hours + Visits Wkend Hours + # of Travel Hrs + # of Travel OT HOL Hrs + Office Staff Regular OT Hours) * .03334
; max = 40
; result = If ( sickHours > max ; max ; sickHours )
Thank you for your reply. The let statement did the same as the if statement. It stops any 1 entry from exceeding the 40 hours, but not multiple entries, of from a YTD basis. Is there a way to have the calculation field do a YTD calc and bring back results to apply to the max?
If I understand correctly, you have this calc in a shift record.
Also if I understand it correctly, you are allowed to accumulate 40 hrs. per year, which don't get blasted on january 1st.
I think that you should
1) leave out the Carry over from last year
2) just do the calc in a sickHours Field
3) have a sickHoursTotal which in a let ()
- sums the sick hours of every shift of the current year via a relationship in sh;
- returns as result the max(40; sh)
4) have a cumulatedSickHoursTotal which is = sickHoursTotal + carry over of previous years.
Your own sick hours calculation adds up no fewer than 12 separate elements. Examine that.
Yes you are correct on both counts. We are a home healthcare agency employing primarily per diem staff that in many cases do not work enough to accrue 40 hours of sick time in an entire year. For that reason, it does not make sense to blast them 40 hours. The other part of the law says, whatever portion of sick time accrued, and not used, can be carried over into the new year up to a max of 40 hours. So to account for the carry over, I create 1 record on 01/01/xxxx with the amount of the carry over, so in essence the carry over field is just one of the many fields/records that the calc is accounting for. Due to the regulations imposed on us, we are required to breakdown #'s of hours & payroll the we do in various settings which is why the calc fields are many. That is the background to this.
I have never worked with Let statements before so I do not know how to structure the statement for it to work.
Seems to me that the whole problem might be handled more simply and with much greater flexibility if you did not set up separate fields for each type of "hours" that need to be part of this calculation. A related table with a field for the employee ID, a field that identifies the category (2016 carryover, Facility Wkday....), A date field and a field for the Hours worked.
Then either a summary field or an aggregate function (sum ( ) ) can be used with a relationship or Execute SQL query to get the total hours for an employee.
I see this as more flexible as changes to what categories of hours need be tracked becomes a data entry change rather than changing multiple field definitions (and this calculation here) is avoided.