How to create a 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.

• Build the database in Baserow
• Build an interface in ToolJet
• Release your application

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

• A Baserow account in either the self-hosted or hosted baserow.io SaaS versions
• A free account on ToolJet Cloud or run on your local machine

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 workspaces and databases.

A workspace 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 workspace called 'ToolJet CRM’. On the Dashboard page where you can see an overview of all your workspaces and databases, click the ‘+ Create workspace’ button. This button will also be visible in the sidebar:

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

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

• Plans
• Contacts
• Customers

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

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:

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:

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:

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:

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 workspace and grant relevant access. It’s easy to collaborate with data in real-time.

To add workspace collaborators:

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

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:

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:

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

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

Get Baserow Database token

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

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

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:

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 workspace to access the API documentation. Then select ‘View API Docs’ from the workspace menu:

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

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:

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

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

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

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:

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:

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:

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:

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

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:

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:

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

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

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

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:

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:

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:

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:

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

That’s all!! You have successfully developed a CRM tool to store and analyze 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!

What’s your story? Tell us how you use Baserow