Build HR Management System using MongoDB and ToolJet (Part 1: Dashboard)

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. 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.
• MongoDB: For this tutorial, I am using the free tier of MongoDB Atlas. 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.

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.

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

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

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:

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

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.

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.

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:[email protected]/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.

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:

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.

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: <h1>Summary</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:

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

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.

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

convertLeaveDaysTotal

• Create a new query from the query manager and select the Run JavaScript Code as the data source
• Enter the following code:

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.

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.

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.

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

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.

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.

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:

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.

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

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.