Build Customer Relationship Management (CRM)- Using Airtable and ToolJet

This article is a complete guide to build a a customer relationship management app. Users can list, delete, update & create record on Airtable. We'll have a detailed insight around a customer's order detail. To access the app visit this link:

We are going to build this app with three simple steps:

1. Building the UI
2. Integrating data sources & creating queries
3. Connecting event handlers

You can choose to build your custom UI. We'll have the following features in this app.

1. Create new lead
2. Update existing lead
3. Delete existing lead
4. List the records

Prerequisites

1. ToolJet (https://github.com/ToolJet/ToolJet): A free and open-source low-code platform that allows you to quickly build applications. Sign up here.

2. Airtable: A replacements to traditional google-sheet, Airtable is a database to read & write data.

This tutorial will help you build the complete app in minutes, it'll allow you to understand various steps with a full description. It would be a best practice if you go through the above links before we start building our app.

Building the UI

CRM app we're going to build will look like this, you're free to choose your custom UI. Let's buckle up for a fun developing experience 🤩

First, let's start with building the UI for our app. Open ToolJet Cloud on a new tab & click on “Create New Application”. We'll require the following widgets to make our entire app.

1. Container
2. Various input fields
3. Button
4. Table
5. SVG-Image
6. Modal
7. Divider

Start with following the steps one by one:

• Divide your canvas into 2 containers. The first container will have table widget & the other one will display customer details with delete & edit operations.

• You need to configure your table widget with the following input values.

• Now, on the second container drag-drop widgets same as the UI below. You have to use the following widgets:

1. Text: To display all the texts in the present in the UI.
2. Divider: To provide a separation b/w column names & values.
3. Button: For edit and delete operation
4. SvgImage: For logo and icons

We'll be able to collect the following data around a customer:

• Name
• Contact
• Email
• Address
• Delivery date
• Member assigned
• Cost price
• Selling price
• Quantity
• Special request

• Items

  • Next, create your modals for edit & create operations. Replicate the following UI for both the modals.

• Bind your modal input fields to connect it with the data.

1) Create modal : Inspect the widgets in the UI and add placeholder texts as per your choice.

2) Update modal : Inspect the widgets in the UI and add default values as shown below. You must sync it with your widget names.

a) Name: {{components.table1.selectedRow.name ?? ''}}
b) Email: {{components.table1.selectedRow.email ?? ''}}
c) Contact: {{components.table1.selectedRow.contact ?? ''}}
d) Address: {{components.table1.selectedRow.address ?? ''}}
e) Priority: {{components.table1.selectedRow.priority ?? ''}}
f) Status: {{components.table1.selectedRow.status ?? ''}}
g) Items: {{components.table1.selectedRow.items ?? ''}}
h) Member: {{components.table1.selectedRow.member_assigned ?? ''}}
i) Cost Price: {{components.table1.selectedRow.cost_price ?? ''}}
j) Quantity: {{components.table1.selectedRow.quantity ?? ''}}
k) Special Request: {{components.table1.selectedRow.special_request ?? ''}}
l) Selling Price: {{components.table1.selectedRow.selling_price ?? ''}}

• Now trigger both your modals on respective button clicks, On the Navbar we've our create new lead button. Inspect and add an event handler. Select Show modal under action and the name of your Create new lead modal under modal name. Do the required for your edit modal as well.

Congratulations 👏
You have successfully completed the first step, Next, let's move to the most important step.

Integrating data sources and creating queries

We'll use Airtable to implement the backend for our app. You can visit this link to setup your account if you haven't already done it.

Step 1: On ToolJet, go to "sources" tab & add a new source. Search & select Airtable.

As the next step you need to fill the credentials for API key which you can access from this link once you create your account.

Step 2: Once the data source is successfully saved, let's set up Airtable data sheet.

a) Name your sheet as per usecase, I've named it
as user_list.

b) Add the following column names & specify their data
types as the following. You should name them in small-
cases, its a good practice. These are the inputs we'll
use to manage our customers.

 `
 **name**: String
 **contact**: number
 **email**: String 
 **address**: Long text
 **deliver_date**: Date
 **member_assigned**: Single select
 **cost_price**: number
 **selling_price**: number
 **quantity**: number
 **special_reuqest**: Long text
 **items**: Single select
 **priority**: Single select
 **status**: Single select
`

You should set your custom options for the next step, else copy the details below.

• Under member_assigned, type Single select, enter & save the following options.

• For items,type Single select, enter the options below.

• For status, type Single select, enter the options below.

• For priority , type Single select, enter the following options.

Our table setup is completed now, let's continue with the next step.

Step 3: Once the data source is successfully added, we'll configure four operations so we have to create queries respectively.

a) First Operation- ListRecord

It'll be used to access all the records from our Airtable sheet. We'll use it to populate data in the table widget. You can also refer this link to read the documentation for Airtable integration.

• Expand the query panel at the bottom & click on create query button & select Airtable.

• Now, under Operations, select List records.

• For Base ID, go to your Airtable data sheet & on the top right corner click on Help button. A side drawer will appear, at the bottom click on "API Documentation". You will be redirected to a new page where you can find the Base ID. It'll look something like this apptJ2LJtQqSt0CYh. Note: use your unique Base ID & don't copy this.

• Under Table name, enter the name of your table. I've entered it as lead_list

• We'll avoid enter values for Page size & Offset. You can use these values if it fulfils your app requirement.

-Now, toggle on "Transformations" to modify your data source. We're creating an array of objects using this function with the required data we want for our app.

return data.records.map(row => {
let obj = {}
obj.fields = row.fields
obj.fields.id = row.id
return obj.fields
});

You can preview the output of this code below the code editor. Once all the values are entered, click on create & then save query.

b) Second Operation- CreateRecord

This query is used to Create a new row in our Airtable spread-sheet. Start with selecting the Operation as Create record. For the Base ID & Table name enter the values from the previous step.

Under Records, here we'll enter the row value that we want to enter in our spreadsheet in the following format, also keep in mind to sync the correct widget names as per your modal UI.

[{"fields":{
 "name":"{{components.textinput2.value}}",
 "email":"{{components.textinput1.value}}",
 "address":"{{components.textarea2.value}}",
 "priority":"{{components.dropdown4.value}}",
 "delivery_date":"{{components.datepicker1.value}}",
 "member_assigned":"{{components.dropdown3.value}}",
 "contact":{{parseInt(components.numberinput7.value)}},
 "cost_price":{{parseInt(components.numberinput1.value)}},
 "selling_price":{{parseInt(components.numberinput2.value)}},
 "quantity":{{parseInt(components.numberinput3.value)}},
 "items":"{{components.dropdown2.value}}",
 "special_request":"{{components.textarea1.value}}",
 "status":"{{components.dropdown1.value}}"}}]

Note: You have to parseInt the number inputs i.e contact, selling price, cost price & quantity.

Once you enter all the values, you can trigger Create modal which we built in the first step. You can fill input fields with values in the modal and, preview the data at the bottom of the query panel. This is an ideal way to test & understand the flow of the data in a query.

c) Third Operation- UpdateRecord

This query is used to Update an existing row in our Airtable spread-sheet. Start with selecting the Operation as Update record. For the Base ID & Table name enter the previous values.

For the Record ID, we've can access it from our data source we transformed. Enter the following value.
{{components.table1.data.selectedRow.data.id}}

Under Record ID, we'll input the row value that we want to update in our spreadsheet in the following format, also keep in mind to sync the correct widget names as per your edit modal UI.

{
 "name":"{{components.textinput5.value}}",
 "email":"{{components.textinput4.value}}",
 "address":"{{components.textarea4.value}}",
 "priority":"{{components.dropdown5.value}}",
 "delivery_date":"{{components.datepicker2.value}}",
 "member_assigned":"{{components.dropdown6.value}}",
 "contact":{{parseInt(components.numberinput8.value)}},
 "cost_price":{{parseInt(components.numberinput5.value)}},
 "selling_price":{{parseInt(components.numberinput6.value)}},
 "quantity":{{parseInt(components.numberinput4.value)}},
 "items":"{{components.dropdown8.value}}",
 "special_request":"{{components.textarea3.value}}",
 "status":"{{components.dropdown7.value}}"
}

Once you enter all the values, you can trigger Edit modal from the first step. You can fill input fields with values in the modal and, preview the data at the bottom of the query panel to understand the flow of the data.

d) Fourth query- DeleteRecord

This query is used to delete an existing record in our Airtable spreadsheet. Follow the steps to quickly set up this query.

1) Start with selecting the Operation as Delete record. For the Base ID & Table name enter the previous values.

2) In the record ID enter the the following value
{{components.table1.selectedRow.id}}.

Amazing!! 🤩 We have successfully implemented our queries. Now let's start creating and connecting our event handlers.

Connecting event handlers

Now that we have built the user interface & created all the queries, all we need to do is connect the UI with queries. Let's get started:

• Trigger the Create_Lead modal, inspect the "Create" button in it & add the following four-event handlers in the same sequence:

a) CreateRecord: Under action, enter Run Query and select your Create Record query name as Query.

b) ListRecord: Under action, enter Run Query and select your List Record query name as Query.

c) CloseModal: Under action, enter Close Modal and select create new record Modal name as Modal.

d) ShowAlert: Under action, enter Show Alert. Add the message and Type to alert the user on success of this query. I've enter New lead added as message and success alert type.

• Trigger the Update_Lead details modal, inspect the "Update" button in it & add the following four-event handlers in the same sequence:

a) UpdateRecord: Under action, enter Run Query and select your Update Record query name as Query.

b) ListRecord: Under action, enter Run Query and select your List Record query name as Query.

c) CloseModal: Under action, enter Close Modal and select create new record Modal name as Modal.

d) ShowAlert: Under action, enter Show Alert. Add the message and Type to alert the user on success of this query. I've enter Lead Details updated as message and success alert type.

• Now inspect the delete button in the UI and add the following three event handlers in the same sequence:

a) DeleteRecord: Under action, enter Run Query and select your Delete Record query name as Query.

b) ListRecord: Under action, enter Run Query and select your List Record query name as Query.

c) ShowAlert: Under action, enter Show Alert. Add the message and Type to alert the user on success of this query. I've enter Lead Deleted as message and success alert type.

• Inspect the table widget and in the table data input field add the following {{queries.ListRecord.data}}. This will connect our table with Airtable spreadsheet data.

Binding the values in the right container of our app's UI. Inspect each text and add the following values. This will allow us to show the details when we select a table row. Kindly sync the names correctly as per your UI.

1. Name: {{components.table1.selectedRow.name}}
2. Email: {{components.table1.selectedRow.email}}
3. Contact: {{components.table1.selectedRow.contact}}
4. Address: {{components.table1.selectedRow.address}}
5. Priority: {{components.table1.selectedRow.priority}}
6. Status: {{components.table1.selectedRow.status}}
7. Items: {{components.table1.selectedRow.items}}
8. Member: {{components.table1.selectedRow.member_assigned}}
9. Cost Price: {{components.table1.selectedRow.cost_price}}
10. Quantity: {{components.table1.selectedRow.quantity}}
11. Special Request: {{components.table1.selectedRow.special_request}}
12. Selling Price: {{components.table1.selectedRow.selling_price}}
13. Final Price: {{(components.table1.selectedRow.selling_price - components.table1.selectedRow.cost_price)*components.table1.selectedRow.quantity}}

You can use ternary operator (? :) to display a custom option when no data is available. This will make the UI look better.
For e.g {{components.table1.selectedRow?.status ? components.table1.selectedRow.status: '-----'}}

Congratulations!! 🚀 You have successfully built your application. I hope you had a good time building something new today, you can start working on this app directly by selecting this template when you create a new application.

We are continuously integrating different data sources with ToolJet. Stay in the loop to read upcoming releases to develop useful internal tools. If you have any trouble building this application, please join our Slack community or email us at [e-mail].