Overtime Hours calculation

[okachick5]

Hello,

I have finally convinced my employers to upgrade to....Version 6..wow, but it's a start.

I am building a database for them, nothing too extravagant,however, I am not sure how to do the following; I need to keep track of over-time hours. Our employees start overtime hours after a work week of 44 total hours. Some employees do work 7 days a week during busy times so I would need it to be within the whole 7 weekdays. I have a number field for Hours Worked and a summary of Total hours worked. If for instance within one week an employee has 48 hours worked, how can I do this so it will automatically calculate and put it into a new field we can call "Banked Hours". All help will be greatly appreciated.

Thank you

[Inky Phil]

Hi okachick and welcome to the forums. I am afraid that it would help if you could explain a little better what you want/have.

I understand that you want a calc field that reflects the number of hours in excess of 44 worked in a seven days period but I do not know what information is stored at the moment. for instance Is the data captured from a time clock or is it manually entered? . Is it entered once a week or everyday?

 

Also under what circumstances do you want this calculation to be assessed ie automatically once a week? or constantly monitored or on request for any given week?

 

Sorry I cannot give you a simple answer to what seems (and I am sure is) a straightforward request but I am sure that with a bit more info we will be able to help you

 

Regards

 

Phil

[okachick5]

Thank you so much for replying so quickly.

In answer to your questions in order to help me, I have added the following information;

yes it is in excess of 44 hours over 7 days

the information will be entered weekly (I'd love to say daily but unfortunately that isn't going to happen lol)

the dates and times are manually entered from worksheets handed in

I want to have the information available as soon as I can enter it

and

it's actually going to be "banked hours" and also if I can have some sort of field that when they cash it in (or use it) I would need to keep track of it and minus the hours they use to have to what they would have currently. Ex; employee David had 100 banked hours and on such a date he cashed them in. The remaining would be in the Banked hours field. I am assuming it would be a running total.

Thank you again so much for your time on this.

[Inky Phil]

Ok oka

 

Your profile says that you have fm8 but your original post mentions upgrading to 6. I will assume that you are working in 8 and you will have to replace 'table with 'file' if you are actually working in 6.

 

Since this data is entered on a weekly basis I think that I would create an 'hours inputting' routine whereby you call up your first employee on screen, enter their hours, click a 'next' button and a script, if applicable, would create a new record in a seperate table specifically created to record all bankable hours.

 

The new record should I think, record the employees unique id, the number of bankable hours, and a record of the week to which the hours relate.

 

Once these details have been retained the 'next' script can move on to the next employee.

 

I think I would also create a seperate table for banked hours called off. This would also record the employee id ,the number of hours 'un-banked' and the date on which the transaction took place.

 

Your original table should be related to the two others by employee id.

 

All that you would need then would be 3 calc fields (all result number, do not store evaluate as necessary) in your original table in order to see the running balance for any employee.

 

1. total banked hours. Sum(firstnewtablerelationship::hoursbanked)

2. total unbanked hours. Sum (second new tablerelationship:hoursunbanked)

3. current balance. totalbankedhours - total unbanked hours.

 

Because the Sum function returns the sum for related data only, fields one and two would change as you went from employee to employee in your main table and so field 3 would always be relevant to the current employees record.

 

Sorry if I have rambled on a bit but I have been thinking out loud as I typed however it gives you the the general outline of how I think that I would approach this problem. It is by no means a comprehensive solution but it would work to give you the simple running total that you asked for. You might want to consider routines that analyze the data, hence my suggestion that we record the relevant dates as well as the hours

 

Get back to us if we can help any further

 

Regards

 

Phil

[okachick5]

thank you very very much phil. I will use this and let you know how it works out.

I have fmp 8 but my employers only have 6 and it took me the last 5 months to convince them to use fmp 6 from 5 so I doubt they will go to 8 anytime soon. (afraid of technology I guess hahahaha).

Again, thank you so much for your help