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. Let’s get started!

Project set up

As mentioned earlier, we’ll be using Supabase as our storage DB. Signup for Supabase and create three tables; an employee, a question, and a responses table. You can seed the database with this dummy data

You should also go-ahead to set up N8N. You can signup for the cloud version or self-host it. Create a new workflow, and we’ll come back to it in a bit.

Lastly, signup for Appsmith 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:

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

{{
[{label: 'all', value: '%'}]
.concat(get_questions.data.map(q =>({label: q.question, value: q.id})))
}}

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

[
    {
    "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:

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:

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:

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

{{
    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:

[
  {
    "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:

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):

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

[
  {
    "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}}
  }
]

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

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:

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:

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:

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

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

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.