Building an Employee Survey Dashboard with Supabase and N8N
Tags: saas, admin, dashboard, survey
Published: 2021-12-03 08:00:54

---
title: Building an Employee Survey Dashboard with Supabase and N8N
published: true
description: 
tags: SaaS, admin, dashboard, survey
cover_image: https://cdn.hashnode.com/res/hashnode/image/unsplash/ZoGOw9WYMtQ/upload/v1638518022387/HUxXgA_na.jpeg?w=1600&h=840&fit=crop&crop=entropy&auto=compress
---

Many companies use questionnaires to keep track of their employee’s health and general wellbeing. Because of the time and complexity involved in building one, many choose to go ahead with off-the-shelf solutions that may be quick to set up but have poor analytics tools. For example, the organization might be interested in knowing specifics about the use of the survey/questionnaires. For example (include one or two examples here) But there’s something better than off-the-shelf, and it can be built using Appsmith.

In this article, I will teach you how to build a custom employee survey dashboard to fit your specific requirements. We’ll achieve this by tying together the best open-source tools in the business to build powerful workflows. 

We’re going to cover a lot, but specifically, you will learn how to:
* Use Supabase as a data source
* Use filters in DB queries
* Display data in charts
* Perform email automation with N8N

You can peek ahead to see the app we’ll be building [here](https://app.appsmith.com/applications/61281a99695af65b46d75843/pages/61281a99695af65b46d75845). Let’s get started!

## Project set up
As mentioned earlier, we’ll be using Supabase as our storage DB. [Signup for Supabase](https://supabase.io/) and create three tables; an employee, a question, and a responses table. You can seed the database with this [dummy data](http://appsmith-employee-survey-seed.surge.sh/seed.zip)

You should also go-ahead to set up N8N. You can [signup for the cloud version or self-host it](https://n8n.io/). Create a new workflow, and we’ll come back to it in a bit.

Lastly, [signup for Appsmith](https://www.appsmith.com/) and create a new app. Go to `Datasources` > `Create New` > `PostgreSQL` and fill the form to connect to your Supabase instance. 

Awesome! With this, we have completed all the setup. Let’s begin writing queries.

## How to use filters in DB queries
We need to write a query to get questions from the DB. Let’s call this query `get_questions`, and it should look like this:

```sql
SELECT * FROM public."questions";
```

With the returned data, we can build a question filter. Drag in a select widget and configure its `Options` property shown below.

> The name of the widget has been updated to `q_filter`

```js
{{
[{label: 'all', value: '%'}]
.concat(get_questions.data.map(q =>({label: q.question, value: q.id})))
}}
```
![question filter](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635232266/blogs/Screenshot_2021-10-26_at_8.10.44_AM_mqmwfh.png)

Similarly, go ahead to build a team filter. Name the select widget `t_filter`, and its `Options` property should be:

```json
[
    {
	"label": "all",
	"value": "%"
  },
    {
	"label": "hr",
	"value": "hr"
  },
  {
	"label": "marketing",
	"value": "marketing"
  },
  {
	"label": "engineering",
	"value": "engineering"
  },
    {
	"label": "logistics",
	"value": "logistics"
  }
]
```

Using the filters we just built, we can write filtered queries i.e., to get employees based on the selected team and get responses based on the chosen question and team. 

Create a new query for employees and update its name to `get_employees`.  The query should look like this:

```sql
SELECT id AS employee, name, email, team FROM public."employees"
WHERE team LIKE "{{t_filter.selectedOptionValue}}";
```

Similarly, the `get_responses` query for responses should look like this:

```sql
SELECT r."completedAt", r.employee, r.response, r.comment, e.age, e.sex, e.team
FROM responses r
INNER JOIN employees e ON r.employee=e.id
WHERE
    r.question LIKE "{{q_filter.selectedOptionValue || '%'}}"
    AND e.team LIKE "{{t_filter.selectedOptionValue}}";
```

Using a table widget, you can display the employee completion status like so:

![employee completion](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635233175/blogs/Screenshot_2021-10-26_at_8.26.06_AM_xytmoj.png)

And, here’s the snipped used for the `Table Data` property:

```js
{{
	get_employees.data.map
  	(e => _.omit({
    	...e,
    	...get_responses.data.find(r => r.employee === e.employee)

  	}, ['sex', 'age', 'response', 'employee', 'comment'])
  	)
}}
```

## How to display data in charts
In the last section, we wrote a query to get responses for the selected question and team. Now, we’re going to focus on displaying the data in charts.

From the widgets tab, drag a chart widget into the canvas. We want this chart to display the aggregate number for responses for a particular rating level, i.e. 26 employees rated 5. So for the `Series Data` property on the chart widget, use the snippet below:

```json
[
  {
	"x": "5",
	"y": {{get_responses.data.filter(r => r.response === 5).length}}
  },
  {
	"x": "4",
	"y": {{get_responses.data.filter(r => r.response === 4).length}}
  },
  {
	"x": "3",
	"y": {{get_responses.data.filter(r => r.response === 3).length}}
  },
  {
	"x": "2",
	"y": {{get_responses.data.filter(r => r.response === 2).length}}
  },
  {
	"x": "1",
	"y": {{get_responses.data.filter(r => r.response === 1).length}}
  }
]
```

And your chart should look like this: 

![responses chart](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635233843/blogs/Screenshot_2021-10-26_at_8.37.14_AM_duglgx.png)

We can also display responses by gender. For this, we’ll need a sex filter. You know the drill, so go and make one. Here’s mine (named `f_sex`):

![sex filter](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635234096/blogs/Screenshot_2021-10-26_at_8.41.28_AM_wqqgor.png)

And now, we can display the data using a chart widget set to a `Pie Chart` `Chart Type`. Here’s the snippet:

```json
[
  {
	"x": "5",
	"y": {{get_responses.data.filter(r => r.response === 5 && r.sex === f_sex.selectedOptionValue).length}}
  },
  {
	"x": "4",
	"y": {{get_responses.data.filter(r => r.response === 4 && r.sex === f_sex.selectedOptionValue).length}}
  },
  {
	"x": "3",
	"y": {{get_responses.data.filter(r => r.response === 3 && r.sex === f_sex.selectedOptionValue).length}}
  },
  {
	"x": "2",
	"y": {{get_responses.data.filter(r => r.response === 2 && r.sex === f_sex.selectedOptionValue).length}}
  },
  {
	"x": "1",
	"y": {{get_responses.data.filter(r => r.response === 1 && r.sex === f_sex.selectedOptionValue).length}}
  }
]
```

![chart for responses by gender](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635234360/blogs/Screenshot_2021-10-26_at_8.45.51_AM_v0cbw9.png)

For some extra credit, use the principles above to build a filtered chart to display responses by age group. Here’s mine:

![chart by age group](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635234568/blogs/Screenshot_2021-10-26_at_8.49.20_AM_s5ybtj.png)

## How to perform email automation with N8N
In this section, we’re going to look at how to send out emails from the dashboard using N8N.

For employees who haven’t completed a survey questionnaire, we need a way to resend it to them via email. Let’s build an N8N workflow for this.

The workflow would consist of two nodes, a webhook that accepts post requests and the second being a Gmail node. Here’s a snapshot of the workflow:

![n8n workflow](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635236163/blogs/Screenshot_2021-10-26_at_9.15.56_AM_ukoxlx.png)

The Gmail node should be configured to send an email by setting the `Resource` to `Message` and the `Operation` to `Send`.  The subject should contain a link to the questionnaire form, and the recipient email should be an expression that is evaluated the payload of the webhook as shown below: 

![email field](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635236726/blogs/Screenshot_2021-10-26_at_9.25.18_AM_uxou0o.png)

Going back to the dashboard, we can create a new API to trigger this workflow. Go to `Datasources` > `Create New` > `Create New API` and enter the URL of the N8N webhook. Give the request a name i.e. `send_email`, set the request type to `POST`, and the body would be the email of the employee selected from the table:

![send email API](https://res.cloudinary.com/confidenceappsmith/image/upload/v1635237234/blogs/Screenshot_2021-10-26_at_9.33.46_AM_sko9qv.png)

Now, going back to the table widget, we can create a custom column of `Column Type` `Button`. Configure this column to run the `send_email` API when it is clicked. Thus, clicking on the button for a particular employee would resend the questionnaire to that employee via email.

## Wrap up
![you did  it](https://media.giphy.com/media/J5Xr9k7qK5KGRi45vp/giphy.gif)

Awesome! We’ve been able to build the employee survey dashboard. You can take a look at the app here [https://app.appsmith.com/applications/61281a99695af65b46d75843/pages/61281a99695af65b46d75845](https://app.appsmith.com/applications/61281a99695af65b46d75843/pages/61281a99695af65b46d75845)

You can expand on this and add more features to the app. The floor is all yours! We’d love to see what you add next. By the way, [give us a star any time you visit github](https://github.com/appsmithorg/appsmith).