Salesforce is a leading customer relationship management (CRM) platform. It’s the central operating system for many sales teams and provides valuable data. Nevertheless, Salesforce has a notorious reputation for some of its rough edges:
This means that companies are paying vast amounts for user licenses where certain users only need a small amount of data, and also often leaves companies hiring from a small pool of expensive specialists who have received specific training on the platform.
Building on top of your Salesforce data with a platform like Retool can help bypass these high costs and complications. In Retool you can quickly and easily connect to your data in resources like Salesforce, and then use this data to create bespoke applications and internal tools with custom business logic.
What’s more, you can:
To make working with Salesforce data even more seamless, we will be using Sequin, a platform that allows us to bypass the use of SOQL entirely and query with SQL instead.
This article will guide you through some solutions to common Salesforce issues. It includes a step-by-step process of how to leverage Retool and Sequin to build a tailored app that enriches Salesforce data with ease. Here’s how the end app will look:
Salesforce’s proprietary querying language SOQL allows users to perform structured queries on Salesforce objects, such as standard and custom objects, to retrieve records that meet specific criteria. But unfortunately, the pool of developers proficient in the language (and knowledgeable of its limitations) is small. Odds are, you don’t have a SOQL expert on your technical team. Compare that to SQL, the third most-used programming language, used by almost half of developers worldwide* and one which at least a few of your engineers are certain to know.
Some of the key limitations of using SOQL include:
For a full list of differences between SOQL and SQL and more in-depth explanations, hop to the end of this article.
So, what’s the better solution? To avoid SOQL’s technical issues and make use of the SQL knowledge you likely already have on your team, you can use a product like Sequin. Sequin pulls Salesforce data into a Postgres DB and allows developers to query their data from there using SQL instead. Using Sequin you can make the connection between Salesforce and a building platform like Retool even more seamless.
Sequin acts as a proxy between Salesforce and Retool to bypass the limitations of SOQL and the Salesforce API. Sequin syncs data between APIs and Postgres, allowing you to avoid polling, webhooks, query parameters, or HTTP errors.
Sequin will set up a real-time, persistent sync to keep the data between Postgres and the Salesforce API up-to-date. When changes are made in Salesforce, they're synced to Postgres. When changes are made in Postgres, they're synced to Salesforce. This means that you can even write back to Salesforce via the database using SQL, something that isn’t possible with SOQL.
By using Sequin, you won’t need to worry about:
Making it a perfect solution for those looking to build on top of Salesforce data, without all the complications! Sequin currently also supports Hubspot and Airtable, with more integrations reportedly on the way.
Now that we have a better way of working with our Salesforce data, let’s get all our data connected and start building our app.
Our SOQL problem is already solved with the use of Sequin, but how can we get around the high cost of users and the use of Salesforce’s proprietary app-building platforms? Our top suggestion is to build on a platform like Retool, which simplifies building internal apps whilst making use of developers’ existing coding skills, like SQL and JavaScript. Retool not only speeds up development time, but the pricing structure is much more conducive to teams that only require occasional or limited access to sales data in Salesforce.
The goal of this app is for our BDR team members to be able to enrich lead data from Salesforce without needing to pay for additional Salesforce users. We’ve also added some additional functionality to optimize this process and improve UX/UI.
In the rest of this tutorial, we’ll show you how quickly you can set up a bespoke lead data enrichment tool using Retool and Sequin to build on top of your Salesforce data. Here’s what our final product looks like after less than 2 hours of development:
Connecting your Salesforce data in Sequin is really simple and just requires following the steps in Sequin’s connection workflow. You’ll need to follow the Salesforce authentication system. Then, you can connect this to a Postgres database: a pre-existing one in your system, or one hosted by Sequin. Once connected, your Salesforce data will all be live-synced to this database, giving you more freedom and flexibility to work with your own data.
Once your Salesforce data is connected in Sequin, you’ll just need to connect to your Postgres database in Retool. You’ll need the Host, Port, Database name, Database username, and Password for your database, which you can find in the Sequin portal or in your Postgres settings.
Both Sequin and Retool offer an easy walkthrough for connecting to these resources, but, if you need more help, head to Sequin’s resources for step-by-step instructions before continuing with this tutorial.
Now, let’s walk through how we built this lead enrichment app in the Retool IDE.
Once you’ve connected your data in Retool, open up a new app. In the bottom code panel, add a new query and select your Postgres DB as the resource. Now, you can use SQL to write a simple query pulling the data into our app. You can use SELECT * to pull in all the table data, or select specific columns as we have in ours:
For this simple tutorial, we only need to pull in data from a single table, but since we are using SQL rather than SOQL, we have the potential to perform much more complex queries, joining, grouping, and creating subselect queries.
Another benefit of using the Sequin Postgres table is that we can see the schema on the right-hand side of our query, making it easier to find and select the tables and find the right fields and values. By comparison, to complete this same kind of introspection with SOQL you would need to go separately into the Salesforce Object Manager and find the object and the API names of each individual field that you need, so you know exactly what you need to pull.
Here is an example of a query that is possible with SQL and not SOQL:
You can read more about what’s possible in SQL and not SOQL at the end of this article. If you are a SQL beginner, you can follow our SQL table filter tutorial to set up the basics.
Now that all our data is connected and we can easily query it with SQL, we can get started with building the functionality of the application.
To begin with, we’ve pulled in a table component to display the data. We can connect this to our Postgres data source by referencing the query data in the right-hand inspector panel like so:
For this table, we also want to filter the data by the ‘Lead Search’ and ‘Lead Status’ inputs. To do this, we first pulled in a ‘text input’ for the Lead Search.
To filter the table data by the searched value, Retool offers an easy UI under the ‘Interaction’ section of the table component settings. Here we just need to reference the text component’s value attribute. We set the search settings to ‘fuzzy search’ to make the search as inclusive as possible. This search box will now search the entire dataset for searched terms.
We then pulled in a ‘Select’ component to create a dropdown for the Lead Status, which we manually programmed with the two options for the status field.
Now, to filter the data by the selected Lead Status in the dropdown, we’ve added a JavaScript ternary to the table data source, which checks for a value and uses the .filter function to filter by the value, if one is selected. The filter function requires an array type, so we first formatDataAsArray to allow this.
By filtering the data with JavaScript here, we are only pivoting the data on the front end, which means we aren’t having to query the data each time the inputs change. However, in certain cases (such as with large loads of data) it would make sense to filter the SQL query to reduce load on the computer. To learn more about setting up SQL filters, head over to our table filter tutorial.
Here’s what we set as our table data source:
To finish off our filter functionality we’ve added a ‘Reset filters’ button which uses two simple event handlers to reset the values of our components.
Now, we can use the table component’s built-in functionality to display the data just how we’d like. We can hide the fields we don’t need to see and set the data to the respective types.
For the ‘Name’ column, we have optimized the use of space by setting the format to ‘avatar’, and setting the ‘add-on’ caption to appear as the source row’s email using a reference in JS escape hatches. We’ve also set the image to match the company logo or profile image of the contact. We’ve used the Google favicons link to automatically match the logo of the website.
For the Company column, we’ve added another simple UI feature that helps optimize the use of space in the table, by adding a link button to a small link icon. To do this we’ve used HTML to map the value and send the link to the company website in a new tab.
For the status tag, we’ve used a ternary to change the color of the tag by value, so users can quickly interpret the data to qualify at first glance.
Using Retool, we can quickly insert JavaScript almost anywhere to customize the app to our specific needs. For simple tasks, you can easily use JavaScript escape hatches (like we’ve done so far). But for more complex scripts it’s also possible to code like you usually would, by creating a JavaScript query to reference instead:
Now that we have customized and optimized the data displayed in our table, we can set up the side panel and inputs that our Sales team will use to enrich the data.
To begin, we pulled in a container to hold our components. We then used a text box to display the name and company of the contact by referencing the selectedRow. We set the text as a header using markdown.
To aid the user in enriching the data, we’ve added these four buttons. The ‘Lead LinkedIn’ and ‘Company LinkedIn’ use the table data to populate Google search with some key terms to speed up search time:
https://www.google.com/search?q=%22linkedin.com%2Fin%2F%22+{{encodeURI(tblLeads.selectedRow.name)}}
When clicked, the button will pre-populate the Google search parameters in a new tab to eliminate some extra clicks and help the user find LinkedIn faster.
For the other buttons, the code in the ‘Go to URL’ click handler is as follows:
Company LinkedIn:
Find Revenue Crunchbase search:
https://www.google.com/search?q=%22https%3A%2F%2Fwww.crunchbase.com%2Forganization%2F%22+{{encodeURI(tblLeads.selectedSourceRow.company)}}
Open Website:
{{tblLeads.selectedRow.website}}
Below these buttons we’ve included some text inputs that allow the user to then update the data in the record if found. The default value is the value already in the table if it exists. We’ve added an additional placeholder to guide the user with a more effective UI.
For the industry dropdown, we have mapped the data with the _.uniq Lodash function to only display the unique values in the ‘industry’ backend data as options.
For annual revenue, we used a number input with the currency format.
The Lead status is manually mapped with the Qualified and Unqualified options.
Finally, to update the details in the backend, we’ve added an update query.
📌 Important note: Salesforce’s SOQL doesn’t allow you to write back to their database, instead you would have to set up new API PATCH requests to update data, and monitor their success. But mutations via Sequin’s Postgres Proxy are applied to Salesforce’s API and your Postgres database simultaneously. Writing back to Salesforce using normal SQL greatly simplifies the development process.
First, we’ve connected the click handler of the ‘Update’ button to trigger our new update query.
For the SQL query, we’ve used the UPDATE command and are writing back the data as the values of each component, based on the ID of the selected row.
Once the SQL query runs, we want to trigger two more actions: we want the first SELECT query to rerun with the updated values to keep the table up-to-date. And we also want to reselect the currently selected row to ensure a smooth UX.
When we run this query, it sends the data back to the Postgres database, which, in turn, updates the data via the proxy sync to Salesforce. Sequin takes care of all the complex parts of working with the Salesforce API, so you can get on with developing like you normally would.
Since Retool has a native integration with OpenAI, we’ve used the prompt query to help generate a sales pitch specific to the client’s data as a final bonus feature of this app.
To connect to OpenAI, follow these steps in the Retool documentation.
Once connected, Retool has a simple UI for writing prompts for GPT.
To present the response in an editable format, we have pulled in a modal, set the button to hidden, and triggered it to open when the Smart Query runs.
The modal contains a rich text editor, for which a second event handler populates the value:
For a final UI touch, we’ve added a ‘Copy to clipboard’ button so that the user can copy to their email.
For an improved experience, you can even connect to your SendGrid account or via Retool Email (in beta), and send an email from the app itself.
To finish off this app, we’ve created a side panel that links to the other apps in our infrastructure. For more tips on Sidebar UX/UI, head to our Sidebar Layouts guide. We also added some components to show progress as BDRs work through their leads.
We’ve also added a ‘Feedback’ button, which opens a modal to send a ticket to Asana or your own ticketing platform like Zendesk. Head to our Bug Tracking in Asana tutorial for more tips on creating an efficient bug-reporting form.
And here is the final product!
Here are some more capabilities in SQL that you can't do in SOQL:
In summary, SQL provides more flexibility and features for querying and manipulating data, while SOQL is designed specifically for Salesforce and is more limited in scope.
Interested in trying out Sequin to build app for your Sales team? As internal tool specialists, we can advise on the best resources and products to build you bespoke internal tools for your teams. Get in touch today and tell us about your project!
Bold Tech helped us tremendously on a recent app development project by providing expert Retool resources who could plug into our project and make an immediate impact. I highly recommend Joey and Bold Tech as the go-to Retool resource.
Joey and his team helped Vial build our internal tools. Bold Tech was great to work with and were consistently thoughtful, communicative and had a high quality bar for their work.
We build internal tools, fast.
