Build HR Management System using MongoDB and ToolJet (Part 1: Dashboard)
Tags: webdev, javascript, beginners, tutorial
Published: 2022-07-11 11:32:41

> In this tutorial, We will build an HR management system which is suite of 4 different applications using MongoDB as the database and ToolJet for building the user interface.

The HR management system will include total 4 applications:
1. **Dashboard**: To display the stats and metrics, only for HR.
2. **Company Calendar**: Adding company holidays as an HR and viewing holidays as an employee.
3. **Employees and Leave Approval**: Only for the HR person to add more employees and approve or reject pending leave requests.
4. **Leave Request Dashboard**: For employees, to submit leave requests and get stats of all the leaves.

You can check out the live application [here](https://app.tooljet.com/applications/hr-dashboard). Some sections might not be visible/functional as they're only for a particular group of people that I have added to the database.

### Prerequisites

- **ToolJet** (https://github.com/ToolJet/ToolJet): A free and open-source low-code platform that allows you to build applications quickly. Sign up for free [here](https://tooljet.com/).
- **MongoDB**: For this tutorial, I am using the free tier of [MongoDB Atlas](https://www.mongodb.com/atlas/database). It is used to deploy and run MongoDB in the cloud.

I have divided the whole tutorial into the following 6 sections:

1. **Setting up MongoDB Atlas**: Creating Database and Collections on MongoDB Atlas and adding data-source in ToolJet
2. **Dashboard**: Building the first application including the UI and the queries
3. **Company Calendar**: Building the application for viewing and adding company holidays and leaves.
4. **Employees & Leaves**: Building the application for approving leave requests from employees and managing employees
5. **Leave request dashboard**: Building the dashboard from where employees can raise leave requests.
6. **Connecting and making apps live**: In this section, we will connect all the applications and make them live.

Without further ado,  let's start setting up the database.

---

## Setting up MongoDB Atlas
Once you have created an account on MongoDB Atlas, you'll be asked to enter the `username` and `password`  for your database (not the same as what you use to log into the MongoDB cloud). Once done, you'll be redirected to your project's database deployment where you can create/manage Clusters. You can create a new Cluster and choose a configuration according to your preference.

![mongo1](https://blog.tooljet.com/content/images/size/w1000/2022/06/Screenshot-2022-06-21-at-4.13.23-AM.png)

As you can see in the screenshot above, we have a `Cluster0` deployed. Go to the `browse collections` and create a new database `hrms` with two collections `employees` and `company_holidays`.

![mongo1](https://blog.tooljet.com/content/images/size/w1000/2022/06/Screenshot-2022-06-21-at-4.18.07-AM.png)

For `employees` collections, create some sample documents with the following schema:

![mongo1](https://blog.tooljet.com/content/images/size/w1000/2022/06/Screenshot-2022-06-21-at-4.22.54-AM.png)

And for company_holidays collection, create some sample documents in the following schema:

![mongo1](https://blog.tooljet.com/content/images/size/w1000/2022/06/Screenshot-2022-06-21-at-4.23.55-AM.png)

Once, you have created the `hrms` database with the `employees` and `company_holidays` collections including some sample documents, you can go ahead and connect the datasource on ToolJet.

### Adding MongoDB datasource on ToolJet

You can add the MongoDB datasource using the Manual connection:
![mongo1](https://blog.tooljet.com/content/images/2022/06/Screenshot-2022-06-21-at-4.28.04-AM.png)

Or just use a **Connection String** instead by switching the method from the dropdown.

![mongo1](https://blog.tooljet.com/content/images/2022/06/Screenshot-2022-06-21-at-4.28.22-AM.png)

We will use the connection string method for MongoDB Atlas. Go to the `Database Deployments` on the Atlas and click on the **Connect** button next to the cluster name ( **Cluster0** in our case). A connection dialog will popup to choose the method by which you want to connect, click on the second option - Connect your application.

![mongo1](https://blog.tooljet.com/content/images/2022/06/Screenshot-2022-06-21-at-4.36.50-AM.png)

Once you click on the second option, you'll get the connection string - just click on the copy button next to it to copy the string.

![mongo1](https://blog.tooljet.com/content/images/2022/06/Screenshot-2022-06-21-at-4.37.06-AM.png)

The string that you'll copy will be like this `mongodb+srv://tooljettest:<password>@cluster0.urul7.mongodb.net/?retryWrites=true&w=majority` where `tooljettest` is the database username that I created, I will need to replace `<password>` with the password that I created for this database and then remove everything after `.net/` and add your database name - in our case the name of the database is `hrms`.

So, the actual connection string will be: `mongodb+srv://tooljettest:fakepassword@cluster0.urul7.mongodb.net/hrms`

Copy-paste the connection string on the ToolJet and click on the test connection button to test the connection before saving the datasource once the connection is successful then save the datasource.

![mongo1](https://blog.tooljet.com/content/images/2022/06/Screenshot-2022-06-21-at-4.46.51-AM.png)

---

## Dashboard

The Dashboard application will include an overview of leaves and employees. On the top, we will use **statistics** widgets to display the number of employees and the number of leaves, A **graph** that will display the number of leaves approved in the last couple of months, and a **list** of approved upcoming leaves. On the left, we will add a sidebar to switch to other connected applications of the HR management system. Take a look at the screenshot below:

![mongo1](https://blog.tooljet.com/content/images/size/w1000/2022/06/Screenshot-2022-06-15-at-1.59.08-AM.png)

Let's start building the application, go to the **ToolJet dashboard**, and click on **Create new application** button on the top-right. When the App editor opens up you'll be asked to create the initial version of your application. Once done, you'll need to **connect the MongoDB datasource** as explained in the previous section.

Now, that we have connected the datasource, we can start building the UI of the application.

### Build the User Interface of the dashboard

#### Sidebar
For building the Sidebar, we will use a container, and drag widgets like text, image, and buttons inside the container.

- Let's drag a **container** on the app editor and place it on the left side. Increase its height to reach the bottom and its width according to your preference. You can click on the handle of the container to edit its properties, and change the background color of the container to `#E6EEF8`
- Drag an image widget inside the container, and edit the image widget property to set the URL to the logo of your choice, or use this: `https://www.freepnglogos.com/uploads/company-logo-png/file-mobile-apps-development-company-logo-25.png`
- Now drag a **text** widget (below the logo) and set its value to `Hey {{globals.currentUser.firstName}}` - here we are using exposed variables to display the first name of the current user.
- Finally, Add **4 buttons** placed vertically inside the container. Set the button text to **Dashboard**, **Calendar**, **Employees & Requests**, and **Request Leaves** respectively. You can style the button by changing its background color of your choice or use `#EA4C89` , and border-radius to `10`. Also, we will keep the Dashboard button **disabled** so that it is unclickable on this app.

![mongo1](https://blog.tooljet.com/content/images/size/w1600/2022/06/Screenshot-2022-06-22-at-4.24.07-AM.png)

#### Summary
For building this section of the application, we will use a container and inside that, we will use text, statistics, graph, divider, and list view widgets.

- Let's drag a **container** and adjust its height and width to the vacant space on the canvas. Set its background color according to your preference or use `#F5F8FC`
- Drag a **text** widget on the top left of the container and set its value to Summary and wrap it inside the HTML tags to make it a heading: &lt;h1>Summary&lt;/h1>
- Drag a **statistics** widget, set the **Primary value label** to `Total Employees` and **Primary value** to `{{queries.totalCount.data.count}}` (Right now it will show the undefined as we haven't created the queries yet but it will display the query result once we create and run them), and finally, toggle on the **Hide secondary value**.
- Drag another **statistics** widget, and set the **Primary value label** to `Approved leaves` and **Primary value** to `{{queries.convertLeaveDaysTotal.data?.value ? queries.convertLeaveDaysTotal.data.value : 0}}` , set the **Secondary value label** to `Paid leaves pending approval` and **Secondary value** to `{{queries.pendingApproval.data.count}}`. You can also style the widget according to your choice.
- Now drag a **divider** and **vertical divider** to create sections as shown in the screenshot.
- On the left section, drag a **Chart** widget. Set Chart Title as `Monthly Leaves` , chart type as `Bar` and in the chart data enter:
```json
[
{ "x": "{{moment().subtract(3, 'months').format("MMMM")}}", "y": {{queries.monthChart.data.March ?? 0}} },
{ "x": "{{moment().subtract(2, 'months').format("MMMM")}}", "y": {{queries.monthChart.data.April ?? 0}} },
{ "x": "{{moment().subtract(1, 'months').format("MMMM")}}", "y": {{queries.monthChart.data.June ?? 0}} },
{ "x": "{{moment().subtract(0, 'months').format("MMMM")}}", "y": {{queries.monthChart.data.July ?? 0}} }
]
```

The data will show up on the chart once we create the queries(chart and monthChart) later in this tutorial.

- On the right section, Drag a **Text** widget and a **ListView** widget. Set the text of the Text widget to `Upcoming Paid Leaves` (use HTML heading tag for formatting). In the **List View** widget, set the **List data** field value to `{{queries.upcomingPaidLeavesformatted.data}}`. This section will display all the leaves that have been approved by HR and are upcoming.

Now the UI of our first app is finished. All we need to do now is to build queries and display data on the UI.


![mongo1](https://blog.tooljet.com/content/images/size/w1600/2022/06/Screenshot-2022-06-22-at-4.52.20-AM.png)

### Creating and connecting queries to the UI

### totalCount
This query will be used to get the total count of employees in the database. This query gets the total number of documents in the employees collection. Let's build this query:

- Go to the **Query Manager** at the bottom of the app editor, click on `+` button to create a new query.
- Select `MongoDB` as the datasource, in the **Operations** dropdown choose `Total Count` , and in the **Collection** field enter the collection name which is `employees` in this case.
- You can click on the **Preview** button to check the result of the query without executing it. Note: This is only for checking the returned data from the query.
- Now finally, click on the `Save & Run` button next to the Preview button to save the query and execute it.
- Make sure to enable the `Run query on page load?` option from the **Advanced** tab of the query so that this query runs every time the app loads.

![mongo1](https://blog.tooljet.com/content/images/2022/07/Screenshot-2022-07-07-at-2.05.22-AM.png)

Once the query has been executed, you'll see that the value in the first statistics widget displaying the Total Employees will be updated.

### leaveDaystotal
This query will make use of the Find Many operation of MongoDB query to return all the documents in the employees collection, but to get the total approved leaves in the number we will be creating a **convertLeaveDaysTotal** query as an extension to this one. Let's create the query:

- Create a new query, and choose MongoDB as the data source
- Select the `Find Many` **Operation** and enter the **Collection** name as `employees`
- In the **Filter** field, enter `{ "leaves.leave_days": { $gt: 0 }}` - this will filter the query results with only those documents that have **leave_days with a value greater than 0**
- In the **Options**, we will use MongoDB projections to define which fields to include and which fields to include from the documents. Set this as the field value `{ projection: { first_name: 0, last_name: 0, email: 0, phone: 0, _id: 0, "leaves.start_date" : 0}}`
- Finally, click on **Save and Run** button. Make sure to enable the `Run query on page load?` option from the **Advanced** tab of the query so that this query runs every time the app loads.
- Now, we will create the **convertLeaveDaysTotal** query

![mongo1](https://blog.tooljet.com/content/images/2022/07/Screenshot-2022-07-07-at-2.20.30-AM.png)

### convertLeaveDaysTotal

- Create a new query from the query manager and select the `Run JavaScript Code` as the data source
- Enter the following code:
```javascript
var leaves = queries.leaveDaystotal.data;
var count = leaves.reduce((acc,cv)=> {
    cv.leaves.forEach(obj => {
		acc += obj.leave_days
    })
    return acc;
},0)
let result = {value : count}
return result;
```

- Click on the **Preview** button, it should return a number as the output.
- Now hit **Save & Run** button - You'll be able to see the updated value of the **second statistics widget** now.
- Go to the **leaveDaysTotal** query, open the **Advanced** tab, and add an **Event Handler** to run the `convertLeaveDaysTotal` query for the **On Success** event. This will make the convertLeaveDaysTotal query run every time the leaveDaysTotal query runs successfully.

![mongo1](https://blog.tooljet.com/content/images/2022/07/Screenshot-2022-07-07-at-2.35.44-AM.png)

### pendingApproval
This query will return the count of leave requests and will display it on the secondary value of the second statistics widget.

- Create a new MongoDB query, select the Count operation, and choose the `employees` collection
- In the **Filter** field, enter `{"leaves.status": "requested"}` - this will only return the count of those documents that have status as `requested`
- Hit **Save & Run** button, and you'll see the secondary value of the statistics widget will be updated.
- Make sure to enable the `Run query on page load?` option from the **Advanced** tab of the query so that this query runs every time the app loads.

![mongo1](https://blog.tooljet.com/content/images/2022/07/Screenshot-2022-07-07-at-2.45.46-AM.png)

### chart
This query will use the `Find Many` operation of MongoDB data source and will return those documents in the `employees` collection that has leave_days greater than 0. Let's create the query:

- Create a new MongoDB query, select `Find Many` operation, enter the collection name as `employees`
- In the **Filter** field, set the value as `{ "leaves.leave_days": { $gt: 0 }}`
- In the **Options** field, set the value as `{ projection: { first_name: 0, last_name: 0, email: 0, phone: 0, _id: 0,}}`
- Hit **Save & Run**, now that we have all the documents, we need to display the data on the Chart widget but the returned data needs to be in a **key-value** pair, so to do this we will need to write **JavaScript Code**. let's create a new query called `monthChart` .
- Make sure to enable the `Run query on page load?` option from the **Advanced** tab of the query so that this query runs every time the app loads.

![mongo1](https://blog.tooljet.com/content/images/2022/07/Screenshot-2022-07-07-at-3.05.55-AM.png)

### monthChart
This JavaScript code will get the data from the **chart** query and will create an object that will have leaves count as the value stored in the month name as the key.

- Create a `Run JavaScript Code` query
```javascript
currentMonth = moment().format("M");
let leaves = queries.chart.data;
let months = leaves.reduce((acc,cv)=> {
    cv.leaves.forEach(obj => {
        if (moment(obj.start_date).format("M") <= currentMonth && 
            (moment(obj.start_date).format("M") >= (currentMonth-3))){
            if (acc.hasOwnProperty(moment(obj.start_date).format("MMMM"))){
                acc[moment(obj.start_date).format("MMMM")] = acc[moment(obj.start_date).format("MMMM")] + obj.leave_days
            }else{
                acc[moment(obj.start_date).format("MMMM")] = obj.leave_days
            }
        }
    })
    return acc;
},{});
return months;
```

- You can hit the **Preview** button to check the output and then Hit **Save & Run** button to save and fire up the query.
- Now go back to the **chart** query and go to the **Advanced** tab of the chart query to add an **event handler**. Add the handler to run the `monthChart` query on the **On Success** event of the chart query.

![mongo1](https://blog.tooljet.com/content/images/2022/07/Screenshot-2022-07-07-at-3.25.22-AM.png)

### upcomingPaidLeaves
This query will use the **Aggregate** operation of the MongoDB datasource to return the documents that will have leaves approved.

- Create a new query, select the **Aggregate** operation, and enter the collection name as `employees`
- In the Pipeline, enter `[{$match: {"leaves": { "$elemMatch" : { "status":"approved" }}}},{"$project": {"first_name":1, "last_name":1, "email":1, "leaves": {"$filter": {"input": "$leaves", "as": "leaves", "cond": { $and: [{"$gt":["$$leaves.start_date","{{moment().toISOString(true)}}"]},{ "$eq":["$$leaves.status","approved"]}] } }}}}]`
- Hit **Preview** to check the returned data and then **Save & Run** the query. Make sure to enable the `Run query on page load?` option from the **Advanced** tab of the query so that this query runs every time the app loads.

Now we will have all the documents that will have the leaves status as `approved` but to display it in the list view we will need to write **JavaScript code** to convert the returned data into the array of objects. Let's build a query for that next.

![mongo1](https://blog.tooljet.com/content/images/2022/07/Screenshot-2022-07-07-at-3.36.20-AM.png)

### upcomingPaidLeavesformatted
This JavaScript code will get the data from returned by the query `upcomingPaidLeaves` and will convert them into array of objects and put the relevant data in key-value pair.

- Use the following code:
```javascript
approvedLeavesArray = queries.upcomingPaidLeaves.data;
var arrayObjects = approvedLeavesArray.reduce((acc,cv) => {
    return acc.concat(cv.leaves.reduce((accLeave, leave)=> {
        accLeave.push(
            {
                "first_name": cv["first_name"],
                "email": cv["email"],
                "date":	moment(leave["start_date"]).format("DD-MM-YYYY"),
                "leave_days":	leave["leave_days"],
            }
        );
        return accLeave;
    },[]));
},[]);
return arrayObjects;
```

- Hit **Save & Run** but this time you'll not see any data on the list view widget. The reason being we didn't add the widgets on the ListView widget yet. To display the data on the List View widget we will need to drag widgets over it and then edit the widget properties to display the data.
- Let's drag the text widgets over the List View widget and set their value as `{{listItem.first_name}}` , `{{listItem.email}}` , `{{listItem.date}}` , and `{{listItem.leave_days}}` respectively. You can style the widgets accordingly.
- Now, go to the **Advanced** tab of the **upcomingPaidLeaves** query to add an **event handler**. Add the handler to run the `upcomingPaidLeavesformatted` query on the **On Success** event of the chart query and Save it.

![mongo1](https://blog.tooljet.com/content/images/2022/07/Screenshot-2022-07-07-at-3.37.28-AM.png)

Now, you can successfully release this version of the application by clicking the **Release** button on the top right of the app editor.

![mongo1](https://blog.tooljet.com/content/images/size/w1600/2022/07/Screenshot-2022-07-07-at-3.07.52-PM.png)

Now that we have successfully built the Dashboard(1st application) of the HR Management system, let's move forward with the second application which is **Company Calendar**.