Filter report using dynamic values from a function

Filter report using dynamic values from a function

Requirement

When a user accesses a report, data is filtered based on the logged-in user's role so that only the required details are visible to the user.

Use Case

An employee management system has two forms, Department and Employees. The Department form has the list of various departments and the Employees form holds the details of employees. Multiple employees can belong to a department. In this application, when the head of a department logs in to the application, the employees in their department alone are listed for their perusal.

See how it works

Steps to follow

1. Create the forms with the following details:

Form

Form Link Name

Field Type

Field Name

Field Link Name

Department

Department

Single Line

Department Name

Department_Name

Employees

Employees

Name

Name

Name

Lookup (Department)

Department

Department

Email

Official Email

Official_Email

Phone

Phone

Phone_Number


The Official Email field holds the email ID the employee uses to log in to the employee management application.
 
2) Let us now create a function to find the department of the current logged-in user. Create a function named getDepartment and select the return type int.
 
(Lookups are referenced based on the record link IDs of the relevant records in their base forms, so the return type of this function is int (number). This function is going to search employee reports to find employees based on this lookup value.)
 
3) Add the below code in the ensuing Deluge editor:
  1. int getDepartment()
  2. {
  3. //Fetch the department record based on login user email ID
  4. employee = Employees[Official_Email = zoho.loginuserid];
  5. return employee.Department;
  6. }
This function searches the employee records from the All Employees report based on the logged-in user's email ID. From the Employee record, the corresponding Department record's ID value is returned.
 
4) Add a filter to the All Employees report using the getDepartment function to match the ID in the Department form:
 


See how it works


Points to note

  • The Department field in the Employee form can be created as a picklist. In this case, the getDepartment function can return the Department value as a string directly and the filter can also be done. (Since integer comparison would be more optimal than string comparison, lookup was used in this example.) The sample code could be:
  1. string getDepartment()
  2. {
  3. return Employee [ Official_Email == zoho.loginuserid ].Department;
  4. }