Zoho People: Reject an Application for Leave in Deluge based on Criteria

What?
This article documents how to stop an employee from applying for leave when already 80% of their team have already booked the same date off.  My brief was:

  • Check the department this employee belongs to
  • Search for employees of the same department (team members) who have booked the same days off (ie count holiday clashes)
  • Prevent an employee for applying for leave if over 80% of the department have already booked the requested dates off.

In other words, if there are 10 people in a department/team, and 8 people have booked tomorrow off, prevent a 9th team member booking tomorrow off.

Why?
I've written this article as it took a while to achieve. The Zoho People application and its API are documented but there are problems with the documentation at the time that this article was written. One is that there are 2 sets of documentation and the other is that the majority of the documentation doesn't refer to OAuth 2.0/json and instead uses the deprecated authtoken v1 and XML.

How?
The trick in this solution was more about the process and what that process was.

Reading the brief, and having written many custom functions for other Zoho apps, it would stand to reason that the process would be: allow a user to apply for leave, then on save/submit, auto approve/reject the application based on a custom function that could calculate the percentage of staff who already booked the requested dates as leave.

[Warning: red herring ahead] For this process, we setup connections choosing to make use of the Zoho People API and we could update any of the fields EXCEPT for "ApprovalStatus" and would get the following response when attempting:
{
  "response": {
    "message": "Error occurred",
    "uri": "/api/forms/json/P_ApplyLeave/updateRecord",
    "errors": {
      "code": 7013,
      "message": "Field name 'ApprovalStatus' is invalid "
    },
    "status": 1
  }
}

[Warning: Another red herring ahead] We tried various other options such as adding a tickbox that would be used in an approval workflow but ran into issues when trying to update the record:
{
  "response": {
    "message": "Error occurred",
    "uri": "/api/forms/json/P_ApplyLeave/updateRecord",
    "errors": {
      "code": 7115,
      "message": {
        "From": "Sorry, this record cannot be edited."
      }
    },
    "status": 1
  }
}

[The working solution] I'll cut to the quick solution, which was not to try to update the form at all but instead to prevent the user from applying for leave in the first place:
  1. Create a validation custom function:
    1. Login to ZohoPeople as an admin, Go to Settings > Customization > Forms > Leave
    2. Under "Form Properties" > Tick the box under "Validation" for "Custom Validation" to create a function:
      Zoho People - Form Properties - Validation - Custom Function
  2. Ensure you select parameters to input rather than retrieving the record data:
    1. I'm calling the Function Name "RejectLeave" then click on Edit Params
      Zoho People - Creating a Function
    2. I'm calling the Method Name "OnValidate" and I add the following parameters:
      • p_RequestID specified as "ID (Leave)" ... this is the ID of the Leave form record.
      • p_Dept as the name of the "Department" ... this is found on the Employee form not the leave form.
      • p_LeaveFrom this is the value of "From" on the "Leave" form.
      • p_LeaveTill this is the value of "To" on the "Leave" form.
      • Giving you something as per the following screenshot:
        Zoho People - Setting function parameters
      • Click on "Save" to accept this
      Note: It is important to add these parameters rather than trying to retrieve the details with a zoho.people.getRecordByID("P_ApplyLeave",v_RequestId) in the function because on application, the record doesn't properly exist in the system yet and the delay causes errors further down the line. Just add these parameters as per my above instructions for a more robust solution.
  3. [See below for full code] Enter the code to determine total number of employees in this department/team:
    1. To determine the total number of employees, we will use zoho.people.getRecords as a search:
      // get total "team members" in department
      l_SearchCriterias = List();
      m_SearchCriteria = Map();
      m_SearchCriteria.put("searchField","Department");
      m_SearchCriteria.put("searchOperator","Is");
      m_SearchCriteria.put("searchText",v_DeptName);
      l_SearchCriterias.add(m_SearchCriteria);
      l_DeptEmployees = zoho.people.getRecords("P_Employee",0,200,l_SearchCriterias);
      
  4. Enter the code to search for and count holiday clashes:
    1. First we need the code to search for other requests with these dates, we're going to use a range of 1 month before and 1 month after the requested date (so this works as long as no one books longer than a month in one go - increase the submonth/addmonth parameter if an employee can book longer than a month):
      // set criteria to search for clashing annual leave dates (pre/post 1 month check)
      l_SearchCriterias = List();
      m_SearchCriteria = Map();
      m_SearchCriteria.put("searchField","From");
      m_SearchCriteria.put("searchOperator","After");
      m_SearchCriteria.put("searchText",v_GivenDateFrom.subMonth(1).toString("dd-MMM-yyyy"));
      l_SearchCriterias.add(m_SearchCriteria);
      m_SearchCriteria = Map();
      m_SearchCriteria.put("searchField","To");
      m_SearchCriteria.put("searchOperator","Before");
      m_SearchCriteria.put("searchText",v_GivenDateTill.addMonth(1).toString("dd-MMM-yyyy"));
      l_SearchCriterias.add(m_SearchCriteria);
      m_SearchCriteria = Map();
      m_SearchCriteria.put("searchField","Leavetype");
      m_SearchCriteria.put("searchOperator","Contains");
      m_SearchCriteria.put("searchText","Leave");
      l_SearchCriterias.add(m_SearchCriteria);
      l_AllTimeOffs = zoho.people.getRecords("P_ApplyLeave",0,200,l_SearchCriterias);
      
    2. Then we need the code to loop through all these matching records (and approved) and determine if it clashes with the requested dates:
      for each  r_TimeOff in l_AllTimeOffs
      {
          if(!isnull(r_TimeOff.get("Employee_ID")))
          {
              v_ThisStatus = r_TimeOff.get("ApprovalStatus");
              v_ThisFrom = r_TimeOff.get("From").toDate();
              v_ThisTill = r_TimeOff.get("To").toDate();
              v_ClashOrNot = "NO Clash with ";
              if(v_ThisStatus!="Cancelled")
              {
                  if(v_CompareDateFrom <= v_ThisFrom && v_CompareDateTill >= v_ThisFrom || v_CompareDateFrom <= v_ThisTill && v_CompareDateTill >= v_ThisTill)
                  {
                      v_TotalClashes = v_TotalClashes + 1;
                      v_ClashOrNot = "Clashes with ";
                  }
                  info v_ClashOrNot + r_TimeOff.get("Employee_ID") + ":" + v_ThisFrom + "-" + v_ThisTill;
              }
          }
      }
      
      Note: Play around with the Status, there are "Pending", "Cancelled" (UK spelling), "Approval Not Enabled" and I think "Approved", "Rejected". We're using the !="Cancelled" and probably !="Rejected" because we want pending ones to also be taken into account (first come first serve basis).
  5. Calculate the percentage of people already booked off:
    1. A very quick percentage calculation snippet of code to work this out:
      // calculate percentage
      v_Percentage = (v_TotalClashes / v_TotalDeptEmployees * 100).round(0);
      
  6. Return an error if overbooked, no error if not:
    1. To return an error (I know we set the function to return "Map" and are using a "Collection" but a Collection is either a list or a map):
          // return response as error popup
          v_OutputMessage = "Sorry! Leave has been rejected due to too many employees booking the same dates off.";
          c_Response = Collection();
          c_Response.insert("EmployeeID":v_OutputMessage);
          c_Error = Collection();
          c_Error.insert("errors":c_Response);
          return c_Error;
      
      Displays to the user as per the following screenshot AND prevents the user from submitting this until they provide a date that doesn't clash: Zoho People - Return Error
    2. To return an ok response, as in application accepted, we simply don't return an error so this might be completely optional however the Zoho People system will require a return value for this function:
          // return response as ok popup (doesn't really do anything: just says "Record added successfully")
          v_OutputMessage = "Thank you for submitting your application for leave.";
          c_Response = Collection();
          c_Response.insert("EmployeeID":v_OutputMessage);
          c_Ok = Collection();
          c_Ok.insert("response":c_Response);
          return c_Ok;
      
      Displays to the user as per the following screenshot: Zoho People - Record added successfully

The full code for this function
As mentioned, you may need to tweak the search for records with status as approved/pending but this is what we use:
/* *******************************************************************************
Function:       OnValidate
Trigger:        Function executed on the application of a leave
Inputs:         string p_RequestID,string p_Dept,string p_LeaveFrom,string p_LeaveTill
Outputs:        map/collection

Date Created:   2020-10-27 (Joel Lipman)
                - Initial release
                - Workflow to calculate 80% dept workforce
                - Returns response as popup
		- Cancels submit with error popup if over 80%, else success
******************************************************************************* */
//
// declare
v_TotalClashes = 0;
v_TotalDeptEmployees = 0;
v_OutputMessage = "";
//
// get record details
v_RequestId = ifnull(p_RequestID,0).toLong();
v_DeptName = ifnull(p_Dept,"");
v_GivenDateFrom = p_LeaveFrom;
v_GivenDateTill = p_LeaveTill;
v_CompareDateFrom = v_GivenDateFrom.toDate();
v_CompareDateTill = v_GivenDateTill.toDate();
//
// get total "team members" in department
l_SearchCriterias = List();
m_SearchCriteria = Map();
m_SearchCriteria.put("searchField","Department");
m_SearchCriteria.put("searchOperator","Is");
m_SearchCriteria.put("searchText",v_DeptName);
l_SearchCriterias.add(m_SearchCriteria);
l_DeptEmployees = zoho.people.getRecords("P_Employee",0,200,l_SearchCriterias);
v_TotalDeptEmployees = l_DeptEmployees.size();
// ensure total is not zero as we need to divide by it (defaulting to 2 because if 1 then it's 100%, as 2 it's 50% might have it booked off)
v_TotalDeptEmployees = if(v_TotalDeptEmployees<=0, 2, v_TotalDeptEmployees);
info "Total Employees in Department: " + v_TotalDeptEmployees;
//
// set criteria to search for clashing annual leave dates (pre/post 1 month check)
l_SearchCriterias = List();
m_SearchCriteria = Map();
m_SearchCriteria.put("searchField","From");
m_SearchCriteria.put("searchOperator","After");
m_SearchCriteria.put("searchText",v_GivenDateFrom.subMonth(1).toString("dd-MMM-yyyy"));
l_SearchCriterias.add(m_SearchCriteria);
m_SearchCriteria = Map();
m_SearchCriteria.put("searchField","To");
m_SearchCriteria.put("searchOperator","Before");
m_SearchCriteria.put("searchText",v_GivenDateTill.addMonth(1).toString("dd-MMM-yyyy"));
l_SearchCriterias.add(m_SearchCriteria);
// only include records where leave type includes the word "Leave" (eg. sick leave, half day leave, etc)
m_SearchCriteria = Map();
m_SearchCriteria.put("searchField","Leavetype");
m_SearchCriteria.put("searchOperator","Contains");
m_SearchCriteria.put("searchText","Leave");
l_SearchCriterias.add(m_SearchCriteria);
l_AllTimeOffs = zoho.people.getRecords("P_ApplyLeave",0,200,l_SearchCriterias);
for each  r_TimeOff in l_AllTimeOffs
{
    if(!isnull(r_TimeOff.get("Employee_ID")))
    {
        v_ThisStatus = r_TimeOff.get("ApprovalStatus");
        v_ThisFrom = r_TimeOff.get("From").toDate();
        v_ThisTill = r_TimeOff.get("To").toDate();
        v_ClashOrNot = "NO Clash with ";
        if(v_ThisStatus!="Cancelled" && v_ThisStatus!="Rejected")
        {
            if(v_CompareDateFrom <= v_ThisFrom && v_CompareDateTill >= v_ThisFrom || v_CompareDateFrom <= v_ThisTill && v_CompareDateTill >= v_ThisTill)
            {
                v_TotalClashes = v_TotalClashes + 1;
                v_ClashOrNot = "Clashes with ";
            }
            info v_ClashOrNot + r_TimeOff.get("Employee_ID") + ":" + v_ThisFrom + "-" + v_ThisTill;
        }
    }
}
//
// calculate percentage
v_Percentage = (v_TotalClashes / v_TotalDeptEmployees * 100).round(0);
if(v_Percentage > 80)
{
    //
    // return response as error popup
    v_OutputMessage = "Sorry! Leave has been rejected due to too many employees booking the same dates off.";
    c_Response = Collection();
    c_Response.insert("EmployeeID":v_OutputMessage);
    c_Error = Collection();
    c_Error.insert("errors":c_Response);
    return c_Error;
}
else
{
    //
    // return response as ok popup (doesn't really do anything: just says "Record added successfully")
    v_OutputMessage = "Thank you for submitting your application for leave.";
    c_Response = Collection();
    c_Response.insert("EmployeeID":v_OutputMessage);
    c_Ok = Collection();
    c_Ok.insert("response":c_Response);
    return c_Ok;
}

Additional:
We asked Zoho for a solution that might work and were given the following snippet of code (not tested yet!). It looks like we have to fill in the blanks and it's on the same path as we were. The main revelation was the function getEmployeesOnLeave and perhaps this can be combined with a function that gets all employees of a department. The following allows only 1 user in a department to apply for leave in a day.
c_Response = Collection();
for each  d in dd.keys()
{
    c_Params= Collection();
    c_Params.insert("date":d);
    c_Params.insert("type":"3");
    c_Params.insert("userId":empid);
    r_ApiResponse= invokeurl
    [
	url :"https://people.zoho.com/people/api/leave/getEmployeesOnLeave" 	
	type :POST 	
	parameters: c_Params.toMap() 	
	connection: “peoplecf”
    ];
    v_EmployeeCount = r_ApiResponse.get("response").get("result").get("employeeCount");
    if(v_EmployeeCount >= 1)
    {
	c_Response.insert("err1":"You cannot apply leave as another employee has already applied leave in this period");
    }
}
c_Return = Collection();
c_Return.insert("errors":c_Response);
return c_Return;

Source(s):

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate
© 2021 Joel Lipman .com. All Rights Reserved.