I'm fairly new to FM, so please bear with me if this question sounds dumb or has already been answered.

I run a small business and I'm creating an employee database. Within that DB I want to be able to continuously track vacation time used and earned. But I would like to have the vacation automatically increased on the employee's anniversary date (but only once each year regardless of how many times the script is run). The increased vacation varies according to seniority. Of course, whenever an employee takes vacation time that would be recorded (I'm using repeating fields for start date and end dates, then calculating number of days) then automatically deducted from the remaining vacation time.

Would anyone like to help me with an appropriate script? Please!

Thanks, Dave

Hello Dave,

An example of a script which apportions leave credits in a staff database is set out below:

Show All Records

Go To Record/Request/Page [First]

Loop

If ["Status(CurrentDate) > Date(Month(StartDate), Day(StartDate), Year(Status(CurrentDate))) and LastAccrual < Year(Status(CurrentDate))"]

Set Field ["LeaveCredit", "LeaveCredit + 14 + Min(6, Year(Status(CurrentDate)) - Year(StartDate))")

Set Field ["LastAccrual", "Year(Status(CurrentDate))")

EndIf

Go To Record/Request/Page [Exit After last, Next]

End Loop

It can be run as often as desired and will only add leave credits once per year, on or after the anniversary of each staff member's start date.

The particular formula used in the example script allocates fifteen days vacation time on the first anniversary, plus an additional day for each additional year of service up to a maximum of 20 days per year. You didn't give any details of the formula for allocating vacation days according to seniority in your case, so you'd likely need to adjust the formula to suit your own circumstances.

A script of this type can be called as a sub-script of the start-up script so that it will refresh the vacation credits each time the database is opened.

Meanwhile, you have said that you are storing details of the leave taken in repeating fields. I'd advise against that, because repeating fields are inflexible, are not extensible and are cumbersome to summarize or even reference.

Instead I would recommend that you establish a second file of leave taken (with each employee absence period placed on a separate record) and link the two files via a relationship based on employeeID. Calculations of periods of absence can then take place in the vacation file, and a calculation field in your employee database with a formula along the lines of:

Leave Credit - Sum(Vacation::TotalDaysAbsent)

will give the balance of vacation credits for each employee.