Forem: Shubhendra Singh Chauhan

Supercharge Your Web Automation with BrowserQL: Logging in and Scraping Private Data Seamlessly

Shubhendra Singh Chauhan — Sun, 27 Apr 2025 22:23:36 +0000

Scraping data behind a login is often one of the hardest parts of browser automation.

Traditional tools like Puppeteer or Playwright can do it — but they require heavy setup, CAPTCHA bypass tricks, and constant maintenance to handle anti-bot defenses.
Browserless.io changes the game with BrowserQL — their GraphQL-based automation API, designed to make stealth-first browser control easier than ever.

In this guide, we’ll:

Log in to Hacker News
Access the user's private profile page
Extract hidden data (the user's email address)
Learn why BrowserQL makes this process effortless compared to traditional approaches

✅ And we’ll do it with clean Node.js code and just a few simple mutations.

Find the entire code in this GitHub repo

🚀 Why Browserless and BrowserQL?

Before we dive into code, let’s understand why Browserless is a major upgrade for web automation:

Problem with Traditional Automation	How- Browserless/BrowserQL Solves It
Fingerprinting (easily detected bots)	Browserless automatically applies human-like fingerprints
Proxies and session management	Built-in session handling and proxy support
Complex async code to wait for pages	GraphQL-style mutations like `waitForNavigation` and `goto` handle waits intuitively
Heavy setup and browser maintenance	Fully cloud-hosted; no local browsers needed
Managing CAPTCHAs and consent modals	BrowserQL options handle consent modals, stealth scripts minimize CAPTCHA triggers

Browserless essentially lets you focus on your business logic, not fighting web defenses.

✅ No browser binaries.

✅ No network headaches.

✅ No bot detection flags.

Prerequisites

Account on Browserless.io
Node & npm installed locally

The BrowserQL Query: Logging In and Scraping

BrowserQL uses a GraphQL-style syntax where every interaction with the browser is expressed as a mutation.

Our flow will involve the following steps:

Navigate to Hacker News login page
Type the username and password
Click the login button
Wait for navigation to confirm login
Navigate to the private profile page
Scrape the email field from the form

Here’s the BrowserQL mutation we’ll use:

mutation LoginAndScrape {
  goto(url: "https://news.ycombinator.com/login", waitUntil: networkIdle) {
    status
  }
  enterUsername: type(text: "yourUsername", selector: "input[name='acct']") {
    time
  }
  enterPassword: type(text: "yourPassword", selector: "input[name='pw']") {
    time
  }
  clickLogin: click(selector: "input[type='submit'][value='login']") {
    time
  }
  afterLogin: waitForNavigation {
    url
    status
  }
  postLoginScrape: goto(url: "https://news.ycombinator.com/user?id=yourUsername", waitUntil: networkIdle) {
    status
  }
  emailAddress: mapSelector(selector: "input[name='email']") {
    email: attribute(name: "value") {
      value
    }
  }
}

To test this visually, we will run this inside the BrowserQL IDE:

Login to Browserless.io
Select the BrowserQL Editor from the left navigation
Copy-paste the above code and update the credentials

💡Once tested on BroswerQL IDE, you can use the Export Query as Code feature to turn any of your queries into a number of available coding languages.

How BrowserQL Mutations Work

Mutation	What it does
`goto`	Navigates to a page
`type`	Types into an input field
`click`	Clicks a button
`waitForNavigation`	Waits until page navigation completes
`mapSelector` + `attribute`	Extracts data from HTML elements

Note: While text() mutation extracts visible text, input fields (like email forms) require fetching the value attribute instead.
That's why we use mapSelector + attribute(name: "value") to scrape the email address cleanly.

You can find the list of all the mutations on Browserless Docs

Setting Up the Project

Let’s get started with a simple Node.js project.

1. Initialize a new project and install dependencies

npm init -y
npm install node-fetch dotenv

node-fetch: To make API requests
dotenv: To securely manage API tokens

2. Create a `.env` file for your API key

Never hardcode sensitive information like API tokens.

Create a .env file:

BROWSERLESS_TOKEN=your-browserless-api-token

And add .env to your .gitignore file to avoid committing secrets.

NodeJS Code:

Now let’s export the code from the BrowserQL IDE:

Open BrowserQL IDE
Select the Export query as Code option on right side of the editor
Select Javascript(fetch) from the list of available languages
click on the copy icon on top of the code-block

Note: The copied code will include the hardcoded API token. You will need to remove the token from the code and put it in the .env file. You can also find the API Token on the dashboard of Browserless.

Add the following lines on top of the copied code:

import fetch from "node-fetch";
import dotenv from "dotenv";

dotenv.config();
const token = process.env.TOKEN;

Your final code will look like this:

import fetch from "node-fetch";
import dotenv from "dotenv";

dotenv.config();

const endpoint = "https://production-sfo.browserless.io/chrome/bql";
const token = process.env.TOKEN;

const optionsString = "&blockConsentModals=true";

const options = {
  method: "POST",
  headers: {
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    query: `
mutation LoginAndScrape {
  goto(url: "https://news.ycombinator.com/login", waitUntil: networkIdle) {
    status
  }

  enterUsername: type(
    text: "yourUsername", 
    selector: "input[name='acct']"
  ) {
    time
  }

  enterPassword: type(
    text: "yourPassword", 
    selector: "input[name='pw']"
  ) {
    time
  }

  clickLogin: click(
    selector: "input[type='submit'][value='login']"
  ) {
    time
  }

  afterLogin: waitForNavigation {
    url
    status
  }

  postLoginScrape: goto(
    url: "https://news.ycombinator.com/user?id=yourUsername",
    waitUntil: networkIdle
  ) {
    status
  }

emailAddress: mapSelector(selector: "input[name='email']") {
  email: attribute(name: "value") {
    value
  }
}
}

    `,
    operationName: "LoginAndScrape",
  }),
};

const url = `${endpoint}?token=${token}${optionsString}`;
const response = await fetch(url, options);
const data = await response.json();

console.log(JSON.stringify(data, null, 2));

✅ Browserless handles the entire browser session, fingerprinting, navigation, and anti-bot measures behind the scenes.

Expected Output

Running the script returns a clean JSON like this:

{
  "data": {
    "emailAddress": [
      {
        "email": {
          "value": "username@email.com"
        }
      }
    ]
  }
}

Why BrowserQL Shines Here

Browserless Advantage	Impact
Cloud-hosted stealth browsers	No local installation
Built-in human-like behavior	Bypass simple bot detection automatically
Consent modals auto-accepted	No annoying pop-ups
GraphQL language	No need to learn complex browser APIs
Session management handled	Clean, lightweight scripts

✅ In 30 lines, you’ve built a real-world login automation and data extraction workflow!

Final Thoughts

BrowserQL isn't just a new API — it’s a new way of thinking about browser automation:

Stealth-first by design
Cloud-native from day one
Intuitive like GraphQL, not procedural coding

If you want to stop worrying about fingerprints, proxies, CAPTCHAs, and flaky browsers —

Browserless and BrowserQL are your secret weapons.

Try BrowserQL Yourself

You can start experimenting with BrowserQL today:

👉 Create your free Browserless account here

Managing Multiple Git Profiles with Ease: Introducing `gguser`

Shubhendra Singh Chauhan — Fri, 28 Feb 2025 20:48:52 +0000

If you're a developer juggling between work projects, personal side projects, and maybe even some open-source contributions, you're likely familiar with a common frustration—managing multiple Git identities on the same machine.

Imagine this scenario: You commit changes to your personal project and push them to GitHub, only to realize that your work email is attached to the commit. Or worse, you accidentally push a work-related change using your personal GitHub account. Fixing this means manually updating your Git config each time you switch contexts.

For years, developers have resorted to running:

git config --global user.name "Your Name"
git config --global user.email "your.email@example.com"

But this is tedious and error-prone. What if there was a better way?

Meet `gguser`

gguser is a simple yet powerful CLI tool designed to seamlessly switch between multiple Git user profiles. Whether you're switching between work, personal, or open-source accounts, gguser makes the process effortless.

🚀 Installing `gguser`

Installing gguser is quick and easy. Simply run:

npm install -g gguser

Now, you have gguser available globally, ready to manage your Git profiles.

🎯 How to Use `gguser`

1️⃣ Adding Git Profiles

Before switching between Git users, you need to define them:

gguser add <profile_name> "<full_name>" "<email>" [ssh_key]

Example:

gguser add work "Shubhendra Chauhan" "work@company.com" ~/.ssh/id_ed25520
gguser add personal "Shubhendra Singh Chauhan" "personal@email.com" ~/.ssh/id_ed25521

Here, the SSH key is optional but useful if you want to authenticate using SSH.

2️⃣ Switching Between Git Profiles

Interactive Selection

Instead of remembering profile names, use:

gguser select

You'll see a list of your configured profiles and can simply pick one.

Quick Switching

If you already know which profile you want to switch to, just run:

gguser <profile_name>

Example:

gguser work

This updates your Git global config instantly.

3️⃣ Checking Your Current Git User

Ever wondered which Git identity is active? Quickly verify using:

gguser now

This command will display the currently configured Git user:

👤 Current Git User: Shubhendra Singh Chauhan <personal@email.com>

4️⃣ Managing Your Profiles

Listing All Profiles

To see all configured profiles, run:

gguser list

Removing a Profile

No longer need a profile? Remove it:

gguser remove <profile_name>

🔗 Linking a Profile to a Directory

Many developers work on different projects that require different Git identities. For example:

Your personal projects live in ~/projects/personal
Your work projects live in ~/projects/work

Instead of manually switching Git users each time, you can link a profile to a specific directory:

gguser link work

Now, whenever you enter that directory, gguser will automatically use the correct Git profile.

To unlink:

gguser unlink

🔥 Why `gguser` is a Game-Changer

✅ No more accidental commits with the wrong Git identity

✅ Seamlessly switch between work, personal, and open-source profiles

✅ Automatic profile selection based on directories

✅ Easily manage SSH keys

✅ Developer-friendly CLI with an interactive selection mode

💡 Contribute or Extend `gguser`

gguser is open-source, and contributions are welcome! If you have ideas for improvements or new features, feel free to submit a pull request. You can check out the repo here:

🔗 GitHub: https://github.com/withshubh/gguser

📦 NPM: https://www.npmjs.com/package/gguser

🎯 Final Thoughts

For developers working with multiple Git accounts, gguser is a must-have tool. No more wasting time manually updating Git config settings or dealing with incorrect commit histories. Try it out today and simplify your workflow!

Let me know in the comments how gguser has helped you! 🚀

Kubernetes for Beginners: Introduction, Functionality, and Cost

Shubhendra Singh Chauhan — Sat, 20 Jan 2024 13:05:45 +0000

Are you just starting to learn about Kubernetes, but the technical jargon and complex concepts are making your head spin? Fear not! In this beginner-friendly tutorial, we'll dive into everything you need to know about Kubernetes to kickstart your journey. Whether you're a developer, IT enthusiast, or someone just curious about the world of tech, we'll break down the basics of Kubernetes, explore its functionalities, and even sprinkle in some insights on managing costs associated with it. So, let's get started.

⚠️ Note: This tutorial assumes a basic familiarity with Docker. If you're new to Docker, consider exploring its fundamentals before diving into this content for a smoother learning experience.

Introduction
Functionality
Cost
- Cost associated with k8 clusters
- Optimizing cost

Introduction

Let's first take a look at the definition of Kubernetes:

Kubernetes, also known as K8s, is an open-source container orchestration tool developed by Google to help you manage containerized/dockerized applications supporting multiple deployment environments like On-premise, cloud, or virtual machines.

In simple words, imagine you have a bunch of different programs, each like a little worker doing a specific job. Now, Kubernetes is like the smart manager that keeps an eye on all these workers (we call them containers) and makes sure they're doing their tasks correctly.

Think of these containers as small, portable boxes that contain everything a program needs to work smoothly. They could be on your computer, in the cloud, or on some virtual machines – it doesn't matter. Kubernetes helps you manage and organize these containers so that your programs run smoothly no matter where they are.

Functionality

Now, we will learn about the functionalities of Kubernetes using an analogy:

Imagine you have a bakery with a superstar baker (your website) making delicious cakes (information) stored in a special box (Docker container). Business is booming, and lots of people are coming in to buy cakes. But, oh no, the baker is getting overwhelmed, and sometimes the cakes don't come out right.

Scenario:

Issue: Too many customers, and the baker (Docker container) is struggling.
Solution: You decide to set up another baker in a different kitchen (server) to share the workload.

Functionality: Horizontal Scaling

Explanation: In the digital world, we call this "Horizontal Scaling," where we duplicate our bakers (pods) to handle more customers (traffic).

This helps for a while, but your bakery becomes so famous that you're flooded with customers, and now you need even more bakers and kitchens. It's a lot of work, and you can't update the cake recipes without a headache.

Scenario:

Issue: The bakery is getting extremely popular, and you need more bakers and kitchens (servers).
Solution: You set up another 20+ servers (just to be sure this time), which all need Docker containers and some extra load balancers to help manage the bandwidth distribution.

Functionality: Load Balancing and Efficient Resource Management

Explanation: Now, you're practicing "Load Balancing" to make sure customers are evenly distributed between your servers, and you're also efficiently managing resources to handle the increased workload.

Now, imagine if you had a magical organizer (Kubernetes). Right from the start, when you had just one or two bakers, this organizer could automatically duplicate them whenever you needed. In this magical world, each baker (Docker container) is called a 'pod,' and the organizer knows exactly how many bakers you want (worker nodes).

Functionality: Pod Creation and Auto-scaling

Explanation: The magical organizer is Kubernetes, creating and managing 'pods' (containers) and automatically adjusting their numbers based on your needs. It's like having a dynamic baking team that grows or shrinks as the demand changes.

You simply tell the organizer (Kubernetes master) how many pods (bakers) you need, and it takes care of distributing the workload among them, making sure no baker is overworked or underworked. Plus, you can easily update your recipes (website) without breaking a sweat.

Functionality: Workload Distribution and Rolling Updates

Explanation: Kubernetes master efficiently distributes the workload among pods, ensuring each one is handling its fair share. When you update your recipes (website), it's like having a smooth "Rolling Update" without disrupting the entire bakery operation.

It's like having a baking manager who automatically handles everything, ensuring your bakery runs smoothly, no matter how many customers (visitors) show up. Smart, isn't it? 🍰✨

Functionality: Exposing the Bakery to the World

So these pods (bakers) that are created, and the cakes (website) that are inside them, all have their own addresses. Great, they are all accessible to the outside world, and each version of your bakery can be reached by the public and by you if you want to edit anything.

Wait...how can they be exposed if they are in a pod? Well, it’s one of the powerful features of Kubernetes.

Functionality: Service and Load Balancing

Explanation: For the outside world to view your bakery, you need to expose the pods to the network. If you tell it to, Kubernetes will spin up something called a 'Service,' acting like a diligent front-of-house staff member to make sure that none of the bakers (pods) are getting too much traffic and risking a 'crash zone.'

Functionality: Precision Load Balancing

Explanation: Unlike a usual front-of-house staff member (load balancer), Kubernetes Service balances the load by baker ID (pod ID), ensuring that you can control everything with a lot more precision. It's like making sure each baker is handling customers according to their individual capacity.

Functionality: Cloud Provider Load Balancer

Explanation: To top it all off, Kubernetes creates a load balancer in your cloud provider. This means that you update your bakery in one place, and like magic, the changes are reflected everywhere. It's like having a head chef who updates the menu centrally, and all the kitchens automatically get the latest recipes. Mind-blowing, isn't it? 🤯

From the above analogy we can conclude the following benefits of using Kubernetes:

Assures high availability with zero downtime
Highly performant and scalable
Reliable infrastructure to support data backup and recovery

Bonus: A video that explains Kubernetes

Cost

Over the past decade, migrating to the public cloud and adopting continuous delivery has enabled developers to move faster than ever before. accelerating their time to market from weeks to on-demand using self-service capabilities. Today developers consume the cloud like they consume coffee but they never get to see the real cost, resulting in the CFOs and finance teams receiving the bill every 30 days with little to no context. The problem today is that developers lack basic visibility into which apps or microservices are consuming cloud resources and costs. Over-provisioned infrastructure, deserted environments, zombie nodes, or even under-utilized clusters can all be cost wildfires but they go unnoticed until someone finally picks up the bill. In a world where developers are scrambling to determine the root cause of cloud cost, Tagging has become the new scripting. However, tagging cloud infrastructure with labels is a time-consuming mind-numbing, and never-ending exercise that at best is only 75% accurate in an ever-changing world.

Cost associated with k8 clusters

The cost associated with Kubernetes (K8s) clusters can vary based on several factors, including the cloud provider you choose, the type and size of instances you use, the storage requirements, network usage, and any additional services or features you employ. Here are some key factors that contribute to the cost of running Kubernetes clusters:

Compute Resources (Instances): The cost of the virtual machines or instances used to run your Kubernetes nodes.
Storage: The cost of persistent storage for your data within the cluster.
Network Traffic: Charges related to data transfer between nodes, load balancers, and services.
Managed Services: If using a managed Kubernetes service from a cloud provider, there may be additional costs.
Load Balancers: Charges for using load balancing services to distribute traffic.
Geographical Location: The cost of resources can vary based on the geographical region in which your Kubernetes clusters are deployed.

Optimizing cost

Let’s look at some of the ways through which the cost can be optimized focusing on primary cost factors associated with K8 clusters:

Compute Resources (Instances):
- Right-size Instances: Choose instances that match your actual workload requirements to avoid overprovisioning.
- Auto-scaling: Implement auto-scaling to dynamically adjust the number of instances based on demand.
Storage:
- Optimize Storage: Choose the right type of storage (e.g., standard, SSD) based on your performance needs.
- Data Lifecycle Policies: Implement automated policies for managing data lifecycle, including retention and deletion.
Network Traffic:
- Minimize Data Transfer: Optimize communication between services and regions to reduce data transfer costs.
- Content Delivery Networks (CDNs): Use CDNs for static assets to lower data transfer costs.
Managed Services:
- Evaluate Costs: Compare the costs of using managed Kubernetes services vs. self-managed clusters.
- Choose Efficient Services: Use only the managed services that are necessary for your application.
Load Balancers:
- Use Efficiently: Optimize the use of load balancers and consider alternatives for specific use cases.
- Evaluate Traffic Patterns: Understand your application's traffic patterns to provision load balancers appropriately.
Geographical Location:
- Select Regions Wisely: Choose regions based on cost considerations and the geographic distribution of your user base.
- Consider Multi-Region Strategies: Explore multi-region deployments for redundancy and cost optimization.

These tips specifically target the major cost factors associated with running Kubernetes clusters. By implementing these strategies and integrating tools, you can optimize your spending on compute resources, storage, network traffic, managed services, load balancers, and geographical considerations. Regularly review and adjust your configurations based on changing requirements to ensure ongoing cost efficiency.

Threads or Twitter?

Shubhendra Singh Chauhan — Thu, 06 Jul 2023 18:53:29 +0000

Threads vs. Twitter: What are the Differences?

Threads and Twitter are both social media platforms that allow users to share short messages with their followers. However, there are some key differences between the two platforms.

Threads

Threads is a standalone app that is owned by Meta (formerly Facebook).
Threads is designed for sharing more personal and intimate content with close friends and family.

Twitter

Twitter is a larger social media platform with over 330 million active users.
Twitter is designed for sharing a wider range of content, including news, events, and personal thoughts.
Twitter messages are not ephemeral, meaning that they remain on the platform indefinitely.
Twitter includes features like hashtags, @mentions, and retweets.

Which Platform is Right for You?

The best platform for you will depend on your individual needs and preferences. If you are looking for a platform to share personal and intimate content with close friends and family, then Threads is a good option. If you are looking for a platform to share a wider range of content with a larger audience, then Twitter is a good option.

Here is a table that summarises the key differences between Threads and Twitter:

Feature	Threads	Twitter
Platform	Standalone app	Website and app
Target audience	Close friends and family	Wider audience
Characters limit	500	280
Repost	Can't see numbers, only notifications for repost	Can see numbers
Hashtags	not available	available
@mentions	yes	yes
Drafts	no	yes

What do you think? Which platform would you prefer?

I would love to hear your thoughts in the comments below.

Building an efficient email marketing application with ChatGPT, ToolJet, and PostgreSQL

Shubhendra Singh Chauhan — Tue, 06 Jun 2023 11:44:47 +0000

In this tutorial, you'll learn how to build an email marketing application using ToolJet, ChatGPT, and PostgreSQL.

The application allows you to add subscribers to your mailing list, group them into categories, and send them newsletters using Brevo (formerly Sendinblue). We'll also use ChatGPT for generating email content. All these features are very easy to integrate with the help of ToolJet.

What is ToolJet?

ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes. With ToolJet, you can create standalone fully-functional full-stack applications or embed applications into other websites.

ToolJet allows you to build applications that use relational and non-relational databases, REST APIs, OpenAI technologies, and cloud storage like Google Cloud Storage, AWS S3, and Minio. It is an excellent development tool helping individuals, developers, and businesses create and ship products faster.

Before we continue, I need your help? 😔
I would be super happy if you could give us a star! And let me also know in the comments section. ❤️

https://github.com/ToolJet/ToolJet

Setting up an ElephantSQL (PostgreSQL) database

ElephantSQL enables us to create a PostgreSQL database on the cloud instead of your local machine. Follow the steps below to create a PostgreSQL database:

Create an ElephantSQL account here.

Add a new database instance. No credit card or billing information is required.

Once you've created the database instance, your database information is displayed.

Congratulations! You've successfully created the database needed for this application. Next, let's design the application interface and connect the database to the application.

Building the application with ToolJet

Here, you'll learn how to create a ToolJet account and build a fully functional application with ToolJet.

If you are new to ToolJet, create an account.

Create a Workspace and a new app called Newsletter Manager.

The application is divided into four pages - the home page, the ViewNewsletter page, the CreateNewsletter page, and the SubscribersManager page. Click on the pages icon on the top left corner to create the three additional pages.

On the home page, you can view all the available newsletters and navigate to the SubscribersManager and CreateNewsletter pages.

The ViewNewsletter page displays the details of a published newsletter.

The CreateNewsletter page enables us to create a newsletter as a draft or publish it by sending it to a group of users.

The SubscribersManager page displays existing subscribers and allows you to add a new subscriber to the list.

The Home page

Here, you'll learn how to build the user interface below.

To create the UI above, you need to add a container element on the canvas that will house all the other UI elements of the application.
Next, drag the Text and Button component from the panel on the right into the container element to create the title and buttons.

In the image above, I added a Tab component below the button and text components. Next, edit the Tab component to contain only the Published and Drafts tab menu by copying the code snippet below into the Tabs input at the right-hand side of the screen.

{{[
        { title: 'Published', id: '0' },
        { title: 'Drafts', id: '1' }
 ]}}

Remove the ToolJet image at the centre of the list and insert the ListView component into the body of the Tab component. Adjust it until it becomes similar to this.

Next, add an on click event on the Subscribers button to navigate users to the SubscribersManager page.

Finally, add an on click event on the Create button that redirects users to the Create Newsletter page.

The View newsletter page

Change the current page to the ViewNewsletter page, place a large container on the canvas, and create a UI similar to the image below.

There are three text components - the application's name, title and content, and the Back button. The Title and content texts are placeholders for the title and content of each newsletter. This page allows us to view the contents of any published newsletter.

Finally, add an on click event on the Back button to redirect users to the Home page.

The Create newsletter page

This page enables you to save and publish a newsletter. It accepts the newsletter's title, email group, and content. You can also use AI-generated content with ChatGPT by providing accurate prompts.

The Save to Draft and Send buttons allow us to save the newsletter or send it to the users' email.

Finally, redirect users to the home when they click the Back button.

The Subscribers manager page

On this page, you can view all the existing subscribers and add a new subscriber to the list.

Design the page as shown below. We have the Title and Button components at the top of the page and the Table component displaying the list of subscribers.

Then, add an on click event on the Add button such that it displays a Modal component that allows you to add the subscriber's details.

Congratulations! You've successfully built the user interface for the application. Next, let's add the required functionalities to the application.

How to communicate with a PostgreSQL database in ToolJet

ToolJet allows us to communicate with external resources or create custom functions via a panel known as Query Panel. In ToolJet, any function that interacts with a database, API, or cloud storage and runs a JavaScript or Python code is called a Query.

Here, you'll learn how to communicate with a PostgreSQL database in ToolJet. First, let me walk you through connecting a PostgreSQL database to ToolJet.

Connecting the PostgreSQL database to ToolJet

Select Global Datasources from the top-right menu bar on your ToolJet dashboard.

Select PostgreSQL from the list of databases under the Global Datasources panel, and provide the required information as shown below.

From the image above, the host is the same as the server name on ElephantSQL (excluding the brackets). The username and database name are the same, and copy and paste the password into its field.

Scroll down the page and click Test Connection.

If the connection is verified, we can start making queries to the database.

Setting up the database structure

You need to create two database tables, one for the Subscribers and another for the Newsletters.

Both the newsletter and subscriber table will have an email_group property to enable us to identify subscribers who belong to a group and send emails to only subscribers within a group.

Therefore, let's create the database tables and add some dummy data. Navigate to the browser section on your ElephantSQL database and execute the code snippet below.

CREATE TABLE Letters(
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    email_group VARCHAR(255),
    published BOOLEAN, published_date VARCHAR(255));

INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('ToolJet v3.0 is live', 'ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes.',  'one', false, '');

INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('Team Community Call is live', 'ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes.',  'two', true,'01-05-2023');

INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('Invitation to product launch', 'It is an excellent development tool helping individuals, developers, and businesses create and ship products faster.',  'three', true, '01-05-2023');

The code snippet above creates the Letters table on the database and adds three data entries: two are published, and the last one is a draft.

Finally, create the Subscribers table.

CREATE TABLE Subscribers (
    name VARCHAR(255),
    email VARCHAR(255),
    organisation VARCHAR(255),
    email_group VARCHAR(255),
);

INSERT INTO Subscribers(name, email, organisation, email_group) VALUES('Jack', 'jack@gmail.com', 'ToolJet',  'one');
INSERT INTO Subscribers(name, email, organisation, email_group) VALUES('Teja', 'teja@gmail.com', 'ToolJet',  'two');
INSERT INTO Subscribers(name, email, organisation, email_group) VALUES('Badri', 'badri@gmail.com', 'ToolJet',  'three');

Querying the PostgreSQL database in ToolJet

To create the email marketing application, you need to create some database queries.

Get all the subscribers.
Add a new subscriber to the list.
Get newsletters saved as drafts.
Get published newsletters.
Save a newsletter as draft.
Save a published newsletter
Get all the details of a published newsletter.

Getting all available subscribers

On the SubscriberManager page we need to display the list of subscribers from the database on the table as shown below.

Therefore, create a new query on the Postgres database called getSubscribers and retrieve all the data from the Subscribers table. Ensure you set the Run this query on application load toggle to true.

Finally, update the the Table data with the data returned from the query.

Adding a new subscriber to the list

On the SubscribersManager page, there is a Add button. When you click on the button, it displays a modal that enables you to add the various details related to a subscriber.

Therefore, you need to execute a createSubscriber query when you click the Add button.

INSERT INTO Subscribers(name, email, organisation, email_group) VALUES('{{components.subscriberName.value}}', '{{components.subscriberEmail.value}}', '{{components.subscriberOrg.value}}',  '{{components.subscriberGroup.value}}');

The code snippet above access the value of the input components on the modal. The components object allows you to access every component's properties.

Finally, let's run the query when a user clicks the Add button and closes the modal immediately.

Getting newsletters saved as drafts

Create a PostgreSQL query that retrieves all the newsletters saved as drafts.

Enable the Transformations below the SQL editor. It enables us to modify the data returned from the query.

const imageURL = "https://www.svgrepo.com/show/34217/image.svg";

return data.map((obj) => {
    return { imageURL, text: obj.title, group: obj.email_group };
});

Update the Draft tab to display the data returned from the getDrafts query.

Each element on the List View displays the content using the listItem property. Therefore, to access each data property, use listItem.<propertyName> - {{listItem.text}} and {{listItem.group}}.

Getting published newsletters

Create a similar query to the getDrafts query, which will return only newsletters whose published attribute is true.

SELECT *
FROM Letters
WHERE published = TRUE;

const imageURL = "https://www.svgrepo.com/show/34217/image.svg";

return data.map((obj) => {
    return {
        imageURL,
        text: obj.title,
        date: obj.published_date,
        group: obj.email_group,
        id: obj.id,
    };
});

The code snippet above returns an image URL, text (title), date, group, and id of the published letters.

Display all the published newsletters in the list view. Recall you need to use the listItem.<property> method to access each attribute returned from the query.

Saving newsletters as draft

Create another query that accepts the values from the createNewsletter page and saves the newsletter as a draft.

INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('{{components.title.value}}', '{{components.content.value}}', '{{components.group.value}}', false, '');

The query is executed when a user clicks the Save to Drafts button in the image below. You can redirect the user to the home page after saving the newsletter.

Saving published newsletters

Add another query called savePublished that accepts the values from the input field and saves it as a published newsletter.

INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('{{components.title.value}}', '{{components.content.value}}', '{{components.group.value}}', true, CURRENT_DATE);

Run the query when a user clicks on the Send button. In the upcoming section, after saving the published newsletter, we'll also send it to the subscribers in that email group.

Getting the details of a published newsletter

We have a page called -viewNewsletter that is supposed to display the title and content of a published post when a user clicks on them.

To do this, you need to get the id of the selected newsletter and query the Letters database using the id.

First, add the set Variable action on each row of the list view. When you click on each list item, it sets the variable's value to its index in the list view.

The variable - selectedListviewId contains the selected item's index. Therefore, you can get the item's ID from the data returned from its query.

SELECT *
FROM Letters
WHERE id = {{queries.getPublished.data[variables.selectedListviewId].id}}

The code snippet above accesses the variables object and gets the selected item's id using its index. Then, you can filter the Letters database using the id.

Since we only need the title and content attribute of the newsletter. Therefore, you can update the data returned with only the required data.

return data.map((item) => {
    return { title: item.title, content: item.content };
});

Next, run the query when a user clicks on each item of the List View.

Finally, redirect the user to the View Newsletter page when they click on each item in the List View to enable them to view the title and content of the newsletter.

How to communicate with ChatGPT in ToolJet

Here, you'll learn how to communicate with ChatGPT via OpenAI in ToolJet by accepting prompts and generating email contents.

Setting up an OpenAI account

Click Personal on the navigation bar and select View API keys from the menu bar to create a new secret key.

Generate a new API key and copy it somewhere on your computer. We'll use it in the upcoming section.

Communicating with the OpenAI API in ToolJet

Under the Global Datasources tab on your dashboard, click Plugins, and select OpenAI. Then, paste your API key and organization ID into the input fields and test the connection.

You can now access the OpenAI data source from the query panel.

If you don’t have an organization ID, use “Personal”.

Select OpenAI from the Query Panel and set its content to draft an email using ChatGPT.

Draft a newsletter about {{components.prompt.value}}

Run the query when a user clicks the Ask button on the createNewsLetter page.

How to send emails via the Sendinblue API in ToolJet

Sendinblue is a digital marketing tool that provides Email, SMS, Facebook, Chat, and more, via one platform to help grow businesses by building stronger customer relationships.

In this section, you'll learn how to integrate and send emails via Sendinblue in ToolJet. First, you need to log in or create a SendinBlue account.

Select SMTP and API on your dashboard, generate an SMTP key, and copy it somewhere on your computer. You'll need it shortly.

Return to your ToolJet app, add a new SMTP data source, and fill in the required credentials. Your password is the generated SMTP key.

If successfully connected, it will display "Connection Verified". Then you can start sending emails.

Next, create the query for sending the emails. Provide your email, name, the recipient's email, subject, and title components.

From the image above, the email recipient is the data returned from another query - {{queries.querySubscriber.data}}. Since you need to send the newsletter to a particular group of subscribers when you select a group on the createNewsLetter page. Therefore, the querySubscriber query fetches the list of subscribers under a particular group.

The image above returns an array of the subscribers’ email related to the selected group.

Congratulations! You’ve completed this project. Here is a working demo of the application:Toolbus newsletter manager

You can also download its JSON file and import it into a ToolJet app, but you'll need to provide your Sendinblue credentials and OpenAI API key.

Conclusion

So far, you've learnt how to

add a PostgreSQL database to ToolJet
send emails within a ToolJet application using Sendinblue,
communicate with ChatGPT in ToolJet, and
build full-stack applications in a few minutes with ToolJet.

ToolJet is both an excellent development tool and open-source - meaning its code is readily available for everyone to modify and improve. It has a large community of developers and talented contributors constantly maintaining and improving the software. As a user, you can be sure of getting the best performance when you use ToolJet.

Are you interested in contributing to ToolJet? Feel free to check out our GitHub repo- https://github.com/ToolJet/ToolJet to contribute and raise issues about ToolJet.

Thank you for reading!

Building an intelligent CRM using ChatGPT, PostgreSQL, and ToolJet

Shubhendra Singh Chauhan — Mon, 22 May 2023 09:48:05 +0000

In this tutorial, you'll learn how to build a CRM (Customer Relationship Management) application using ChatGPT, ToolJet, and PostgreSQL.

Introduction

The CRM application will allow you to add new users or customers to a PostgreSQL database, view their details on a dashboard, generate email templates using ChatGPT, and send them emails via Sendinblue.

We'll use ToolJet for the application interface in this tutorial.

What is ToolJet?

Before we continue, I need your help? 😔
I would be super happy if you could give us a star! And let me also know in the comments section. ❤️

https://github.com/ToolJet/ToolJet

Setting up an ElephantSQL (PostgreSQL) database

ElephantSQL enables us to create a PostgreSQL database on the cloud instead of your local machine. Follow the steps below to create a PostgreSQL database:

Create an ElephantSQL account here.

Add a new database instance. No credit card or billing information is required.

Once you've created the database instance, your database information is displayed.

Congratulations! You've successfully created the database needed for this application. Next, let's build the application interface and connect the database to the application.

Building the application with ToolJet

Here, you'll learn how to create a ToolJet account and build a fully functional application with ToolJet.

If you are new to ToolJet, create an account.

Create a Workspace and a new app called CRM application.

Build a user interface similar to the image below.

To create the UI image above, you will first add a large container on the canvas that will house all the other UI elements of the application. Next, add the three container elements at the top of the page, the Add Lead button component and the Table component.

ToolJet enables you to create applications' interfaces by dragging and dropping various UI components on the canvas.

After recreating the UI, add an Action button with the text "Send Email" to the table. (We'll configure it later to enable us to email each user).

To complete the UI, we need an interface that allows us to add a user to the database and another for editing and generating the email content for each user.

To do this, we'll use the Modal component. We'll display a modal when we click on the Add Lead button - for adding new data and the "Send Email" action button - for entering the email content for the user.

The “Add New Lead” Modal

To display the Add New Lead Modal component, you need to create the modal and add an onClick event to the "Add Lead" button to show the modal.

Add the following components to the modal, and remember to close the modal when you click the modal button.

The “Send Email” modal

The Send Email modal allows us to enter the user's email, email subject, and content. You can also generate email content using the ChatGPT API. You'll learn how to do that in the upcoming sessions.

The toggle switch has an onChange event that hides the ChatGPT prompt input field and button whenever you don't need it.

Remember to close the modal when a user clicks the Send Email button.

Congratulations!🎉 You've completed building the UI for this application. In the upcoming sessions, you'll learn how to connect the user interface to a PostgreSQL database, communicate with ChatGPT, and send emails using Sendinblue.

How to communicate with a PostgreSQL database in ToolJet

Here, you'll learn how to communicate with a PostgreSQL database in ToolJet. First, let me walk you through connecting a PostgreSQL database to ToolJet.

Connecting a PostgreSQL database to ToolJet

Select PostgreSQL from the list of databases under the Global Datasources panel, and provide the required information as shown below.

From the image above, the host is the same as the server name on ElephantSQL (excluding the brackets). The username and database name are the same, and copy and paste the password into its field.

Scroll down the page and click Test Connection.

If the connection is verified, we can start making queries to the database.

Querying a PostgreSQL database in ToolJet

Here, you'll learn how to query data from the database and display them accordingly within the application.

Before we begin, let's add some data to the database. Click PostgreSQL on the Query Panel, select SQL mode, and run the code snippet below to create a new table containing the data below.

CREATE TABLE Users (Name varchar(255), Email varchar(255), Phone varchar(255), Organisation varchar(255), Designation varchar(255), Status varchar(255));
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Teja', 'teja@gmail.com', '+555-34569', 'ToolJet', 'Developer Advocate', 'Customer');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Badri', 'badri@gmail.com', '+555-59659', 'ToolJet', 'Engineering Manager', 'Prospect');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Jack', 'jack@gmail.com', '+555-44449', 'ToolJet', 'Software Engineer', 'Lead');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Nora', 'nora@gmail.com', '+555-46249', 'ToolJet', 'Product Manager', 'Customer');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Emily', 'emily@gmail.com', '+555-47893', 'ToolJet', 'Product Designer', 'Prospect');
INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('Dave', 'dave@gmail.com', '+555-49313', 'ToolJet', 'Data analyst', 'Lead');
SELECT * FROM Users;

You can view the data on ElephantSQL once it has been uploaded.

Next, rename the query to getTableData, update the SQL editor to display all the table contents, and save the query.

Update the Table component to display the data retrieved from the getTableData query.

Since we've displayed the contents on the table. Next, let's show the number of Customers, Leads, and Prospects available on the database.

Create three new queries based on the PostgreSQL database called getLeadsCount, getProspectsCount, and getCustomersCount.

Update the SQL editor for each query to retrieve the number of data under each category as done below, and save them.

SELECT COUNT(*)
FROM Users
WHERE Status IN ('Lead');
/*-- change 'Lead' for leads, 'Prospect' for prospects, 'Customer' for customers. ---*/

Display the number of data returned from each query by replacing the text content within the container as done below.

{{queries.getProspectsCount.data[0].count}}

//(Use {{queries.getLeadsCount.data[0].count}} for leads and {{queries.getCustomersCount.data[0].count}} for customers)

Finally, switch on the "Run this query on application load?" toggle for the getTableData and the other three queries.

Congratulations! You've learnt how to query and display the data received within the application. Next, I will walk you through adding new data to the database via the application interface.

Adding new users to the database

Here, you'll learn how to accept that data from the modal and add the user's data to the database.

Create a new query related to the PostgreSQL data source that accepts the values from the modal and sends an SQL query to add the data to the database.

Ensure that the code snippet below matches the components' names from the modal. Copy the code snippet below into the SQL editor panel and save the query.

INSERT INTO Users(name, email, phone, organisation, designation, status) VALUES('{{components.name.value}}', '{{components.email.value}}', '{{components.phone.value}}', '{{components.organisation.value}}', '{{components.designation.value}}', '{{components.status.value}}');

Finally, add an onClick event to the Add Lead button to run the query on button click.

How to communicate with ChatGPT in ToolJet

Here, you'll learn how to communicate with ChatGPT via OpenAI in ToolJet.

Setting up an OpenAI account

Click Personal on the navigation bar and select View API keys from the menu bar to create a new secret key.

Generate a new API key and copy it somewhere on your computer. We'll use it in the upcoming section.

Communicating with the OpenAI API in ToolJet

Under the Global Datasources tab on your dashboard, click Plugins, and select OpenAI. Then, paste your API key and organization ID into the input fields and test the connection.

You can now access the OpenAI data source from the query panel.

If you don’t have an organization ID, use “Personal”.

Select OpenAI from the Query Panel and set its content to draft an email using ChatGPT.

Draft an email to {{components.recipient.value}} about {{components.chatgptprompt.value}}

Run the query when a user clicks the Ask button on the modal.

Finally, to display a selected user's email automatically on the modal, set the default value of the recipient's email input to {{components.table1.selectedRow.email}}. This automatically sets its value to the email of the selected user.

How to send emails via the Sendinblue API in ToolJet

Sendinblue is a digital marketing tool that provides Email, SMS, Facebook, Chat, and more, via one platform to help grow businesses by building stronger customer relationships.

In this section, you'll learn how to integrate and send emails via Sendinblue in ToolJet. First, you need to log in or create a SendinBlue account.

Select SMTP and API on your dashboard, generate an SMTP key, and copy it somewhere on your computer. You'll need it shortly.

Return to your ToolJet app, add a new SMTP data source, and fill in the required credentials. Your password is the generated SMTP key.

If successfully connected, it will display "Connection Verified". Then you can start sending emails.

Next, create the query for sending the emails. Provide your email, name, the recipient's email, subject, and title components.

Finally, save the query and run it when the Send Email button on the modal is clicked.

Here is a working demo of the application: https://tooljet-stny.onrender.com/applications/7a8867e3-3cea-49e3-89e7-8da39038b875

You can also download its JSON file and import it into a ToolJet app, but you'll need to provide your Sendinblue credentials and OpenAI API key.

Conclusion

So far, you've learnt how to

add a PostgreSQL database to ToolJet
send emails within a ToolJet application using Sendinblue,
communicate with ChatGPT via OpenAI in ToolJet, and
Build full-stack applications in a few minutes with ToolJet.

Are you interested in contributing to ToolJet? Feel free to check out our GitHub repo- https://github.com/ToolJet/ToolJet to contribute and raise issues about ToolJet.

Thank you for reading!

Build your own CMS using low-code

Shubhendra Singh Chauhan — Tue, 28 Mar 2023 06:21:33 +0000

In this tutorial, We will build CMS(Content Management System) using the ToolJet which is a lowcode application development platform. The CMS can be used to perform CRUD operations to the MongoDB which is used as the database for the NextJS application.

Overview
Prerequisites
Setting up the database
Connecting MongoDB to ToolJet
Building the UI, Queries and connecting them
Connecting to NextJS app

Overview

A content management system (CMS) is an application used for managing content creation and modification, usually articles and blog posts containing images and videos. WordPress CMS is one of the popular CMS. CMSs make it easy to perform CRUD operations (create, read, update, and delete) without coding knowledge.
ToolJet is a free and open-source low-code app development platform that allows you to build applications quickly and has more than 30+ datasources including MongoDB that will let you seamlessly perform operations on your database. You can create a free account on ToolJet Cloud or run it on your local machine.
Many developers use databases like MongoDB and frameworks like NextJS for their full-stack web applications. Sometimes performing CRUD operations can be time taking for developers and can be hectic for someone who is not familiar with running database queries. In this tutorial, we will build an app using ToolJet that will solve these problems. The CMS app will be connected to the MongoDB database to perform CRUD operation, and the changes will be reflected on the NextJS application.

Want to use the app without going through the complete tutorial?

Import the exported application JSON file to your ToolJet account. Here's the file.
Once imported the application, you'll need to connect the MongoDB datasource using host details or connection string.

Prerequisites

MongoDB database: You can set up the MongoDB database locally or use Atlas. For this tutorial, we are going to use Atlas.
ToolJet (https://github.com/ToolJet/ToolJet): A free and open-source low-code platform that allows you to build applications quickly. Sign up here.
NextJS app: We will learn how to build one sample app using the command line later in this tutorial.

Setting up the database

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 Cluster0 deployed. Go to the browse collections and create a new database cms with three collections posts, authors, and tags.

posts- this collection will store all the blog posts information
authors- this collection will store information on all the authors
tags - this collection will store the tags

Let's insert at least one sample document in each collection. For posts:

_id: the object id will be generated by MongoDB automatically.
title: enter the title of the blog posts.
subtitle: enter the subtitle
content: enter the content/body of blog posts
cover: enter the URL of the cover image
author_name: enter the name of the author
tag_name: enter the tag name
published_on: enter the date in DD/MM/YYYY format The data type of all the records should be string.

Similarly, we will insert documents in authors and tags. For authors

_id: the object id will be generated by MongoDB automatically.
author_name: name of the author
author_email: email of the author
author_image: URL of author's headshot image

For tags:

_id: the object id will be generated by MongoDB automatically.
tag_name: name of the tag

Once you have inserted the sample documents in all three collections, we are good to go. Our database is ready.

Now, let's go to ToolJet and connect the database.

Connecting MongoDB to ToolJet

Let's go to the ToolJet dashboard and create a new app. Once you click the New App button, an untitled app will open in the app builder. You can rename the app from the toolbar. On the left sidebar, you'll find the third option for adding or editing datasources.

Click on the + add datasource and a modal will pop up with a list of all the available datasources. Select MongoDB from the list and enter the credentials for connecting.

For this tutorial, we are using MongoDB Atlas to connect to MongoDB datasource using the connection string.

You can find your connection string by going to the MongoDB Atlas dashboard. Go to Database Deployments on the Atlas and click on the Connect button next to the cluster name ( Cluster0 in our case). A connection dialog will pop up 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 - click on the copy button next to it to copy the string.

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

So, the actual connection string will be: mongodb+srv://testusername:fakepassword@cluster0.nc8nial.mongodb.net/cms

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.

Now, that we have successfully connected the MongoDB database, we can build the user interface of the CMS app.

Build the UI of the CMS app

Let's start building the UI of the CMS application using ToolJet, which is a low-code application development platform.

The CMS application is a multi-page application that will have the following 5 pages:

All Posts

New Post

Edit Post

View Post

Authors and Tags

In the previous section, we connected the MongoDB datasource to a new application. Now, all we need to do is build the UI and then create queries and connect them with the UI.

The very first thing we are going to do is create Pages in our application. We can find the Pages option on the left sidebar from where we can add the pages to the application. Let's add the following pages:

all_posts
new_post
edit_post
view_post
authors_tags

💡 Before we begin with UI, it is recommended to learn about ToolJet's app builder: https://docs.tooljet.com/docs/app-builder/overview

All Posts: UI

The All Posts page will show the list of all the blog posts. This is going to be the home page of the application, and from this page, we can navigate to other pages.

This page will use the listview component to show a list of blogs and buttons to navigate other pages. Let's build the UI:

Let's drag a container on the canvas and increase its height to reach the bottom and its width to both sides. You can click on the handle of the container to edit its properties.
Drag a text component and set its value to <h1>All Posts</h1>. Yes, we can use HTML tags inside the text component to format text value.
Drag a divider below the text component.

Now drag two button components above the right side of the divider and style the buttons.
For the first button, we will set the button text to New Post and edit its Styles. Set the button color to #14171a , text color to white/#ffffff, loader color to white, border-radius to 2 , border color to #14171a
Add an Event Handler to the New Post button, choose switch page action, and then select the new_post from the dropdown. Now, whenever the button is clicked New Post page of the application will be loaded.

For the second button, we will set the button text to Authors and Tags and edit its Styles. Set the button color to white/#ffffff , text color to #14171a, loader color to #14171a, border-radius to 2 , border color to #14171a
Add an Event Handler to the Authors & Tags button, choose switch page action, and select the authors_tags from the dropdown. Now, whenever the button is clicked Authors & Tags page of the application will be loaded.

Now drag a listview component below the divider. Go to its Styles and set the border color to white.

Let's add components inside the listview. Remove the image component, and add three text components and two buttons as shown in the image below.

Currently, the listview looks very rough since we haven't styled the nested components (text and buttons) yet. To make it look better, we can add some styling to the components and some placeholder text until we create the query for the data to be loaded into the listview.
Let's edit the properties and styles of the nested text components and buttons:
- text1: Text = Title of the blog , Font weight = Bold , Font size = 16
- text2: Text = By Author - 06/02/2023 , Font weight = Lighter
- text3: Text = Tag , Font weight = Lighter
- button1: Button text = 👁️ , Background color = white , Border radius = 2 , Border color = #e2e4e4
- button2: Button text = ✏️ , Background color = white , Border radius = 2 , Border color = #e2e4e4

For the view/👁️ and edit/✏️ buttons inside listview, we need to add event handlers. On both buttons, we need to add an event handler for set variable event that creates a variable selectedListViewIndex and the variable value will be {{listItem._id}} - this value will be there once we create the query. Another event handler that we need to add is to switch the page, for the edit button link edit_post page and for the view button link view_post page.

The UI of the All Posts page is almost finished, now let's go ahead and build the queries and connect to the UI.

All Posts: Queries

Let's build the required query for loading the data on All Posts page. Since we are only loading the list of all the blogs from the database on the listview, we need to build a single query:

posts

Go to the query panel at the bottom of the app builder, create a new MongoDB query, and name it as posts
Select the Find Many Operation from the dropdown options and then enter the Collection name as posts which is the name of the collection in our cms db that we created in MongoDB Atlas
From the Advanced section, toggle on the Run query on application load? option, and then save the query. This will execute the query every time the app is loaded.
Run the query to trigger it, and we can check the data returned by the query from the inspector panel on the left sidebar.

authors

Create a new MongoDB query, and name it as authors
Select the Find Many Operation from the dropdown options and then enter the Collection name as authors which is the name of the collection in our cms db that we created in MongoDB Atlas
From the Advanced section, toggle on the Run query on application load? option, and then save the query. This will execute the query every time the app is loaded.
Run the query to trigger it and we can check the data returned by the query from the inspector panel on the left sidebar.

All Posts: Connecting queries data to UI

Now, that we have the posts, authors, and tags data from the database, we can populate the listview component. Edit the properties of the ListView component and set the List data value to {{queries.posts.data}}.

The rows will be added to the listview but the actual data won't show up because the components inside the listview currently hold the placeholder values. Let's remove the placeholder values from the components inside the ListView. Go to the text component whose value is Title of the blog and set its value to {{listItem.title}}

Set the value of the second text component to By {{listItem.author_name}} - {{listItem.published_on}}
Set the value of the third text component to {{listItem.tag_name}}

We are finally done with the All Posts page, all we need to do now is build the UI of the remaining 4 pages and then connect the queries.

New post: UI

The New Post page will be used to create a new blog post and insert it into the collection posts. Let's click on the New Post button on the top of the All Post page and switch to the new_post page as we have already added the event handler to the button.

This page will use the container component as the body and we will nest components like text, text-input, dropdown, etc. to create a form-like structure for getting the input from the users. Let's build the UI:

We are going to build a UI similar to the UI that we build for the All Posts page. First, we will drag a container component onto the canvas and then we will use the text component for the Title of the page i.e New Post with a divider component below it.
On the right side of the divider, we can simply copy and paste the Authors & Tags button from the All posts page and paste it into the New Post page. Change the button text to < Back and add the event handler to switch the page to all_posts

Now we are going to drag components and build a form-like structure below the divider component.
- We are going to drag a text component, set its value to Title, set Font weight to Bold, and Font Size to 16
- We will copy-paste(using keyboard shortcuts) the Title text component 5 more times and then change their text to Subtitle, Content, Cover Image, Author, and Tag, respectively.
Now, we will place the following components next to the headings:
- Title: text input component and set placeholder to Title
- Subtitle: text input component and set placeholder to Subtitle
- Content: text area component and set placeholder to Content
- Cover Image: text input component and set placeholder to Cover Image
- Author: dropdown component and set placeholder to Author
- Tag: dropdown component and set placeholder to Tag
Set the value of the border radius of all the input components to {{2}} to make them look better

Our form is almost complete, now at the bottom of the page we will add a button. We will copy-paste the button from the All Posts page and change the button text to Submit. Remove the existing event handlers, and add an event handler to switch the page to all_posts . We will add another event handler once we create the query for submitting the form.

The UI for creating a new blog post is ready, now let's create the query for inserting the form values into the database.

New post: Queries

add_post

Create a new MongoDB query, and name it as add_post
Select the Insert One Operation from the dropdown options and then enter the Collection name as posts which is the name of the collection in our cms db that we created in MongoDB Atlas
In the Document field, enter the following:

{title: "{{components.textinput1.value}}", subtitle: "{{components.textinput2.value}}", content: "{{components.richtexteditor1.value}}", cover: "{{components.textinput3.value}}", author_name: "{{components.dropdown1.value}}", tag_name: "{{components.dropdown2.value}}", published_on: "{{moment().format("DD/MM/YYYY")}}" }

In the code snippet above, we are dynamically getting values from the input components.
Go to the bottom of the query manager and add the event handler to run the query on the Query Success event and select the posts query. Doing this will trigger the posts query every time this add_post query is successful or simple words whenever the user submits the blog then the list of the blogs will be refreshed on the front-end. Finally, Save the query.

New post: Connecting queries data to UI

On this page, all we need to do is connect the query that we created in the previous step to the submit button so that whenever the button gets clicked, the query for inserting the post is triggered and then the page switches to the home page i.e. All Posts page.

Click on the handle of the Submit button, and go to the Events section under its properties.
While building the UI we already added an event handler for switching the page to all_posts, now we need to add another event handler to Run the query on the On Click event -> Select the add_post query. Move the Run query event handler above the Switch Page event handler to ensure the query runs first and then the page switches to the home page.

Edit post: UI

This page is going to be the clone of the New Post page; we will replace the Submit button with the update button and will add another button for deleting the selected post. Let's go!

Let's go to the New Post page and select the container. Press ctrl/cmnd+c, go back to the Edit Post page and press ctrl/cmnd+v, doing this will clone the container entirely including the nested components from the New Post page.
Change the Title from New Post to Edit Post
Set the default values of the following input components:
- Default Value for Title's text input : {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].title}}
- Default Value for Subtitle's text input : {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].subtitle}}
- Default Value for Content's text area : {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].content}}
- Default Value for Cover Image's text input: {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].cover}}
- Default Value for Author's dropdown component: {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].author_name}}
- Option Value for Author's dropdown component: {{queries.authors.data.map(i => i.author_name)}}
- Option Labels for Author's dropdown component: {{queries.authors.data.map(i => i.author_name)}}
- Default Value for Tag's dropdown component: {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].tag_name}}
- Option Value for Tag's dropdown component: {{queries.tags.data.map(i => i.tag)}}
- Option Labels for Tag's dropdown component: {{queries.tags.data.map(i => i.tag)}}

Let's go to Submit button and change the button text to Update. We will keep both the event handlers as it is and will replace the query in the Run Query handler later once we build the query.
Copy the Back button from the top and paste it below next to the Update button. Change the button text to Delete. We will add the event handler in the next section when we create the query. Go to the Styles, change the button text and border color to red.

The UI of the Edit Post section is finished. We also updated the default values of the components so that whenever a listItem is clicked from the homepage the details of the selected blog details get updated on the edit page field values.

Edit post: queries

delete_post

Create a new query from the query panel, and name it delete_post
Select the Find one and delete Operation from the dropdown options and then enter the Collection name as posts
In the Filter field value enter:

{"title":"{{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].title}}"}

Scroll to the bottom, add an event handler to run the query on the Query Success event and select the posts query and then Save the query.

update_post

Create another MongoDB query from the query panel, and name it update_post
Select the Find one and update Operation from the dropdown options and then enter the Collection name as posts
In the Filter field value enter:

{"title":"{{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].title}}"}

In the Update field value enter:

{ $set: {title:"{{components.textinput1.value}}", subtitle:"{{components.textinput2.value}}", content:"{{components.richtexteditor1.value}}", cover:"{{components.textinput3.value}}", author_name:"{{components.dropdown1.value}}", tag_name:"{{components.dropdown2.value}}"} }

Scroll to the bottom, add an event handler to run the query on the Query Success event, and select the posts query and then Save the query.

Edit post: Connecting queries data to UI

Now, all we need to do is update the event handlers on the Update and Delete buttons.

Click on the handle of the Delete button to edit its properties. Go to Events, and change the query in the Run Query event to delete_post

Similarly, we will update the event handler of the Update button. Open its properties, go to Events, and change the query in the Run Query event to update_post

View post: UI

This page will be loaded when the 👁️/view button is clicked next to the item in the listview on the All Posts page. Let's build the UI:

Go to the home page/All Posts page and click on the 👁️/view post button next to the list item to load the view_post page
Once the view_post page is loaded, drag a container component onto the canvas. Expand the container completely both vertically and horizontally to fit the entire canvas.
Go to either of the page New Post or Edit Post, copy the back button and paste it on the view_post page. Drag a divider component below the button.
Now let's drag the following components and set their properties and styles:
- text component for showing the Title: text = {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].title}} , font weight = bold, font size = 16
- text component for showing the Subtitle: text = {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].subtitle}} , font weight = bold, font size = 15
- text component for showing the Author: text = Author: {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].author_name}} , font weight = bold, font size = 14
- image component for displaying the Cover Image: URL = {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].cover}}
- text component for showing Content: text = {{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].content}} , font weight = normal, font size = 14

View post: Queries

This page doesn't requires any queries. The data on this page is loaded using the queries that we created previously.

View post: Connecting queries data to UI

We have already connected the data while building the UI. We are using variables that we created previously to filter the data according the clicked list item.
For ex: for loading the Title of the selected list view item we have set the value:

{{queries.posts.data.filter(post => post._id == variables.selectedListViewIndex)[0].title}}

Authors & Tags: UI

We are finally at the last page of the application. In this page we are going to display the list of all the author and all the tags. From this page we can perform CRUD operations on authors and tags collection in database. Let's build the UI:

Go to the homepage/All Posts page of the application and click on the Authors & Tags button on the page, this will redirect you to the authors_tags page
Drag a container onto the canvas and resize(both vertically and horizontally) it to fit the canvas. Copy the back button from any of the other pages and paste it on the top-right of this page
Let's add two text components use html tags to make them into Title: Authors, Tags
Add two divider components below the titles and two tables respectively.
Now, we will be adding two buttons, one for adding the authors into the database and the other for adding tags into the database. We can copy the New Post button from the home page to keep the styling same. Remove the event handlers and change the button text to +Add.
Drag three modals somewhere below the container. These modals will be shown when the following buttons are clicked: +Add button for Authors, +Add button for tags, and View action button on Authors table.
Toggle off the Use default trigger button for all the three modals.
Go to the properties of +Add button for Authors, add an event handler to show the modal1. Click on the button to show the modal1. Once the modal loads click somewhere outside the modal, then drag the components from the components library onto the modal:
- 3 text components: Author Name, Author Email, and Author Image
- 3 text input components : name, email, image URL
- A button component for triggering the query that we will create in the next section, set the button text to Add

Just like the +Add button for Authors, we are going to add an event handler on the +Add button for tags to trigger modal2, open the modal and add the following components inside the modal:
- 1 text input components: Tag
- A button component for triggering the queries that we will create in the next section, set the button text to Add Tag

Go to the Authors Table, add two Action buttons: View and Remove. On View action button, we will add an event handler to show the modal3 and the Remove action button will trigger the query that will remove the selected author from the collection.
Click on the View button to show the modal, add components inside the modal3 just like we have added components in the previous 2 modals:
- 2 text components: text = {{components.table1.selectedRow.author_name}} & {{components.table1.selectedRow.author_email}}, keep the font weight = bold for the first text component.
- 1 image component to display the author image: {{components.table1.selectedRow.author_image}}
Go to the Tags Table, and add one Action button: Delete. This action button will trigger the query that will remove the selected tag from the collection.

Authors & Tags: Queries

add_authors

Create a new MongoDB query, name it add_author, select insert one from the operations dropdown, and enter authors in the collection field.
In the Document field, enter:

{author_name: "{{components.textinput1.value}}", author_email: "{{components.textinput2.value}}",author_image: "{{components.textinput3.value}}" }

Go to the bottom, and add an event handler to run authors query on Query Success Event. Finally, Save the query.

add_tags

Create a new MongoDB query, name it add_tag, select insert one from the operations dropdown, and enter tags in the collection field.
In the Document field, enter:

{tag:"{{components.textinput4.value}}"}

Go to the bottom, and add an event handler to run tags query on Query Success Event. Finally, Save the query.

delete_author

Create a new MongoDB query, name it delete_author, select delete one from the operations dropdown, and enter authors in the collection field.
In the Filter field, enter:

{"author_name":"{{components.table1.selectedRow.author_name}}"}

Go to the bottom, and add an event handler to run authors query on Query Success Event. Finally, Save the query.

delete_tag

Create a new MongoDB query, name it delete_tag, select delete one from the operations dropdown, and enter tags in the collection field.
In the Filter field, enter:

{"tag":"{{components.table2.selectedRow.tag}}"}

Go to the bottom, and add an event handler to run authors query on Query Success Event. Finally, Save the query.

Authors & Tags: Connecting queries data to UI

Let's connect the queries to the table components to display the data and the button components for triggering the insert and delete queries:

Go to the Author's table, edit its properties, and set table data to {{queries.authors.data}} . The columns will be auto-populated. Enable the highlight selected row from the options and set the loading state of the table to {{queries.delete_author.isLoading || queries.authors.isLoading}}
Edit Remove action button and add the event handler to run the delete_author query. This will remove the author from the database whenever the action button is clicked.

Click on the +Add Authors button to show the modal, inside modal, there is a Add button for submitting the form. Add the following event handlers to this button:
- 1st event handler to run the query: add_author
- the next 3 event handlers to control the component. Components will be the three text inputs that are there in the modal and the Action will be Clear . Adding these event handlers will clear the form as soon as the add_author query is triggered.
- last event handler for closing the modal.

Now, we are going to add the event handlers to +Add button for the Tags:
- 1st event handler to run the query: add_tag
- the next event handler to control the component. Select the text input component that is there in the form and set the Action to Clear .
- last event handler for closing the modal.

Finally, go to the Tags table, and set the table data to {{queries.tags.data}} . The columns will be auto-populated. Enable the highlight selected row from the options and set the loading state of the table to {{queries.delete_tag.isLoading || queries.tag.isLoading}}
Edit delete action button and add the event handler to run the delete_tag query.

Connecting to NextJS app

Using the connection string, we can connect the MongoDB Atlas database to any NextJS app. In this section, we will locally set up a NextJS application and then connect it to the MongoDB database to load the data on the front end.

Requirements:

MongoDB Atlas database
NodeJS 12+
npm and npx
Vercel (Optional if you want to deploy your NextJS app)

Building NextJS app

Go to the terminal, and enter the following command:

npx create-next-app --example with-mongodb nextblog

We are using the npx create-next-app command and are passing the --example with-mongodb parameter which will tell create-next-app to bootstrap our app with the MongoDB integration example. Finally, nextblog is the name of our application.

Executing this command will take a couple of seconds to download and install all the npm dependencies, but once they're downloaded and installed, navigate to your project directory by running:

cd nextblog

Install all the npm dependencies by running:

npm install

Now, let's start up our application. To start our Next.js app, execute:

npm run dev

Once the app is built successfully, we can navigate to localhost:3000 to see our app live in action.

We will get an error similar to the one in the screenshot above because the app is built but we haven't connected our MongoDB database yet to the application. So let's do that.

Connecting MongoDB

On the nextblog directory, we can find the env.local.example file. We can rename the file to env.local and then edit it.
In this file, we will need to enter the value for MONGODB_URI and MONGODB_DB variable. MONGODB_URI is nothing but the connection string till / and MONGODB_DB will be the name of the database. Go to the beginning of the tutorial to learn how we can obtain the connection string from the Atlas. The env.local file will look like this:
As soon as you save these environment variables, go to the browser and you'll see that the error is gone and with-mongodb Next.js app welcome page is loaded.

Querying MongoDB with Next.js

The first example we'll look at is building and exposing an API endpoint in our Next.js application. To create a new API endpoint route, we will first need to create an api directory in our pages directory, and then every file we create in this api directory will be treated as an individual API endpoint.

Let's go ahead and create the api directory and a new file in this directory called posts.js. This endpoint will return a list of all the blog posts from our posts collection in the MongoDB database. The implementation for this route is as follows:

import { connectToDatabase } from "../../lib/mongodb";


export default async function handler(req, res){
    const {db} = await connectToDatabase();

    const data = await db.collection("posts").find({}).toArray();

    res.json(data);
}

Go to the lib/mongodb.ts file and update the existing code to the following code:

import { MongoClient } from 'mongodb'

const { MONGODB_URI, MONGODB_DB } = process.env

if (!MONGODB_URI) {
  throw new Error(
    'Please define the MONGODB_URI environment variable inside .env.local'
  )
}

if (!MONGODB_DB) {
  throw new Error(
    'Please define the MONGODB_DB environment variable inside .env.local'
  )
}

/**
 * Global is used here to maintain a cached connection across hot reloads
 * in development. This prevents connections growing exponentially
 * during API Route usage.
 */
let cached = global.mongo

if (!cached) {
  cached = global.mongo = { conn: null, promise: null }
}

export async function connectToDatabase() {
  if (cached.conn) {
    return cached.conn
  }

  if (!cached.promise) {
    const opts = {
      useNewUrlParser: true,
      useUnifiedTopology: true,
    }

    cached.promise = MongoClient.connect(MONGODB_URI, opts).then((client) => {
      return {
        client,
        db: client.db(MONGODB_DB),
      }
    })
  }
  cached.conn = await cached.promise
  return cached.conn
}

Now, if we navigate to localhost:3000/api/posts, we'll see a result that looks like this:

Finally, let's update the pages/index.ts file to use this JSON response and display the formatted data on the homepage.

import Head from 'next/head'
import { connectToDatabase } from '../lib/mongodb'
import { InferGetServerSidePropsType } from 'next'
import { title } from 'process';

export async function getServerSideProps(context) {
  const { db } = await connectToDatabase()

  const data = await db.collection("posts").find({}).toArray();

  const allposts = JSON.parse(JSON.stringify(data));

  return {
    props: { posts: allposts },
  }
}

export default function Home({ posts }) {

  console.log(posts);

  return (
    <div>
      <Head>
        <title>Nextbnb</title>
        <link rel="icon" href="/favicon.ico" />
        <link href="https://unpkg.com/tailwindcss@^1.0/dist/tailwind.min.css" rel="stylesheet"></link>
      </Head>

      <div className="md:container md:mx-auto">
        <p>All Posts</p>
        <ul>
          {posts.map(post => (
            <li key = {post._id}>{post.title} by {post.author_name} on {post.published_on}</li>
          ))
          }
        </ul>
      </div>

    </div>
  )
}

Finally, navigate to http://localhost:3000/ !

Tada 🎉 Now you can see the posts collection data on the homepage of the application. This is just the rough presentation of the data on the frontend - you can make further changes to improve the UI.

Putting it all together

In this tutorial, we walked through the MongoDB Atlas and created a database with three different collections, learned how to quickly build a multipage application using ToolJet, and finally, we connected our MongoDB database to the Next.js application and executed queries.

If you have any questions or feedback, reach out through the ToolJet Slack community and let me know what you want to build with ToolJet and MongoDB.

Build a Ticket Triaging App with Baserow and ToolJet

Shubhendra Singh Chauhan — Thu, 22 Sep 2022 11:03:10 +0000

In this tutorial, we will learn how to build an application for internal tickets triaging and for this we will be leveraging the power of Baserow for the backend and ToolJet for the frontend of the application.

Baserow is an open-source no-code database and the Airtable alternative. It can be used to build an online database without technical experience and can be easily used as the backend but creating a custom UI can be challenging. That's where ToolJet comes into action, it is a free and open-source low-code platform that allows you to quickly build applications and has more than 30+ datasources including Baserow that will let you seamlessly perform operations on your database. You can create a free account on ToolJet Cloud or run it on your local machine.

In this tutorial, we will build a Ticket Triaging application. This is going to be a simple and easy tutorial, you can use refer to this tutorial to build dashboards, CRUD apps, and internal tools in minutes using the Baserow and ToolJet combination.

With the Ticket Triaging app, you can:

List the issues, check their status and assignees
Update the status of the issue/bug

💡 You can also add more functionality to the application by adding a form in the application to add an issue or a button to delete an issue, making it a CRUD app.

You can try out the application here: https://apps.tooljet.com/ticket-triaging-app

Don't want to read the tutorial and want to jump straight into using the app?

Import the exported application JSON file to your ToolJet account. Here's the file.
Once imported the application, you'll need to provide the API Token and choose the host Baserow Cloud or Baserow Self-Hosted
You can get the API key from the Personal API tokens in Settings of the Baserow
Here's the Software Application Bug Tracker template that I used as the database to build this application.

Without further ado, let's start building the application.

Building the UI of the application

Let's start with building the UI first. Login to ToolJet and then on the Dashboard, click on the Create new application button to create a new app. Once the app is created, you will be redirected to the visual app builder. You can change the name of the app by editing the default name i.e Untitled app from the top left of the app builder.

The visual app builder has 4 sections:

Canvas is at the center, where you'll drag and drop widgets to build UI.
Query Manager at the bottom, where you can create and manage queries.
On the right sidebar, you'll find the Components Drawer which has a list of built-in components. You can drag and drop to start building out the user interface.
On the left sidebar, you'll see the Inspector, debugger, comments, settings, and Datasource manager. The datasource manager is used to add a new datasource or manage the connected datasources.

Let's build the UI

For building the user interface, you’ll need to drag and drop the following components from the Components Drawer(right sidebar) and place them accordingly.

Here is the configuration of widgets that I used for building the user interface:

A Container as header and Text widget inside the container to give a title to the app i.e Ticket Triaging System
For the Header text, you can use HTML tags for the formatting of the text along with the other Styles options available in the component editor
Now drag a Tabs component right below the header, and set the value of the following tabs:

{{[ 
        { title: "'Issues 📋', id: '0' }, "
        { title: "'Review and Triage 🔍', id: '1' }, "
 ]}}

Drag containers inside the Issues and Review & Triage tabs individually and set the background color for both containers
On the Issues tab, add a chart component on the right side. Edit Chart's properties and set the chart type as pie and chart data to {{queries.issues.data.chartData}} - it might throw an error in the debugger as we haven't created any queries yet.
On the left side of the Issues tab, add a table component and set the table data value to {{queries.issues.data.results}} - the data will show up when we build the queries later in this tutorial.
let's add the following columns to the table
- Name: Id , key: id
- Name: Bug , key: Bug
- Name: Status , key: Status.value , Text color: {{cellValue === "Assigned" ? "green" : cellValue === "Closed" ? "red" : "orange"}}
- Name: Severity level , key: Severity level[0].value , Text color: {{cellValue === "Level 3 - critical" ? "red" : cellValue === "Level 2 - major" ? "orange" : "green" }}
- Name: Date reported , key: Date reported
- Name: Assigned to , key: Assigned to[0].value

Let's also add an action button on the table so that the users can click on the action button to open the details about the selected issue. We will add an event handler to the action button to control the tab component and change the current tab to Review & Triage(id 1).
Enable the highlight selected row option in the table.

Let's switch to Review and Triage tab and add components. In this tab we will need to create 5 sections:

Issue Details
Assignee Details
Code Files
File Info
Triage Issue

Let's start with the Issue details section first:

Add a container and resize it as shown in the screenshot.
Add a text widget on the top of the container and set its value to <h1> 📝 Issue detail </h1> and then drop a divider below it. You can also change the text color from the Styles tab.
Next, we will add the text widgets inside this container to display the issue details:
- Issue (bold it from the styles) and then add another text widget to the right of the issue and set its value to {{components.table1.selectedRow.Bug}}.Here we are picking up the data from the database with reference to the selected row in the table.
- Now add other text widgets accordingly; Date Reported - {{components.table1.selectedRow['Date reported']}}
- Status - {{components.table1.selectedRow.Status.value}}
- Severity level - {{components.table1.selectedRow['Severity level'][0].value}}
- Current behavior - {{components.table1.selectedRow['Current behavior']}}
- Expected behavior - {{components.table1.selectedRow['Expected behavior']}}

Did you notice there is a text Please select an issue from the issues tab - it is only visible when the user doesn't click on any issue on the table. You can set a condition on the Visibility property of this text widget to only show when a row is clicked. Set the value to {{_.isEmpty(components.table1.selectedRow)}}

💡 ToolJet allows you to internally utilize the Moment, Lodash, and Axios libraries.

Now, the Issue details section is done. Similarly, we will build other sections. Let's build the Assignee Details section:

We will be quickly creating this section by simply copy-pasting the Issue Details section. Select the Issue details container and press c*trl/cmnd+c* and then press ctrl/cmnd+v to duplicate the whole section. Now place it next to the Issue details section and make the required changes.

💡 Check out all the shortcuts supported in ToolJet here.

At the top of this section add an image component and set the value to {{queries.team.data.results.filter(x => x.id === components.table1.selectedRow['Assigned to'][0].id)[0].Photo[0].url}}
Let's add the text components with the following values:
- Name
- Role
- Skills
- Authored code
- Code files worked on
Add text components next to the components in the previous step and set the value to:
- {{queries.team.data.results.filter(x => x.id === components.table1.selectedRow['Assigned to'][0].id)[0].Name}}
- {{queries.team.data.results.filter(x => x.id === components.table1.selectedRow['Assigned to'][0].id)[0].Role.value}}
- {{queries.team.data.results.filter(x => x.id === components.table1.selectedRow['Assigned to'][0].id)[0]['Programming languages'].map(i => i.value)}}
- {{queries.team.data.results.filter(x => x.id === components.table1.selectedRow['Assigned to'][0].id)[0]['Authored code'].map(i => i.value)}}
- {{queries.team.data.results.filter(x => x.id === components.table1.selectedRow['Assigned to'][0].id)[0]['Code file(s) worked on'].map(i => i.value)}}

The Assignee details section is finished, let's move on to the next section which is Code files:

Let's copy-paste the assignee details section and place it next to each other. Rename the header in this section to Code Files and delete all the text widgets. Just keep the divider and the title.
Add a table inside the container as shown in the screenshot.
Set the table data value as {{components.table1.selectedRow['Related code file(s)']}} and add two new columns id and Name. Also, enable the highlight the selected row option

Now the Code Files section is done, let's create another section for displaying the details of the selected file in the table of the Code Files section.

Copy and paste the Assignee Details section as we have done previously in this tutorial. Edit the Title in the header as File Info
Edit the values of the text components inside the container accordingly. In this section we need the following details:
- Current use: {{queries.files.data.results.filter(x => x.id === components.table2.selectedRow.id)[0]['Current use'].value}}
- Language: {{queries.files.data.results.filter(x => x.id === components.table2.selectedRow.id)[0]['Programming language'].map(i => i.value)}}
- Creator: {{queries.files.data.results.filter(x => x.id === components.table2.selectedRow.id)[0]['Original creator'].map(i => i.value)}}
- Function: {{queries.files.data.results.filter(x => x.id === components.table2.selectedRow.id)[0]['Function']}}
- Path: {{queries.files.data.results.filter(x => x.id === components.table2.selectedRow.id)[0]['Path to source']}}

Finally, let's build the last section i.e Triage Issue. We can simply use the body of other sections by copy-pasting.

For this section, we need only the Title and divider components. Rename the Title to Triage Issue
Drag a dropdown and a button widget inside the container.
Edit the properties of the dropdown component, set the label as Triage , default value as {{243298}} , Option values as {{[243296, 243297, 243298]}} , and Option labels as {{["Assigned", "In progress", "Closed"]}} . The numbers mentioned in the default value and option values are actually the ids of the single select options in the baserow.
Add a button and name it as Update, set its loading state to {{queries.triage.isLoading}} so that it shows the loader whenever the triage query is running. We will add an event handler to the button to trigger the triage query later in this tutorial once we build the queries. You can also change the color of the button from the styles tab.

Great!!🥳 we are finally done with building the UI of the application. Now let's go ahead and quickly build the queries to make our app fully functional.

Connecting Baserow and creating queries

Let's start by connecting Baserow to ToolJet.

Go to the Datasource manager on the left sidebar and click on the + button to add a datasource.
Select Baserow from the modal that pops up.
Enter the Baserow API key and select whether it's Baserow Cloud or Self-Host.
Let's go to the Baserow dashboard and get the API key. You can create a new API key from the Personal API tokens in Settings of the Baserow
Copy-paste the API key on ToolJet, and click on the Save button to save the datasource.

Let's create the queries and connect them to components. For this application, we need to create 4 queries:

issues
team
files
triage

issues

This query will get the list of all the rows from the issues table in the baserow. We will also write some JS code in transformation to transform our returned data from the query.

Go to the Query Panel, and click on the + button
Create a new Baserow query and select the List rows operation from the operations dropdown and in the Table ID field enter the ID of the table in database.
To get the Table ID, you can go to the Baserow dashboard, click on the three dots next to the database name and select the View API docs option. It will open up the documentation specific to that particular database that will have IDs and all the related information for that database.
After entering the Table ID, enable the Transformations and write the JS code to add a new object in the returned data. The following JS code will add a new array chartData to the returned data from the query. The chartData will include objects that will have the total count for Assigned, In progress, and closed issues in the database.

const chartData = [{x: "Assigned", y: 0}, {x: "In progress", y: 0}, {x: "Closed", y: 0}]

data.results.forEach((item) => {
  item.Status.value === 'Assigned' ? chartData[0].y++ : item.Status.value === 'In progress' ? chartData[1].y++ : chartData[2].y++
})

return {...data, chartData: chartData}

Now go to the Advanced tab and enable the Run query on page load? so that the query runs every time the app is loaded.
Click on Save & Run
Now, you'll be able to see the updated data on the table and on the chart.

team

This query will list all the rows from the team table in the database.

Create a new Baserow query, select List rows from the operations dropdown, and enter the Table ID
Go to the Advanced tab and enable the Run query on page load? so that the query runs every time the app is loaded.
Click on Save & Run

files

This query will list all the rows from the files table in the database.

Create a new Baserow query, select List rows from the operations dropdown, and enter the Table ID
Go to the Advanced tab and enable the Run query on page load? so that the query runs every time the app is loaded.
Click on Save & Run

triage

This query will update the status of an issue in the issues table.

Create a new Baserow query, select the Update row from the operations dropdown, and enter the Table ID
In the Row ID field, we will get the value from the selected row in the table using the dynamic variable {{components.table1.selectedRow.id}}
In the Record field, we will get the value from the dropdown that we have added in the Review & Triage tab:

{ Status: {{components.dropdown1.value}}}

Go to the Advanced tab and add an event handler to run the issues query every time the query is successful, so that whenever the status is updated the table gets reloaded on the ToolJet.
Click on Save & Run
Now go to the Update button on Review & triage tab and add an event handler to run the triage query.

Releasing your application

Click on the Release button at the top right corner of the app builder and the version which is currently opened will be released.

You can click on the Share button and enable the make application public allowing anyone to use the application without signing it. You can also customize the shareable URL of your application.

Voila!! You have successfully created an internal Ticket Triaging Application using almost no code 🎉

If you have any queries related to building applications with ToolJet or just want to hang out in the community of low-code application developers just drop us a Hi in our Slack Community. 🚀

Building a MinIO file explorer app in 30 minutes

Shubhendra Singh Chauhan — Fri, 02 Sep 2022 08:31:14 +0000

In this tutorial, we will build an internal tool for reading, downloading and uploading objects to the buckets in MinIO.

What is MinIO?

Minio is an open-source distributed object storage server designed for Private Cloud infrastructure providing S3 storage functionality. Minio is can be used for storing unstructured data such as photos, videos, log files, backups, and containers.

What you'll need to build the GCS file explorer?

ToolJet (https://github.com/ToolJet/ToolJet): A free and open-source low-code platform that allows you to quickly build applications. You can create a free account on ToolJet Cloud or run it on your local machine.
MinIO: Check out the various editions and their Pricing here. I have used the community edition using docker for this tutorial.

Bonus: Download the exported application and import it to your ToolJet account to use it right away.

Here's a glimpse of the application:

Without further ado, let's start building the application.

Building the UI of the explorer

Let's start with building the UI for Explorer. Login to ToolJet and then on the Dashboard, click on the Create new application button to create a new app. Once the app is created, you will be redirected to the visual app editor. You can change the name of the app by editing the default name i.e Untitled app from the top left of the app builder.

The visual app editor has 4 sections:

Canvas is at the center, where you'll drag and drop widgets to build UI.
Query editor at the bottom, where you can create queries.
On the right sidebar, you'll find Widget Manager which has a list of built-in widgets and components. You can drag and drop to start building out the user interface.
On the left sidebar, you'll see the Inspector, debugger, comments, settings, and Datasource manager. The datasource manager is used to add a new datasource or manage the connected datasource.

Let's build the UI

For building the user interface, you’ll need to drag and drop the following components from the Widget Manager(right sidebar) and place them accordingly.

Here is the configuration of widgets that I used for building the UI:

A Container as header and Text widget inside the container to give a title to the app i.e MinIO File Explorer

Another container below the header will contain all the sections of the application. Give this container a background color of your choice from the Styles.

Let's add a text widget in the center and set the text to Please select a bucket - we will display this text by conditionally disabling the visibility of the container that we will add on top of this text widget

Let's drag a container on top of the text widget, and put list view widget inside the container. You can also use text widgets to set columns names- Name, Last updated, Size, and Actions.

In the List view widget, use text widget for name, updated on, and size. Use Image widget for the Action. User will click on the download image to download the object from the MinIO.

Set the following field value for the three text widgets Name, Updated on, and Size as {{listItem.name}}, {{moment(listItem.lastModified).format("DD/MM/YYYY h:mm:ss a")}}, and {{(listItem.size/1024).toFixed(2)}} kb respectively. In the image widget set the loading state value to {{listItem.id === variables.currentlyLoadingId}} , and add the event handler to open the webpage {{queries.urlfordownload.data.url}}

Drag a button on the right side of the container that will be used to trigger the dialog for choosing the bucket.

Drag a modal somewhere below the container, go to the button properties and set the event handler to show the modal that you have added.

Click on the button to show the modal, and then you can drag and drop dropdown and button widget inside the modal to build the modal UI. The dropdown will include the list of buckets that we will retrieve from the query, and the button will have an event handler to fire the query for listing the files from the selected bucket.

Let's add text widget next to button for showing up the currently selected bucket. Set the text value as {{components.dropdown1.value}}

Now, the last section of the UI - the upload section. We will build this section inside a container. We will be using text, divider, text input, file picker and button widget to build the section. The button widget will include an event handler for firing up the upload query that we will create later.

💡 Check the documentation to learn more about customizing the widgets and beautifying the UI. ✨

Now, we are done building the UI - let's move forward with connecting the MinIO and build the queries.

Connecting the MinIO datasource and creating queries

Before creating the queries let's connect the MinIO datasource first:

Go to the datasource manager on the left sidebar and click on + button to add a new datasource.
Search and click on MinIO.
Enter the Host, Port, Username and Password.
You can click on Test connection button to check the connection and then click on Save button to save the datasource.
Now, we are good to go for creating queries.

*For this application, we will need to build the queries for:
*

JavaScript code for triggering show modal action
Listing the buckets from MinIO
Listing the object/files from the selected bucket
Uploading the file to the selected bucket
Creating signed URL for downloading the object from the bucket

JavaScript code for triggering show modal action

Go to the Query Panel, and click on the + button
Create a new JavaScript code query and enter the following code for triggering the show modal action:

actions.showModal('modal1')

Go to the Advanced tab, toggle on the Run query on page load? option, and save the query as showModalOnPageLoad. Enabling the Run query on page load option will display the modal every time the app is loaded - giving users the flexibility to choose the bucket before moving to other app functions.

Listing the buckets from MinIO

Go to the Query Panel, and click on the + button to create a new query
Select MinIO as the datasource
In the operations dropdown, select the List buckets option

Go to the Advanced tab, toggle on the Run query on page load? option, and click Save and Run button.
Let's open up the modal by clicking on the change button, and then edit the properties of the dropdown inside the modal. Set the Option Values and Option Label as {{queries.listBuckets.data.map(row => row.name)}} , the Default value to {{queries.listBuckets.data.map(row => row.name)[0]}}, and Options loading state to {{queries.listBuckets.isLoading}}

Listing the object/files from the selected bucket

Go to the Query Panel, and click on the + button to create a new query
Select MinIO as the datasource
In the operations dropdown, select the List objects in a bucket option
In the Bucket field, use JS to dynamically get the value from the selected option in the dropdown widget - {{components.dropdown1.value}}

Now, go to the Advanced tab and add an event handler to close the modal once the query is successful.
Save and run the query as listFiles.

Let's open up the modal by clicking on the change button, and then edit the properties of the button that is inside the modal. Add an event handler for running the listFiles query for On Click event.

Go to the list view widget and connect the data returned by the query. Set list data field value to {{queries.listFiles.data.files}}

Uploading the file to the selected bucket

Go to the Query Panel, and click on the + button to create a new query
Select MinIO as the datasource
In the operations dropdown, select the Put object option
In the Bucket field enter {{components.dropdown1.value}} , File name field enter {{components.textinput1.value}} , Content type field enter {{components.filepicker1.file[0].type}} , and Upload data field enter {{components.filepicker1.file[0].content}}

Go to the Advanced tab and add the two event handlers, one to run the listFiles query for the On Success event. This will reload the list of files in the listview widget.

And the other event handler to set component specific action for file picker that will clear the file from the widget once the query is successful.

Save and run the query as upload.

Creating signed URL for downloading the file from the bucket

Go to the Query Panel, and click on the + button to create a new query
Select MinIO as the datasource
In the operations dropdown, select the Presigned URL for download option
In the Bucket field enter {{components.dropdown1.value}}, and in File name field enter {{queries.listFiles.data.Body[components.listview2.selectedRowId].name}}

Go to the Advanced tab and set an event handler to unset a variable - currentlyLoadingId

Save the query as urlfordownload
Go to the list view widget and click on its handle to edit its properties.
Add the two event handlers- one to set a variable currentlyLoadingId for On row click event and set its value to {{queries.listFiles.data.Body[components.listview2.selectedRowId].name}}
And the other handler to run the urlfordownload query.
Now you can click on one of the row on the list view widget and it will fire up the urlfordownload query.

Awesome! We have successfully built all the queries and connected with the UI.

And now FINALLY just Release your application from the top right corner of the app editor.

You have successfully created a File explorer app for your MinIO object storage 🎉

If you have any queries related to building applications with ToolJet or just want to hang out in the community of low-code application developers just drop us a Hi in our Slack Community. 🚀

Building a Google Cloud Storage (GCS) file explorer app in 30 minutes

Shubhendra Singh Chauhan — Mon, 22 Aug 2022 12:44:37 +0000

In this tutorial, we will build an internal tool for reading, downloading and uploading files to the buckets in Google Cloud Storage.

What is Google Cloud Storage?

Google Cloud Storage (GCS) is the global, secure, and scalable object storage for immutable data such as images, texts, videos, or any other file format. Objects are stored in buckets that are associated with a project, which are grouped under an organization.

What you'll need to build the GCS file explorer?

ToolJet (https://github.com/ToolJet/ToolJet): A free and open-source low-code platform that allows you to quickly build applications. You can create a free account on ToolJet Cloud or run it on your local machine.
Google Cloud Storage (GCS): JSON private key for the service account required for connecting ToolJet to GCS

Bonus: Download the exported application from here and import it to your ToolJet account to use it straight away.

Here's a glimpse of the application:

Without further ado, let's start building the application.

Building the UI of GCS file explorer

Let's start with building the UI for Explorer. Login to the ToolJet and then on the Dashboard, click on the Create new application button to create a new app. Once the app is created, you will be redirected to the visual app editor. You can change the name of the app by editing the default name i.e Untitled app from the top left of the app builder.

Visual app editor has 4 sections:

Canvas at the center, where you'll drag and drop widgets to build UI.
Query editor at the bottom, where you can create queries.
On the right sidebar, you'll find Widget Manager that has a list of built-in widgets and components. You can drag and drop to start building out user interface.
On the left sidebar, you'll see the Inspector, debugger, comments, settings, and Datasource manager. Datasource manager is used to add a new datasource or manage the connected datasource.

Let's build the UI

For building the user interface, you’ll need to drag and drop the following components from the Widget Manager(right sidebar) and place them accordingly.

Here is the configuration of widgets that I used for building the UI:

A Container as header and Text widget inside the container to give a title to the app i.e GCS File Explorer

Another container below the header that will contain all the sections of the application. Give this container a background color of your choice from the Styles.

Let's add a text widget in the center and set the text to Please select a bucket - we will display this text by conditionally disabling the visibility of the container that we will add on top of this text widget.

Let's drag a container on top of the text widget, and put list view widget inside the container. You can also use text widgets to set columns names- Name, Updated on, Size, and Actions.

In the List view widget, use text widget for name, updated on, and size. Use Image widget for the Action. User will click on the download image to download the object from the GCS.

Set the following field value for the three text widgets Name, Updated on, and Size as {{listItem.name}}, {{moment(listItem).format("DD/MM/YYYY h:mm:ss a")}}, and {{(listItem.size/1024).toFixed(2)}} kb respectively. In the image widget set the loading state value to {{listItem.id === variables.currentlyLoadingId}} , and add the event handler to open the webpage {{queries.urlfordownload.data.url}}

Drag a button on the right side of the container that will be used to trigger the dialog for choosing the bucket.

Drag a modal somewhere below the container, go to the button properties and set the event handler to show the modal that you have added.

Click on the button to show the modal, and then you can drag and drop dropdown and button widget inside the modal to build the modal UI. The dropdown will include the list of buckets that we will retrieve from the query, and the button will have an event handler to fire the query for listing the files from the selected bucket.

Let's add text widget next to button for showing up the currently selected bucket. Set the text value as {{components.dropdown1.value}}

Now, the last section of the UI - the upload section. We will build this section inside a container. We will be using text, divider, text input, file picker and button widget to build the section. The button widget will include an event handler for firing up the upload query that we will create later.

💡 Check the documentation to learn more about customising the widgets and beautifying the UI. ✨

Now, we are done building the UI - let's move forward with connecting the Google Cloud Storage and build the queries.

Connecting the GCS datasource and creating queries

Before creating the queries let's connect the GCS datasource first:

Go to the datasource manager on the left sidebar and click on + button to add a new datasource.
Search and click on Google Cloud Storage.
Enter and save the JSON private key for the service account
You can click on Test connection button to check the connection and then click on Save button to save the datasource.
Now, we are good to go for creating queries.

For this application, we will need to build the queries for:

JavaScript code for triggering show modal action
Listing the buckets from GCS
Listing the object/files from the selected bucket
Uploading the file to the selected bucket
Creating signed URL for downloading the file from the bucket

JavaScript code for triggering show modal action

Go to the Query Panel, and click on the + button Create a new JavaScript code query and enter the following code for triggering the show modal action:

actions.showModal('modal1')

Go to the Advanced tab, toggle on the Run query on page load? option, and save the query as showModalOnPageLoad. Enabling Run query on page load option will display the modal every time the app is loaded - giving users the flexibility to choose the bucket before moving to other app functions.

Listing the buckets from GCS

Go to the Query Panel, and click on the + button to create a new query
Select Google Cloud Storage as the datasource
In the operations dropdown, select the List buckets option

Go to the Advanced tab, toggle on the Run query on page load? option, and click Save and Run button.
Let's open up the modal by clicking on the change button, and then edit the properties of the dropdown inside the modal. Set the Option Values and Option Label as {{queries.listBuckets.data.buckets}} , Default value to {{queries.listBuckets.data.buckets[0]}}, and Options loading state to {{queries.listBuckets.isLoading}}

Listing the object/files from the selected bucket

Go to the Query Panel, and click on the + button to create a new query
Select Google Cloud Storage as the datasource
In the operations dropdown, select the List files in a bucket option
In Bucket field, use JS to dynamically get the value from the selected option in the dropdown widget - {{components.dropdown1.value}}

Now, go to Advanced tab and add an event handler to close the modal once the query is successful.
Save and run the query as listFiles

Let's open up the modal by clicking on the change button, and then edit the properties of the button that is inside the modal. Add an event handler for running the listFiles query for On Click event.

Go to the list view widget and connect the data returned by the query. Set list data field value to {{queries.listFiles.data.files}}

Uploading the file to the selected bucket

Go to the Query Panel, and click on the + button to create a new query
Select Google Cloud Storage as the datasource
In the operations dropdown, select the Upload file option
In the Bucket field enter {{components.dropdown1.value}} , File name field enter {{components.textinput1.value}} , Content type field enter {{components.filepicker1.file[0].type}} , Upload data field enter {{components.filepicker1.file[0].content}} , and set Encoding to utf8

Go to Advanced tab and add the two event handlers, one to run the listFiles query for On Success event. This will reload the list of files in the listview widget.

And the other event handler to set component specific action for file picker that will clear the file from the widget once the query is successful.

Save and run the query as upload.

Creating signed URL for downloading the file from the bucket

Go to the Query Panel, and click on the + button to create a new query
Select Google Cloud Storage as the datasource
In the operations dropdown, select the Signed URL for download option
In the Bucket field enter {{components.dropdown1.value}}, and in File name field enter {{queries.listFiles.data.files[components.listview2.selectedRowId].name}}

Go to the Advanced tab and set an event handler to unset a variable - currentlyLoadingId

Save the query as urlfordownload
Go to the list view widget and click on its handle to edit its properties.
Add the two event handlers- one to set a variable currentlyLoadingId for On row click event and set its value to {{queries.listFiles.data.files[components.listview2.selectedRowId].id}}
And the other handler to run the urlfordownload query.
Now you can click one on row on the list view widget and it will fire up the urlfordownload query.

Awesome! We have successfully built all the queries and connected with the UI.

And now FINALLY just Release your application from the top right corner of the app editor.

You have successfully created a File explorer app for your Google Cloud Storage. 🎉

If you have any queries related to building applications with ToolJet or just want to hangout in the community of low-code application developers the just drop us a Hi in our Slack Community. 🚀

Build HR Management System using MongoDB and ToolJet (Part 4: Leave Requests)

Shubhendra Singh Chauhan — Mon, 11 Jul 2022 11:34:17 +0000

Request Leaves

This is the last application and this application will act as a dashboard for all the employees for requesting the leaves and getting the stats on their past leaves.

To build this application, clone the last application that we built, edit the cloned app and remove all the widgets except the header and sidebar. Remove all the queries, rename the application from Employees and Requests to Request Leaves , and Disable the Request leaves button on the sidebar and enable others.

Building the UI

Rename the header text as <h1>🏖️Leave Scheduler</h1>
Drag another Text widget and set the text value to Your recent leave requests:
Place a ListView widget, a button, a modal, and two statistics widgets as shown in the image below

Edit the ListView widget, and set the List Data field value to {{queries.listRequests.data[0].leaves}}
In list view widget, place the text widgets and set the following values:
Leave requested for date: {{moment(listItem.start_date).format("DD-MM-YYYY")}}
No. of Days: {{listItem.leave_days}}
Status: {{listItem.leave_days}}
You can style the text color of Status(approved, rejected, requested) text programmatically. Go to the Styles tab and click on the Fx button next to Text Color and set this value: {{listItem.status === "approved" ? "green" : listItem.status === "rejected" ? "red" : "blue"}}

Click on the button widget to edit its properties, go to the handlers and add an event handler to show the modal that we added in the previous step.
After adding the event handler, click on the button to show the modal. When the modal shows up, you can drag on drop widgets on it to build a form
For labels use text widgets, datepicker widget for getting input for the start date, and a number input widget for the Number of days field.
Edit date picker properties, set default date to {{moment().format("YYYY-MM-DD")}} and date format to YYYY-MM-DD
For the Number input widget, you can set the maximum and minimum value according to your preference.
Add a button for submitting the leave request, it will have two event handlers - one for closing the modal and the other for running the query that will add a request to the database. For now, just add one handler for closing the modal, we will add another once we create the queries.

On the first Statistics widget, set the Primary Value Label as Leaves Approved and Primary value to {{queries.approvedCount.data.count}} , and toggle on the Hide secondary value as this is not required.
On the second Statistics widget, set the Primary Value Label as Allocated leaves , we will hardcode the Primary value to 24 , set Secondary Value Label as Leave Balance and Secondary Value as {{24- queries.approvedCount.data.count}}

Build the queries

listRequests

This query will return all those documents that match with the email of the currently logged-in user in ToolJet.

Create a new MongoDB query, select Find Many Operation and enter the collection name employees
In the Filter field, enter {email: "{{globals.currentUser.email}}"} - here we are getting the email of the logged-in user through the exposed variable.
Go to the Advanced tab, and enable the Run query on page load? option.
Hit Save and Run button to save and execute the query - You'll see the updated data on the listview widget.

leaveRequest

This query will update a document and add a new object for leave request in the database.

Create a new MongoDB query, select Update One Operation and enter the collection name employees
In the Filter field enter {"email": "{{globals.currentUser.email}}"}
In the Update field enter {$push:{"leaves":{"start_date": "{{moment(components.datepicker1.value).toISOString(true)}}", "leave_days":{{components.numberinput1.value}}, "status": "requested"}}}
Hit Save button to save the query. Don't hit Run because it will create an empty object in the database since there is no value in the form yet.
Now, click on the button to show the modal, select the submit button, and the remaining event handler for running this query.

approvedCount

This query returns the total count of the documents that have status field as approved.

Create a new MongoDB query, select Count Operation and enter the collection name employees
In the Filter field enter {"leaves.status": "approved"}
Go to the Advanced tab, and enable the Run query on page load? option.
Hit Save and Run button to save and execute the query - You'll see the value on the statistics widget gets updated.

Finally, You can now release the last application by clicking the Release button on the top right of the app editor.

Connecting the applications on the sidebar of each application

Now that we have all 4 applications released - All you need to do is to make the applications Public by clicking on the share button on the navbar of App-editor. After making the application public, create a custom shareable URL for all 4 applications.

For connecting all 4 applications, you'll need to edit the buttons in the sidebar of each application and use the Go to App action for On Click event handler.

Note: For making this change you'll need to create a new version from the released version first and then release it again for this change to be included.

Voila!! 🎉 You have successfully built an HR Management system that includes a suite of 4 different applications 🚀

If you have any queries related to building applications with ToolJet or just want to hang out in the community of low-code applications builders just drop us a Hi in our Slack Community. 🚀

Build HR Management System using MongoDB and ToolJet (Part 3: Employees and Requests)

Shubhendra Singh Chauhan — Mon, 11 Jul 2022 11:33:55 +0000

Employees and Requests

Clone the last app that we built and remove all the widgets from the main section and remove all the queries that are there. Don't forget to rename the application from Company Calendar to Employees and Requests from the top left of the app editor.

Assuming that you connected the MongoDB datasource, we can now start building the UI of the main section and then add the queries.

Build the user interface

In the last two apps, we disabled the respective button on the sidebar, similarly, in this app, we will disable the Employees and Requests button.

Drag a Tabs widget into the Canvas, and edit its properties. Set the Tabs field value to:

{{[{ title: 'Employees', id: '0' },{ title: 'Leave Requests', id: '1' }]}}

Set the Default tab to 0
We will be placing the widgets inside the Employees and Leave Requests tabs.

Employees Tab

First, drag a modal widget somewhere below the tabs widget
Place a button inside the Employees tab, edit the button's property and add an event handler to show a modal, and choose the modal that you added on the canvas (modal1)
Click on the button to show the modal, now you can drag the widgets inside the modal to create a form-like UI. I have used text widgets for labels, text input widgets for the input fields, and then a button widget Add . The button widget will have two event handlers: one for closing the modal1 and the other that will trigger the query for adding the employee details into the database.

Now, add a table widget inside the Employees tab. Set the Table data field value to {{queries.allEmployees.data}} - we haven't created the queries yet. The table will be populated once we create the queries.
Go to the Add Columns, add 4 columns First Name, Last Name, Email, Phone, and set their keys as first_name, last_name, email, and phone respectively.

Now, switch the tab to Leave requests. Place a second modal somewhere below the tabs widget, drag a listView widget inside the tab, and set the List data field value to {{queries.leaveRequestsList.data}} . Add an event handler on the ListView widget - select Event as Row Clicked, Action as Show Modal, and choose the modal i.e. modal2

Now, click on the row to show the modal. Inside the modal, add two buttons: Approve or Reject. These two buttons will be used to accept or reject the leave request raised by the employees.

We will add the widgets inside the listview once we have created the queries.

Build the queries

addEmployee

This query will be used to insert a document into a collection. With this query, we will add an employee to the database.

Create a new MongoDB query, select the Insert One operation, enter the Collection name employees
Enter the Document data as an object: { first_name: "{{components.textinput1.value}}", last_name: "{{components.textinput2.value}}", email: "{{components.textinput4.value}}", phone: "{{components.textinput3.value}}" } - here we are using JS to get the values from the widgets that we have added inside the modal1.
Hit the Save button, not the Save & Run because executing the query now will fail as there are currently no values in the form.
Once the query is saved, you can click on the Add Employee button to show up the form modal and then edit the Add button inside it. Add another event handler to run the addEmployee query on the On Click event.

allEmployees

This query will return all the employees in the database.

Create a new MongoDB query, select Find Many operation, and enter the Collection name as employees
Go to the Advanced tab and enable the Run query on page load
Click Save & Run button on the query manager and as soon as the query runs you'll see the table gets populated with the employees' data.

leaveRequestsList

This query will return the list of those documents that have leaves status set as requested and will display it on the listview widget.

Create a MongoDB query, select Find Many operation, enter the Collection name as employees
In the Filter field, set {"leaves.status": "requested"}
Click on Save & Run the button to save and fire up the query.

Now, that we have our data, we can add widgets to the list view widget. I have used the text widgets. For Labels use text widgets with HTML heading/bold tag for formatting. For displaying the query data use:

First Name: {{listItem.first_name}}

Last Name: {{listItem.last_name}}

Phone: {{listItem.phone}}

Email: {{listItem.email}}

Leave Requested from:

{{listItem.leaves.reduce((acc,leave) => {
if (leave.status === "requested"){
acc = moment(leave.start_date).format("DD-MM-YYYY");
}
return acc;
},"")}}

No. of Days:

{{litItem.leaves.reduce((acc,leave) => {
if (leave.status === "requested"){
acc = leave.leave_days
}
return acc;
},0)}}

You can now successfully release this version of the application by clicking the Release button on the top right of the app editor.

Forem: Shubhendra Singh Chauhan

Supercharge Your Web Automation with BrowserQL: Logging in and Scraping Private Data Seamlessly

🚀 Why Browserless and BrowserQL?

Prerequisites

The BrowserQL Query: Logging In and Scraping

How BrowserQL Mutations Work

Setting Up the Project

1. Initialize a new project and install dependencies

2. Create a .env file for your API key

NodeJS Code:

Your final code will look like this:

Expected Output

Why BrowserQL Shines Here

Final Thoughts

Try BrowserQL Yourself

Managing Multiple Git Profiles with Ease: Introducing `gguser`

Meet gguser

🚀 Installing gguser

🎯 How to Use gguser

1️⃣ Adding Git Profiles

2️⃣ Switching Between Git Profiles

Interactive Selection

Quick Switching

3️⃣ Checking Your Current Git User

4️⃣ Managing Your Profiles

Listing All Profiles

Removing a Profile

🔗 Linking a Profile to a Directory

🔥 Why gguser is a Game-Changer

💡 Contribute or Extend gguser

🎯 Final Thoughts

Kubernetes for Beginners: Introduction, Functionality, and Cost

Table Of Contents

Introduction

Functionality

Cost

Cost associated with k8 clusters

Optimizing cost

Threads or Twitter?

Threads vs. Twitter: What are the Differences?

Threads

Twitter

Which Platform is Right for You?

What do you think? Which platform would you prefer?

Building an efficient email marketing application with ChatGPT, ToolJet, and PostgreSQL

What is ToolJet?

Setting up an ElephantSQL (PostgreSQL) database

Building the application with ToolJet

The Home page

The View newsletter page

The Create newsletter page

The Subscribers manager page

How to communicate with a PostgreSQL database in ToolJet

Connecting the PostgreSQL database to ToolJet

Setting up the database structure

Querying the PostgreSQL database in ToolJet

Getting all available subscribers

Adding a new subscriber to the list

Getting newsletters saved as drafts

Getting published newsletters

Saving newsletters as draft

Saving published newsletters

Getting the details of a published newsletter

How to communicate with ChatGPT in ToolJet

Setting up an OpenAI account

Communicating with the OpenAI API in ToolJet

How to send emails via the Sendinblue API in ToolJet

Conclusion

Building an intelligent CRM using ChatGPT, PostgreSQL, and ToolJet

Introduction

What is ToolJet?

Setting up an ElephantSQL (PostgreSQL) database

Building the application with ToolJet

The “Add New Lead” Modal

The “Send Email” modal

How to communicate with a PostgreSQL database in ToolJet

Connecting a PostgreSQL database to ToolJet

Querying a PostgreSQL database in ToolJet

Adding new users to the database

How to communicate with ChatGPT in ToolJet

2. Create a `.env` file for your API key

Meet `gguser`

🚀 Installing `gguser`

🎯 How to Use `gguser`

🔥 Why `gguser` is a Game-Changer

💡 Contribute or Extend `gguser`