tutorials
Back to overview

How to Build a Custom CRM System with Baserow and ToolJet

You can’t do business without customers. If you aren’t growing, you aren’t succeeding. To drive more repeat and referral customers, it helps to figure out what your customers need. A CRM (Customer Relationship Management) system is like a digital database of customers and prospects. It is crucial to every company’s marketing, sales, support, and growth.

In this tutorial, we will learn how to build a custom CRM system that will help businesses become more profitable. This system will manage the entire process from lead generation to sales optimization.

Don’t want to read this tutorial, watch this video instead:

Let’s get started with building a CRM from scratch.

A. What we will build

We will use Baserow as the backend database to create a functional sales CRM database and ToolJet as the front end of the application with basic CRM functionality.

At the end of this tutorial, we want to have:

  • Analytics dashboard in ToolJet showing the contacts, customers and lifecycle stages of leads
  • Contacts: Details of potential customers who have expressed interest in doing business with you
  • Customers: Details about who you are currently doing business with or have previously done business with. Customers are contacts whom we have won the deal with.

What you’ll need

Baserow is an open-source no-code database and an Airtable alternative. It is simple to use as a backend and can be used to build an online database without the need for technical expertise.

ToolJet is an open-source low-code platform that allows you to quickly build applications and perform operations in your Baserow database. You can connect ToolJet with more than 30 different data sources, including Baserow.

Let’s begin by storing data in Baserow.

B. Build the database in Baserow

After logging in to Baserow, you’ll see the Dashboard page, which gives you a snapshot of all your groups and databases.

A group is a workspace where you can store multiple databases and invite collaborators. A database is a collection of tables, each containing rows and columns. A Baserow database can include multiple tables containing information for different projects.

First, we’ll create a group called 'ToolJet CRM’. On the Dashboard page where you can see an overview of all your groups and databases, click the ‘+ Create group’ button. This button will also be visible in the sidebar:

enter image description here

Next, we are going to create a CRM database in the ToolJet CRM group. Click ‘+ Create new’ then select a ‘Database’ from the dropdown option. Input a name for the new database and click the ‘Add database’ button:

enter image description here

Within the CRM database, we want to create the following tables:

  • Plans
  • Contacts
  • Customers

enter image description here

Add fields to the table

A table consists of rows and fields, also known as columns. In a table, a field is a collection of values of the same data type. The rows and columns are visible in the grid view.

To learn more about working with the various field types, read our documentation.

To create a new field, click on the + button just after the last visible field at the end of the table. Next, select the field type and input a name to identify the field, then click ‘Create’:

enter image description here

Tip 💡: You can easily import your data from almost anywhere into Baserow. Want to bring your existing content into Baserow database? Find out how to import data into a database or table in this support article.

Table 1: Plans

If the pricing for the subscription plans changes, we don’t want to adjust the details in every table that refers to pricing; we just have to make the change in this table.

In the Plans table, create the following fields:

  • Name - Text field
  • Price - A Number field indicating the price of the product/plan.

For this tutorial, we will pre-fill the table with the following values:

enter image description here

Table 2: Contacts

In the Contacts table, we want to store the information of our leads in the following fields:

  • Name - Text field
  • Email address - Email field
  • Phone number - Phone number field
  • Lifecycle stage - A Single select field to indicate the contact’s status and track the stages of leads. This shows a dropdown list when you want to enter the cell value. Set four different states to configure the dropdown based on a stage: Interested, Won, Lost, Negotiating
  • Expected deal size - A Number field indicating the expected amount or revenue that you expect to generate when the deal is closed. This will be set to two decimal places to reflect the fact that most currencies have two decimal places.
  • Target subscription plan - A Link to table field to create a relationship to the Plans table. Select the Plans table as the table to link to. Check the ‘Create related field in a linked table’ option to also reflect this field in the corresponding Plans table:

enter image description here

Table 3: Customers

Next, we want to store the details of customers. In the Customers table, we want to add the following fields:

  • Name - Text field
  • Email address - Email field
  • Phone number - Phone number field
  • Lifecycle stage - A Single select field to indicate the contact’s status and track the stages of leads. This shows a dropdown list when you want to enter the cell value. Set four different states to configure the dropdown based on a stage: Interested, Won, Lost, Negotiating
  • Target subscription plan - A Link to table field to create a relationship to the Plans table. Select the Plans table as the table to link to. Check the ‘Create related field in a linked table’ option to also reflect this field in the corresponding Plans table
  • Period - A Single Select field that indicates the subscription frequency of when the customers are paying.

Because the Customers table will be similar to the Contacts table, we will copy the layout and details from the Contacts table using the duplicate functionality.

To duplicate a table, hover over it and click on the three dots  icon next to it. Select the ‘Duplicate’ option from the dropdown menu:

enter image description here

Now, we need to customize the duplicated table to fit this use case.

Rename the duplicated table as Customers to make it easier to differentiate the tables. To rename a table, click the three dots  icon next to it in the sidebar. Then select ‘Rename’ and enter a new name for the table.

Delete the ExpectedDealSize field from the Customers table. Click on the dropdown icon next to the field. From the dialog box next to the field’s name, choose the ‘Delete field’ option then select the ‘Delete field’ button.

Next, add two new Single Select field options to the Period field - Monthly and Yearly:

enter image description here

Invite Team Members

Thanks to collaborative CRM, you can establish communication between many divisions involved in lead generation. CRM that encourages collaboration can be used across departments such as marketing, sales, technical support, clients, vendors, and distributors.

You can invite collaborators to your Baserow CRM group and grant relevant access. It’s easy to collaborate with data in real-time.

To add group collaborators:

  1. Switch to the ToolJet CRM group from the dashboard or by using the sidebar.
  2. Click the vertical ellipses  icon in the right corner next to the group name
  3. Click ‘Members’.
  4. Input the collaborator’s email address and choose either the Admin or Member view permission types:

enter image description here

Now that we have set up the back end, we want to build the front end of the CRM application in ToolJet. Let’s get you up and running!

C. Build an interface in ToolJet

ToolJet provides interactive user interface components for developing application tools based on the users’ needs. ToolJet can connect with your existing Baserow datasource.

Let’s begin creating the user interface. To build a new app, sign in to ToolJet and then select the ‘Create new application’ option on the Dashboard. You can alternatively Import an application or start with a template:

enter image description here

You will be redirected to the visual app builder to design the user interface.

Add Baserow datasource in ToolJet

We need to authenticate with the API token before we can connect the CRM data to ToolJet. Let’s begin with linking the Baserow account to ToolJet to authenticate the API token.

To access the Datasource manager in ToolJet, click on the ‘Sources’ tab in the app builder’s left-sidebar. Then, click the ‘+ add data source’ button to add a datasource:

enter image description here

You will be prompted to select the data source that you want to add. Select Baserow from the modal that pops up:

enter image description here

Provide your database API token and select the Host of your Baserow database.

Get Baserow personal API token

Let’s get the API key from Baserow. You can create a new API key from the Personal API tokens in Settings.

For detailed information on how to create a personal API token, visit our documentation.

enter image description here

Input your Baserow API token in ToolJet and select whether it’s Baserow Cloud (hosted version of Baserow) or Self-Host. For self-hosted option, a base URL is required to connect.

Then, click the Save button to save the datasource:

enter image description here

The fields that are marked as encrypted will be encrypted before saving them to ToolJet’s database.

Get the required parameters

To connect this database with Tooljet, we need access to the database API documentation. Click on the vertical ellipsis beside the selected group to access the API documentation. Then select ‘View API Docs’ from the group menu:

enter image description here

You can browse the database’s automatically generated API documentation. You may view all the newly created fields as well as other endpoints:

enter image description here

We will need these parameters to build a query.

Build Queries

We will now build a query for the Baserow datasource that we connected in the previous step. You can find the ToolJet Query Editor at the bottom of the page.

A query is a request for data or information from a database table or combination of tables.

To create a new query, click on the + icon of the query editor. Select the Baserow datasource created in the previous step:

enter image description here

We want to create the following queries and connect them to the tables on Baserow:

  • contacts - List row operation
  • customers - List row operation
  • plans - List row operation
  • addContact - Create row operation
  • addCustomer - Create row operation

List Rows operation

We need to set up queries to fetch all the contacts, plans and customers. This query will get the list of all the rows from the tables in the Baserow.

First, we will set up a query to list all the rows in the Contacts table.

  • 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 Table ID.

    Find your Baserow table ID by clicking on the three dots next to the table. It’s the number between brackets:

    enter image description here

    You can also find your Baserow table ID by your table name in the API documentation.

  • After entering the Table ID, enable the Transformations and write the JS code to add a new object in the returned data. The LifecycleStage single select and the TargetPlan Link to table fields require the data that ToolJet gets from Baserow to be in a nested format. Enabling the Transformations on queries will transform the query results.

    return data.results.map((row) => ({
    	...row,
    	LifecycleStage: row.LifecycleStage?.value,
    	Plan: row.Plan?.[0]?.value
    }))
    

    The query will now look like this:

    enter image description here

  • 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 Create & Run button to create the query.

Next, we will add another query by following the same steps detailed above to list all the rows in the Customers table. In this case, replace the Transformations with the code:

return data.results.map((row) => ({
	...row,
	Period: row.Period?.value,
	Plan: row.Plan?.[0]?.value
}))

The query will look like this:

enter image description here

Finally, you want to repeat the List rows operations for the Plans tables:

enter image description here

Now let’s connect the queries to the components.

Building the UI of the application

To start building out the user interface, drag and drop some components from the Components Drawer and place them as needed. The Widget manager on the right sidebar displays the list of built-in widgets that can be added to the app.

First, drag the Tabs component right below the header:

enter image description here

Next, set the value of the following tabs:

{{[ 
		{ title: 'Dashboard', id: '0' }, 
		{ title: 'Customers', id: '1' }, 
		{ title: 'Contacts', id: '2' } 
 ]}}

Input these values on the right-hand side of the dashboard to rename the tables:

enter image description here

Customize the Contacts tab

Now we want to connect these queries to the tables we have set up. On the Contacts tab, add a table component. Edit the Table’s properties and set the table data to {{queries.contacts.data}} to call the query:

enter image description here

Customize the Customers tab

Next, we’ll repeat the previous steps in the Customers tab. Add a table and set the table data to {{queries.customers.data}} to pull information from the Customers query.

Customize the Dashboard tab

On the Dashboard tab, add the statistics components to the left, then add a chart component on the right side.

We want to display the Contacts and Customers statistics in the dashboard. For each of the statistics, set the following properties on the right-hand side:

  • Primary value label = Contacts or Customers
  • Primary value = {{queries.contacts.data.length}} or {{queries.customers.data.length}}
  • Toggle to hide the secondary value:

enter image description here

Edit Chart’s properties and set the chart type as Bar:

enter image description here

The number of contacts in each Lifecycle stage will then be displayed in the chart. Provide a javascript array of objects with x and y coordinates to properly visualize the lifecycle stages. Input the chart data:

{
    {
        Object.entries(_.countBy(queries.contacts.data, 'LifecycleStage')).map(row => ({
            x: row[0],
            y: row[1]
        }))
    }
}

The results will look like this:

enter image description here

Modify data in Baserow

Next, we want to be able to edit the CRM and have those changes reflected in our Baserow database.

Add and configure form modal

Let’s start with the Contacts details. In the Contacts tab, add a modal at the bottom of the screen and name this ‘Add contact’. Drag and drop some input and dropdown fields that correspond with our database columns to configure this modal:

enter image description here

Next, get the dropdown details for the Plan and Lifecycle stages. Retrieve the select options ids from the Baserow API documentation:

enter image description here

Input these as the Option values: {{[284203, 284204, 284205, 284206]}} and option and labels: {{["Interested", "Negotiating", "Won", "Lost"]}} in the dropdown properties in ToolJet:

enter image description here

Repeat this process to fetch data and configure the Plans dropdown options:

enter image description here

When we click the ‘Create Contact’ button within the modal, we want to populate our Baserow database. Add an event handler to the action button on click. Choose Run Query as the action, then select addContact as the query:

enter image description here

To close the modal after the query is run, add another event handler to the action button on click. Choose Close modal as the action, then select Modal 1 as the modal.

After the table is closed, we want to run another query to update the data. Add another event handler to the action button on click. Choose Run query as the action, then select Contacts as the query:

enter image description here

Add and configure the button

Let’s now add an action button to the table so that when users click on the button, the modal will pop up. We will add an event handler to the action button on click. Choose Show modal as the action, then select the modal:

enter image description here

When the button is clicked in the app, we want to run an operation to create a new row in Baserow.

Add a new query to Create row operation

When the team uses the CRM in the front end, they may need to add or modify data without modifying the database directly. This is possible with the Create row operation.

This query will populate the Contact and Customer tables in the database with input from the CRM form fields. You will need the Table ID and Records parameters from the Baserow API documentation.

  • Create a new Baserow query. Select Create row from the operations dropdown, then enter the Table ID for the Contacts table.

  • To set up a Create Row query, we will need to pass data from input fields in the form to create a new row in the Baserow database. Add the following Records to pull values from the text input fields added earlier:

    {
    	"Name":"{{components.textinput1.value}}",
      "EmailAddress":"{{components.textinput2.value}}",
      "PhoneNumber":"{{components.textinput3.value}}",
      "LifecycleStage":"{{components.dropdown1.value}}",
      "ExpectedDealSize":"{{components.textinput4.value}}",
      "Plan":[{{components.dropdown2.value}}]
    }
    

    Note that the input field names must match the Records in the addContacts and addCustomers queries.

    The query to add new contacts will look like this:

    enter image description here

  • To run the query every time the app is loaded, enable the Run query on page load? in the Advanced tab. This means that the addContact query will run every time the app loads.

  • Click on Save & Run

Repeat the steps above to create an addCustomer query. Select Create row from the operations dropdown, then enter the Table ID for the Customers table.

When the form is filled, a new contact will be added to your Baserow database. You’ll be able to see the updated data in the table.

Finally, 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.

D. Release 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.

enter image description here

That’s all!! You have successfully developed a CRM tool to store and analyse customer data.

If you’re looking for something else, please feel free to make recommendations or ask us questions in our online community—we’re ready to assist you!

release
November 3, 2022 by Bram Wiepjes
1.13 release of Baserow

Today we are announcing Baserow 1.13 with two new security features as a part of Baserow Enterprise: role-based access control and single sign-on.

info
May 21, 2020 by Bram Wiepjes
Best Airtable alternatives
info
July 14, 2022 by Hiram
The no-code landscape