Updating a leave field
I need to find out how I would go about updating a field I have in an employee table with regards to leave. At the moment I have my whole leave section working nicely, from requesting leave to managers approval to viewing as a list. The only issue now is I have a field in the employee‘s table called ‘Job Location’ and this field lets the managers know where the employee is at a certain time, whether they are on-site, off-site, on a project, on leave etc. I need to figure out how I can set it so that the leave field will be updated automatically to say they are on leave when the time comes around.
My client will have a hosted solution on FM Server 12 and the users are going to be either on FMGo or FM Pro. For the leave portion I setup 2 tables, one to generate the leave forms (Leave) and one to use as a portal into Leave which will save what type of leave (Annual, Sick, Study etc.) and what dates they are taken (from date – to date) for that leave type (LeaveType). I have setup my relationship as follows,
Employee for Leave::__EmpID – Leave::_empID (create or delete record in Leave)
Leave::__LeaveID – LeaveType::_leaveID (create or delete record in LeaveType and sorted by type)
I need to be able to update the ‘Employee::Job Location’ field automatically when the dates for the leave request become active. I was thinking of starting an Open Script set to run whenever a user launches the program, cause as I develop this system I feel that there might be a few more times when I will need to have certain fields updated depending on the date. In the open script I was going to have it go to the LeaveType layout and then search for all records where:
Leave::Approved = “Yes”
LeaveType::dateFrom = “<” & (get(currentDate) – 1)
LeaveType::dateTo = “>” & (get(currentDate) + 1)
Leave::Update = “No”
After the find is performed I would then do 2 set fields Employee for Leave::PreviousJob = Employee for Leave::Job Location and then Employee for Leave::Job Location = “On Leave”. This should cover setting the job location when the user is on leave, and then I was thinking of adding a second script afterwards that would check for Leave::Updated = “yes” and Leave::dateRetruning = >get(currentdate) and if any records are found I would just reverse the PreviousJob and Job Location fields. That should allow me to change the job location back to what it was when the employee should be back at work.
2 concerns I am having is that for one, the find in my open script is not working properly and secondly, am I going about this the wrong way?