Supervisor look-up automation with CSV data

Written by Peter Hilton | 5 min read
Published on: June 6th 2018 - Last modified: June 14th, 2021
Supervisor look-up - selecting from a list of candidates

Tutorial showing how to use a list of employees and their supervisors to automate supervisor look-up so you can automatically assign employee request workflow tasks

Approval workflows typically assign approval tasks to the requester’s manager. This tutorial shows how to automate this assignment by using CSV data for automatic employee supervisor look-up in workflows.

Approval workflow pattern

Many human resources (HR) workflows, such as vacation requests and hiring requisitions, are based on the employee request approval pattern. This workflow pattern consists of a trigger form that an employee will complete when starting the workflow, followed by an ‘Approve request’ task, completed by the employee’s line manager.

Approval workflow

Ideally, the workflow will automatically assign the ‘Approve request’ task to the employee‘s manager. However, the workflow automation platform typically doesn't manage data that includes employee-manager relationships. This data typically comes from an HR system.

Select supervisor (approve employee request example)

To automatically assign the ‘Approve request’ task, you need a ‘Select supervisor’ sub-process that will look up the requester’s supervisor, to use for the task assignment.

Spreadsheet example

The simplest way to provide the list of employee-supervisor relationships is to use a spreadsheet, and save the data in a CSV file.

Supervisors table

This employees spreadsheet shows each employee’s supervisor. However, Process Governance identifies user accounts by email address, so you need email columns for both employee and supervisor. The next step is to make this data available in the workflow.

Attaching CSV data to a workflow in Process Governance

The make the CSV data available in the workflow, you need to make it the fixed value of a field. To do this:

  1. Add a File type ‘Employees CSV’ field to the workflow’s ‘core information’.
  2. Add a ‘Set core information’ event to the workflow.
  3. Configure the ‘Set core information’ event with a fixed value - the CSV file.

Load CSV data - core data event

The next step is to add a script task that will read and parse this CSV.

Supervisor look-up sub-process

The ‘Select supervisor’ sub-process essentially consists of a JavaScript action that does all of the work.

Supervisor look-up sub-process

The trigger form provides the employee selection, the ‘Load CSV data’ event provides the list of employees and their supervisors, and the ‘Select supervisor’ JavaScript action selects the selected employee’s supervisor from the list.

The script configuration requires three workflow variables:

  1. ‘Selected employee’ - existing variable, used on the trigger form
  2. ‘Employees CSV’ - existing variable, from the core informaton
  3. ‘Selected supervisor’ - a new User variable, for the script result

These variables are used in the following script.

const _ = require('_')
const csv = require('csv')
const files = require('files')
const users = require('users');

const employeeColumn = 'Employee email'
const supervisorColumn = 'Supervisor email'
const options = { columns: true, relax_column_count: true, bom: true }
const csvContents = files.getContent(employeesCSV.id)

csv.parse(csvContents.buffer.toString('utf-8'), options, (error, employeeList) => {
  if (error) {
    console.log(`Error parsing CSV: ${error}`)
    return
  }
  if (employeeList.length == 0) {
    console.log(`Error: CSV contains zero rows`)
    return
  }
  else {
    console.log(`${employeeList.length} rows read from CSV`)
  }
  if (!employeeList[0][employeeColumn]) {
    console.log(`Error: missing ‘${employeeColumn}’ column`)
    return
  }

  const entry = _.find(employeeList, [employeeColumn, selectedEmployee.emailAddress])
  if (!entry) {
    console.log(`Error: no entry for employee ‘${selectedEmployee.emailAddress}’`)
    return
  }
  console.log(`Found entry: ${JSON.stringify(entry)}`)

  if (!entry[supervisorColumn]) {
    console.log(`Error: missing ‘${supervisorColumn}’ column for employee`)
    return
  }

  selectedSupervisor = users.findByEmail(entry[supervisorColumn])
  if (!selectedSupervisor) {
    console.log(`Error: no Process Governance user account found for ‘${entry[supervisorColumn]}’`)
    return
  }
  console.log(`Success: ${selectedEmployee.firstName} ${selectedEmployee.lastName} → ${selectedSupervisor.firstName} ${selectedSupervisor.lastName}`)
})

Now you can test the script, by selecting a test value for the ‘Selected employee’ field, uploading the same CSV file as before as the ‘Employees CSV’ test value, and selecting ‘Start new test’. The test runner’s ‘Variable updates’ table should show a user ID in the ‘selectedSupervisor’ variable’s ‘Updated value’ column.

Now you can publish this ‘Select supervisor’ process, and call it up from the sub-process action in the employee request process. In the sub-process inputs configuration, set the ‘Selected employee’ field to the Case / Creator. The outputs configuration then provides a ‘Selected supervisor’ role to which you can assign the ‘Approve request’ task.

Next steps

This tutorial showed a relatively straightforward way to use a JavaScript action to perform an employee-supervisor look-up. In practice, you might not want to use a fixed CSV file, attached to the workflow, because you would have to update this CSV file every time an employee joins or leaves the company.

Alternatively, you could replace the core information event with a JavaScript action that fetches the employee CSV data from an external HTTP URL, or via WebDAV. The rest would stay the same. You could also use an API that serves employee data in JSON or XML format, which would mean minor changes to the ‘Select supervisor’ JavaScript action that parses the data.

Signavio Process Governance’s JavaScript actions give you the flexibility to integrate with complex data and external systems, to further automate your business processes. To create your own workflows that automatically assign tasks to your boss, register for a free 30-day trial today.

Published on: June 6th 2018 - Last modified: June 14th, 2021
Topics: Workflow