Need help with Work Leave calculation annually...

[emtau]

Hi, I'm trying to figure out how to calculate the Leave Balance every Year, so that it accumulates every year according to the remaining or exceeding Balance from this year.

Right now I have 4 fields:

Field1 (Leave Entitlement) = Contains the number of Leave Days entitled to the employee at the beginning of each year. This number is fixed according to the the Salary Level of the employee.

Field2 (Enter number of Days) = This is an Edit Box where the user enters the amount of Leave days the employee requested this year.

Field3 (Leave Days Taken) = This is a Calculation Field showing how many Leave Days the employee have taken this year. (resets to 0 at new year)

Field4 (Leave Balance) = This is a Calculation Field showing how many more Leave Days remaining this year that the employee can take. (Limit can be exceeded but will take the exceeding amount to Next Years Calculation of Leave Balance)

Note/

1. The employee can take as many Leave Days as He/She wanted in a year regardless of the amount of Entitlement assigned to him/her....but will be recorded (shown somewhere) if He/She exceeds or falls behind the limit of the Entitled Days given to him/her at the end of the year.

2. By the beginning of the new year, the recorded remaining or exceeded amount of Leave Days (mentioned in Note1) will contribute to the Leave Balance of the new year (Field4) For example: This employee have exceeded his/her Leave Limit this year by # or This employee have a remaining of Leave Days of #. The new years balance will now be Field1 + # (remaining or exceeded amount).

3. Also at the beginning of the new year, Field3 will reset to "0" so it can start from the beginning on that year.

4. Repeat the whole Calculation every year.

5. keep record of every log entry, etc....so it can be printed as a report when needed.

How can I do all of the above and automatically repeat the procedure every year???

Please I really need help on this.

Thank you for your time.

emTau

[comment]

Field1 (Leave Entitlement) = Contains the number of Leave Days entitled to the employee at the beginning of each year. This number is fixed according to the the Salary Level of the employee.

Is the salary level of an employee fixed - or can it change from year to year? I would assume it can change - and therefore you should have a record for each leave allotment - as well as for each leave actually taken. Basically, what you describe is no different from a bank account, where you deposit an amount at the beginning of each year, and then make withdrawals during the year.

[emtau]

Salary and Leave Entitlement are fixed unless the employee becomes entitled to a higher leave rate. So the change in Salary will manually be edited when approved.

Here's a list of Salary Level with their number of Leave Entitlement Days

L19-17 = 14 days

L16-15 = 18 days

L14-12 = 22 days

L11-10 = 26 days

L9-1 = 30 days

The bank account situation is that....you get to deposit the amount yourself, anytime you want. Well in my case, The amount deposited will be automatically at the very beginning of the year. After two years the deposited amount (automatically) will only be your entitlement days only regardless of the remaining days from previous years.

New Conditions,

1. The employee can have remaining Leave days this year that will accumulate with Next Years Balance. So if your remaining Leave days last year is 3days and your Salary Level Falls between L14-12, then your Leave Balance this year is 24 instead of 22.

2. The employee's Leave Days Balance will reset after two years (won't accumulate the remaining from the previous 2 years).

3. The employee cannot exceed the amount of Leave days entitled to him/her every year.

.....

Can you please show me how to reset a calculation in a field at a certain date? JUST ONCE on that date.

For example:

Leave Entitlement = LE

Enter Number of Leave days requested for Leave = LDRq

The number of Leave Days you've taken = LDTkn

Leave Balance this year = LB

LE = 22

LDRq = x days

LDTkn = x days + LDTkn (accumulate but reset to 0 if 22 reached....cannot exceed 22)

LB = LE - LDTkn (will reset to 22 at the beginning of next year...add the remaining from last year to the new year's balance) but reset the balance to 22 after two years.

[comment]

Salary and Leave Entitlement are fixed unless the employee becomes entitled to a higher leave rate. So the change in Salary will manually be edited when approved.

I don't think you can do that, because once you edit the amount it will be edited for any and all years - including all previous years.

The amount deposited will be automatically at the very beginning of the year.

Nothing in Filemaker will happen automatically as a result of time passing. Similarly, a calculation cannot "reset" at certain dates.

Although in some situations you can construct a calculation to return a different result based on comparing today's date with some date in the past (for example, when calculating the current age of an employee), I don't think this case is one of them.

[emtau]

Thank you for your reply...

You are right about what you said that all the previous years will be messed up when the Salary Level is changed. I really thought that an automatic calculation is possible like the one that works on Ages (Date of Birth) where the Age automatically changes every Year, but maybe I was wrong.

Anyway, you seem to know what you are talking about and looks like you've been working with FileMaker for a very long time. Can you please help me find a better way to do the Leave Calculation? Point me in the right direction...what Fields do I need, etc...

I will try my best to finish the rest myself.

All the information you need are there in my first and second posts....more information you need I can provide.

I would really appreciate it if you can help me out on this.

Thanks in advance.