Building a Retool app that's connected to Salesforce via Sequin

Eric Goldman
Eric Goldman
Co-founder @ Sequin

Sep 21, 2022

This is a guest post that originally appeared on the Sequin docs. It describes an optional path for connecting to Salesforce data through Sequin, which you could use with or instead of Retool's officially supported Salesforce connector. Learn how Sequin unleashes APIs at sequin.io!

In this tutorial, you'll build a tool to manage Salesforce leads using Retool. The app will allow your sales team to enrich and qualify new leads all in one action.

While Retool already has a built-in Salesforce adapter, Sequin lets you integrate with Salesforce using Retool's first-class support for Postgres. This integration path provides faster response times while avoiding Salesforce rate limits and dealing with SOQL or the APEX REST API.

In this tutorial you'll learn how this all works together to build a tool to scale your inbound sales pipeline. Outside of Sequin and Retool, you'll just need a little SQL and JavaScript to wire it all together.

Salesforce Setup

The scenario for this tutorial is very common: New users sign up for your service and your sales team needs to do a little research to determine if these new users are qualified leads.

While an everyday task, this kind of workflow in Salesforce is cumbersome: Lots of repetitive copy, paste, and page reloads. Creating and maintaining a custom set of interfaces in Salesforce isn't an easy option.

This is where Retool comes in. In fact, this tutorial is inspired by an existing Retool template. This tutorial showcases an even easier and faster route.

For this tutorial, you'll need access to a standard or dev Salesforce account. You'll work with the standard leads object throughout this tutorial. You'll also work with a custom field to structure and store information the sales team finds during their research, like the link to the lead's LinkedIn profile.

To get started, create a custom field in Salesforce:

  • Log into your Salesforce account and navigate to Setup by clicking the gear icon in the top right (next to your user thumbnail).
  • The setup screen will open in a new tab. Now use the Quick Find to find and open the Object Manager:
  • In the Object Manager, use the Quick Find again to find and select the Lead object.
  • Select Fields & Relationships in the left menu bar and click the New button to create a new custom field:
  • For datatype, recall that you'll be storing a link to LinkedIn — so select URL and click Next to name and label the field LinkedIn. Feel free to add a description and help text then click Next.
  • Review the configuration of the new LinkedIn field and click Save to add it to your lead object:

If you want to follow this guide exactly as written, you'll also want to customize the Lead Status options in Salesforce. These are the statuses your sales team will use to indicate if a lead is a good fit.

  • Select Lead Status from the list of fields on the Fields & Relationships page.
  • In the Lead Status Picklist Values section, edit the options to be New, Contacted, Qualified, Unqualified:

As a last step, create a couple new leads you can work with in this tutorial by clicking the New button on the leads page or upload this sample set by clicking Import.

Sequin Setup

Create a two-way sync between Salesforce and your Postgres database so you can easily work with the leads you just created.

  • Go to https://app.sequin.io/signup and create an account.
  • Connect Salesforce by going through the tutorial or clicking the Add Sync button.
  • You'll be prompted to enter your Salesforce subdomain. Then follow the Salesforce authentication steps by entering your Salesforce username and password in the modal:
  • You'll now select which Salesforce objects and fields you want to sync. For now, just select the Lead object. You can adjust which objects and fields you sync at any time:
  • In the Destination section, you can connect to your Postgres database. Or, for the purposes of this tutorial, spin up a Sequin demo database. Just click Launch or Connect and then select Launch.
  • When everything is configured, click Create. Sequin will now start backfilling all your Salesforce leads to Postgres and maintain a real-time, two-way sync. You'll be provided with credentials for you new Sequin demo database:

Retool Resource Setup

Now, add your Sequin database to Retool just as you would any other Postgres database:

  • In a new tab, log into Retool. In the top menu bar click Resources and then the blue Create New button.
  • Select Postgres from the list of resource types.
  • Enter the name for your resource (i.e. "Salesforce Database") and then enter the Host, Port, Database name, Database username, and Password for your Sequin database. You can copy and paste these from Sequin. Then click the blue Create resource button:
  • Retool will confirm that your resource was created. Click Back to resources for now.

Retool App Setup

With Salesforce successfully connected to Retool using Sequin, you are ready to build an app that allows your sales team to quickly qualify leads.

First, get the app set up in Retool.

  • On the Retool home page, click the blue Create new button and select App:
  • Give your app a name. Something like Inbound Qualifier will work just fine and then click Create app.
  • You'll now see a blank Retool app in edit mode. To start building the app, drag and drop a text field into the header. Then, in the inspector panel on the right, enter # Inbound Qualifier as the value to give your app a nice H1 header:

This is the basic flow for adding new components to your app:

  • Drag and drop the visual components into your app.
  • Configure the data and interactions for the component.
  • Polish the UI of the component.

You'll follow this construction pattern as you build the rest of the app from here on out.

Searchable leads

You are now ready to build the core functionality of your app, starting with a searchable table that shows new leads.

Scaffold the UI

You'll start by dragging and dropping the components you need for this section of the app onto the canvas:

  • Drag and drop a Container component to the top left corner of the canvas. Resize it to a width of about 9 columns.
  • Drag and drop a Table component into the main section of the container you just created.
  • Delete the text in the header of the container. In its place, drag over a Text input component. This will serve as the search bar — so in the inspector on the right, change the label for this component to Lead Search.
  • To finish this section of the UI, drag and drop a Multi-select input component into the top right of the container on the canvas. This will allow your sales team to filter the leads by status. So in the inspector, change the Label for this component to Lead Status.

At the end of these steps, your app will look something like this:

Connect your leads

To add the underlying Salesforce leads to your app, you'll query your Sequin database using SQL. To step into this paradigm, you'll first query for the data you need and then expand your query to add search and filtering.

  • Open up the bottom panel in Retool. Select query1 from the list of queries on the left. Then, select the Salesforce Database you created earlier as the resource. Enter the SQL statement below:
1   SELECT
2       id,
3       created_date,
4       name,
5       email,
6       title,
7       company,
8       number_of_employees,
9       industry,
10       status,
11       website,
12       linked_in__c
13   FROM lead;
14
  • When you click the Preview button you'll see that this query pulls in the fields your sales team needs to evaluate a lead, including the custom LinkedIn field you created earlier:
  • This query looks good for now, so click the Save & Run button and then name the query get_leads by clicking the query in the list on the left.
  • To connect the data from the get_leads query you just created to the table component in your app, open the inspector in the right panel, select the table in the canvas, and in the data field enter {{get_leads.data}}. The double brackets (i.e. {{}}) indicate that you are using JavaScript in Retool. Then, the get_leads.data is retrieving the data from your query.
  • You'll see the data from your query populate the table:

With the data flowing into your app, you can now connect your search and filter inputs to your get_leads query.

  • First, add search to your app by opening up your get_leads query in the bottom drawer panel and add the following WHERE clause:
1   SELECT
2       id,
3       created_date,
4       name,
5       email,
6       title,
7       company,
8       number_of_employees,
9       industry,
10       status,
11       website,
12       linked_in__c
13       FROM lead
14       where ({{ !textInput1.value}}
15        or (name ilike {{ '%' + textInput1.value + '%'}}
16        or email ilike {{ '%' + textInput1.value + '%'}}
17        or company ilike {{ '%' + textInput1.value + '%'}}));
18

This WHERE clause is performing two actions:

  • First, it checks if there is a value in the text input (i.e. textInput1.value). If there is nothing in the text input, then nothing happens.
  • If there is text in the text input, then it uses Postgres ilike to search across your lead's names, emails, and companies. Remember, the double brackets indicate that you're using Javascript in the query.
  • Next, add filtering to your app by adding one additional statement to your WHERE clause:
1   SELECT
2       id,
3       created_date,
4       name,
5       email,
6       title,
7       company,
8       number_of_employees,
9       industry,
10       status,
11       website,
12       linked_in__c
13       FROM lead
14       where ({{ !textInput1.value}}
15       or (name ilike {{ '%' + textInput1.value + '%'}}
16           or email ilike {{ '%' + textInput1.value + '%'}}
17           or company ilike {{ '%' + textInput1.value + '%'}}))
18       and  ({{ multiselect1.value.length === 0 }} or status = any({{multiselect1.value.value}}))
19

Like the prior WHERE statement, this one also checks to see if there are any filters in the multiselect input. If there are no values (i.e. the array is empty) then nothing happens. If there are filter values in the multiselect, then the query is filtered to just the leads with those specific statuses.

  • Click the Save & Run button and you'll now see that when you search for a name, email, or company, the table immediately responds. But, your multiselect isn't configured yet. To fix this, open the right panel and select your multiselect component in the canvas. Configure the options to match the statuses in Salesforce:

Value Label Color New New "Purple" Contacted Contacted "RoyalBlue" Qualified Qualified "Green" Unqualified Unqualified "Red"

Your table is now easy to search and filter:

Polish the table

As a last step, clean up your table and interface to make it easy to use.

  • Select the table in the canvas and open the right panel.
  • Simplify the table by removing data that is helpful to the app, but not to your sales team. In this case, you can hide the email, website, linked_in__c, and id columns from the table by clicking the eye icon in the inspector.
  • Next, name and format each column in your table by selecting each column in the inspector, changing the name, and setting the data type. For instance, edit the created_date column so the Column title is now Sign up, and the Column type is Date Time:
  • Finally, make the status column stand out. It's the key field your sales team will be working with the most. To make it pop, select the status column, change the Column title to Status, change the Column type to Tag (Dropdown), and for Option color select JS and enter the following:
1{
2 {
3   item == "New"
4     ? "purple"
5     : "" || item == "Contacted"
6     ? "RoyalBlue"
7     : "" || item == "Qualified"
8     ? "green"
9     : "" || item == "Unqualified"
10     ? "red"
11     : "";
12 }
13}
14

This will make the color of each status tag match the colors you used in the multiselect filter. Nice!

You now have a searchable table that allows your sales team to quickly filter and search leads as they come in. Next, you'll make it easy for your sales team to research and enrich leads with a click.

Enrich leads

As your sales team finds and selects a lead in the table, you'll show the details of the lead on the right. Here, the sales person will be able to research the lead with a click and then edit the lead's details based on what they learn.

For this section, you'll repeat the same construction pattern by first scaffolding the UI, connecting the data, and then cleaning up the interface.

Scaffold the UI

This section of the app is a form that is populated with data from the selected lead in the table. As before, start by getting the component parts of this section onto the canvas:

  • Drag and drop a Container component next to the table. As before, delete the container title and in it's place drag and drop an Avatar component.
  • Next, create the quick link buttons by dragging and dropping four Button components into the container. At this point, this section of the app will look something like this:
  • Now, you'll add the editable inputs starting with Lead Status. Since you've already configured the Lead Status multiselect component in your table container, you can just duplicate that component to jump start this section of the app. To do so, right-click the Lead Status component and click Duplicate. Drag the duplicated component into your new container. You only need a single select component here (since a lead can only ever have one status). So change this component to a simple select component by clicking the three dots in the top right corner of the inspector, selecting Switch component, and picking the Select component:
  • Next, drag and drop three Text Input components, one Number Input component, and one more Button component onto the canvas. Change the labels on each component so this section of your app looks like this:

Connect the data

You'll connect data to this section of your app in three phases. First, you'll want to populate the avatar and text input components with the details (eg. name, title, etc.) of the lead selected in the table. Next, you'll configure the quick link buttons. And finally, you'll make the form work by connecting the Update Lead button to a query that updates your Sequin database and Salesforce simultaneously.

Populate the fields

  • Select the Avatar component. In the inspector, set the Label field to {{table1.selectedRow.data.name}}. This pulls in the name from the selected lead in your table. For the Caption pull in the lead's company by entering {{table1.selectedRow.data.company}}. Then, to make the avatar look nice, set the Fallback text field to {{table1.selectedRow.data.company}}.
  • Next, select the Lead Status component and set the Default Value to {{table1.selectedRow.data.status}}. Repeat this process for the remaining Text input and Number input fields:

Field Default Value Position {{table1.selectedRow.data.title}} LinkedIn {{table1.selectedRow.data.linked_in__c}} Company {{table1.selectedRow.data.company}} Employees {{table1.selectedRow.data.number_of_employees}}

Configure the quick link buttons

You'll now configure each Button so that your sales team can quickly research the lead by searching LinkedIn, Crunchbase, and the lead's website.

  • Select the top left button and change the Text to user. Then, configure the button's action by adding an Event handler. Click the + Add link and in the modal set the Action to Go to URL. For the URL enter:

{{"https://www.linkedin.com/search/results/companies/?keywords=" + table1.selectedRow.data.name;}}

This URL is configured to pass in the selected lead's name as a search parameter to LinkedIn, saving the sales team multiple clicks. Nifty!

  • Repeat the steps above to configure the remaining buttons:

Button Text URL Company {{"https://www.linkedin.com/search/results/companies/?keywords=" + table1.selectedRow.data.company}} Crunchbase {{"https://www.crunchbase.com/textsearch?q=" + table1.selectedRow.data.company}} Website {{table1.selectedRow.data.website}}

At this point, your app will look something like this:

Connect the form

As the sales team learns about the lead, they'll add a LinkedIn profile URL and enrich the leads details. When they are done, they'll click the Update Lead button to push those changes back to Salesforce. You'll now write the SQL query that will post these changes to your Sequin database. From there, Sequin will handle the sync with Salesforce.

  • Open the bottom panel and click to the + New button and select Resource query
  • Select your Salesforce Database as the resource and, since you are writing data back to your database, select GUI mode. Then configure the query as follows:
    • Table - lead
    • Action type - Update an existing record
    • Filter by - id = {{table1.selectedRow.data.id}}
    • Changeset - Key value pairs
  • You'll then map each column you want to update in the database to the component value in your app:
    • status - {{multiselect2.value}}
    • title - {{textInput2.value}}
    • linked_in__c - {{textInput3.value}}
    • company - {{textInput4.value}}
    • number_of_employees - {{numberInput1.value}}
  • After you update a lead, you'll want to refresh the data in your app. So in the Event handlers section of your query, click to + Add an on success event handler that will trigger the get_leads query.
  • When you are done, click save and in the query list on the left name the query edit_lead:
  • As a last step, connect this query to the Update Lead button in your app by selecting the button in the canvas, opening the inspector on the right, clicking to + Add an event handler, and setting the Action to Control query. Set the Query to edit_lead.

When you're done, you'll see you can easily edit and update leads:

Polish

You now have a functioning app the reads and writes data to Salesforce. To make it more usable, polish this component by changing the button colors, adding icons, and spacing out components. Here is one approach you can take:

  • Add icons to the buttons and fields by selecting the button, opening the inspector, and setting Prefix icons.
  • Change the colors of the buttons. In the inspector, make the save button green and help the user navigate by making styling the buttons.
  • Drag and drop divider components to help space our the information.

Bulk actions

One thing that is particularly hard to do in Salesforce that is easy to do with Sequin and Retool is allow your sales team to quickly edit multiple leads at once. For example, your team might qualify 5 new leads because they all come from the same company. Save your team the clicks by letting them just update the fields in your leads table. Here is how:

  • First, make the Status field in your table editable. Select the table on the canvas, open the inspector in the right panel, and then click the Status column. In the modal, flip the switch to make the column editable. You'll now be able to double click a status in the table to open up a dropdown:
  • As the sales team changes the status of multiple leads, you'll want to save these changes back to Salesforce. To do so, create a new query in the bottom panel with the following configuration: This query will take in each id from the array of objects in table1.recordUpdates, find the records that match the id in your Salesforce Database, and then UPDATE the status field in those records. Click to Save your query and name it bulk_edit_lead_status in the query list on the left.
    • Resource - Salesforce Database
    • Table - lead
    • Action type - Bulk update via primary key
    • Primary key column - id
    • Array of records to update - {{table1.recordUpdates.map(l => ({"id":l.id,"status":l.status}))}}
    • Event handler: Success - Trigger get_leads
  • As a last step, connect your bulk_edit_lead_status query to your table. Select the table in the canvas, open the inspector in the right panel, in the Interaction section click to + Add an event handler that will trigger the bulk_edit_lead_status query when the user clicks Save changes:

Now edit the status for a couple leads in the table and click Save changes at the bottom of the table:

Get ready for your inbound sales effort to scale!

Customize

You now have a custom internal tool that will help your sales team quickly qualify leads.

With Retool and Sequin, you didn't touch SOQL or APEX to get to this point. You didn't need to paginate queries, deal with rate limits, or worry about response times. All you needed was a little SQL and a smidgen of JavaScript.

From here, tailor this app to your sales team's needs. Bring in data from your production database and join it to your Salesforce data to give your sales team even more context (Sequin can put your Salesforce data in your database!) Add more fields to let your sales team capture notes - or even trigger emails. This is truly just the beginning.

Reader

Eric Goldman
Eric Goldman
Co-founder @ Sequin
Sep 21, 2022
Copied