Build Customer Relationship Management (CRM)- Using Airtable and ToolJet
Tags: airtable, webdev, programming, beginners
Published: 2022-06-07 10:17:26

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](https://app.tooljet.com/applications/4f7b3fa7-9a41-43e9-b6d5-29cf971c580e):

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](https://www.tooljet.com/signup).

2. **Airtable**: A replacements to traditional google-sheet, [Airtable](https://airtable.com/) 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 🤩



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cl4yw9kohcffm82bvptu.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fzclq0wqkktqtu7ojph2.png)


- 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 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lhtq5xp7svjn7y4hui9d.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3ubraogn4ixf9anqvb9b.png)

- 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](https://www.google.com/search?q=airtable+signup&rlz=1C5CHFA_enIN999IN999&oq=airtable+signup&aqs=chrome.0.69i59j0i10i512j0i512j0i390l4j69i60.62332j0j7&sourceid=chrome&ie=UTF-8) 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**. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aomobskpnmydzu21k3fv.png)

As the next step you need to fill the credentials for API key which you can access from this [link](https://airtable.com/account) once you create your account.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ws9frncb1sgte5fsfja2.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzkgb94cejbhhlzp08au.png)


- For **items**,type Single select, enter the options below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vs8lqvmemfitzsue3ket.png)


- For **status**, type Single select, enter the options below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zn4vet2rqgzsmppun5di.png)


- For **priority** , type Single select, enter the following options.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/07xw9vsh7s9tpjallfd4.png)

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](https://docs.tooljet.com/docs/data-sources/airtable/) to read the **documentation** for Airtable integration.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hfitj6drx76cuggz2ygj.png) 

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/orsnl2uplitvr760w4sn.png)

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0swdnksap2lqna0mzobt.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wb7ssb8kwodq3niltmao.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rcf5vn04dxt2fv8li73j.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wb7ssb8kwodq3niltmao.png)


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}}`.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1iquoqyec4k1mgypnysl.png)


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](https://tooljet.slack.com/join/shared_invite/zt-r2neyfcw-KD1COL6t2kgVTlTtAV5rtg#/shared-invite/email) or email us at hello@tooljet.com.