3 Replies Latest reply on Jul 30, 2013 8:39 AM by philmodjunk

    Updating a leave field



      Updating a leave field


           Hi there,
           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?

        • 1. Re: Updating a leave field

               If you are using FIleMaker Server, use a server scheduled script to perform a find for all approved leave records with a related "DateFrom" record that is the same as today's date (Get (CurrentDate ) ). Then your script can update the matching Job Location field to show them on leave. The same script can then check DateTo with today's date to determine which employees have returned from leave to update their Job Location fields back to their specified location.

               Instead of Set Field, use Replace Field Contents to update the entire such found set all in one batch update.

          • 2. Re: Updating a leave field

                 Fantastic, thank-you :). I would have probably gone the long way around with find records and loops blush. Now I am even happier that I convinced my client to go with the server. Thinking about that more, there are going to be more things like this to run after hours, such as reports and updating. Using server scheduled scripts I could set them all to run just after midnight, so when the staff come in the following morning everything should be updated. Thank-you again for the advice :)

            • 3. Re: Updating a leave field

                   There are limitations to what a server scheduled script can do. Be sure to check each script step for server compatibility in the script editor and if you are doing any import or export of records, see this knowledgeBase article on the limitations to those steps: http://help.filemaker.com/app/answers/detail/a_id/7035/kw/server%20import%20records

                   I have several server scheduled scripts in place here, but I also ended up using Windows Scheduled tasks to launch a FileMaker "robot" file that connects to the db and runs a script in the hosted file in order to run the script from a client context and thus avoid the import/export limitations documented in that help article.