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:
These include:
- High cost per user, and no option for ‘lite’ users
- Salesforce-specific languages and platforms like Apex and Lightning are unfamiliar to most engineers and usually require a specialist to build anything custom
- Limited querying ability with their bespoke query language, Salesforce Object Querying Language (SOQL)
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.
Why build a custom app on Retool instead?
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:
- avoid onboarding and paying for more unnecessary Salesforce users
- control exactly how much data these teams can see
- and integrate your other resources and data to create even more powerful apps
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.
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.
Here’s our final app:
Sales app dashboard built in Retool using Salesforce and Sequin for data integration.
Let's dig in.
Seamless data connection using Sequin
Why build a Salesforce app using SQL instead of SOQL?
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.
Key limitations of using SOQL include:
- No exposed database schema: Using the API connection to Salesforce doesn’t allow the use of the SELECT * command, meaning that you need to specify the exact fields you wish to pull. To find these field names you either need to perform a CRUD retrieve of a specific object, or continuously switch between the Object Manager and your query, something that can significantly slow and complicate development.
- The ability to JOIN and GROUP BY is limited. Most advanced functionality like this is not possible in SOQL
- Limited sub-select joins. This is a problem in cases that involve multiple objects, custom objects, and different data types.
- SOQL is rate-limited, as it needs to go through the Salesforce API, and also has limited throughput: you can only get 200 records at a time.
- No update or delete functionality. (Instead, you need to use an API PATCH request.)
- It can also be harder to debug SOQL errors, which tend to return a cryptic message which is less familiar to most developers than SQL or your own ORM failure messages.
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.
What does Sequin do and how does it help?
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:
- Rate limits
- API queries and pagination
- Background jobs
- Cache invalidation
- Out-of-sync data
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.
Build a Salesforce app without Lightning, or SOQL
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 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:
Connect your Salesforce data to Retool using Sequin
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.
Build the UI: Displaying and filtering data in the table component
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.
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.
Adding a form to quickly research and enrich 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.
Update data in Salesforce using Sequin proxy
Finally, to update the details in the backend, we’ve added an update query.
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.
Bonus feature: Generate a sales pitch with AI
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.
Final UX/UI touches
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!
An example of a robust sales app built in Retool using Salesforce and Sequin.
More notes on SQL vs SOQL
Here are some more capabilities in SQL that you can't do in SOQL:
- Joins: SQL allows joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN) to combine data from multiple tables based on common columns. In SOQL, you can only query parent-to-child and child-to-parent relationships, but you cannot perform a true join operation.
- Group By extensions: SQL supports GROUP BY clauses with aggregate functions like COUNT, SUM, AVG, MIN, and MAX. SQL also includes extensions like ROLLUP, CUBE, and GROUPING SETS for advanced grouping and aggregation. SOQL supports basic GROUP BY and some aggregate functions, but it does not support advanced GROUP BY extensions.
- Subqueries: SQL supports subqueries in the SELECT, FROM, and WHERE clauses, allowing complex data extraction and filtering. SOQL supports limited subqueries for parent-to-child relationships within the FROM clause, but not in other clauses.
- UPDATE and DELETE: SQL enables you to update and delete records in the database using the UPDATE and DELETE statements. In SOQL, you can only query records; you cannot update or delete them directly through the query language.
- UNION and UNION ALL: SQL can combine the result of two or more SELECT queries using UNION and UNION ALL operators. SOQL does not support these operators.
- Wildcards: SQL supports wildcard characters like '%' and '_' to perform pattern matching in the search. In SOQL, the LIKE keyword supports only the '%' wildcard character for matching.
- Arithmetic and String operations: SQL supports arithmetic operations in queries (+, -, *, /) and allows string concatenation using the CONCAT function or operators like '||'. SOQL does not support arithmetic operations or string concatenation in queries.
- Temporary tables and table variables: SQL allows creating and using temporary tables and table variables to store intermediate data or to simplify complex queries. SOQL does not support temporary tables or table variables.
- Stored Procedures and User-Defined Functions: SQL allows creating stored procedures and user-defined functions that can be executed in the database. In SOQL, this functionality is not available.
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.
