Custom Where SQL Conditions

A word of warning up front. Custom where conditions are an advanced feature and potentially dangerous to your installation if not used properly. So let’s learn how to use them!

What are they

In Filters and in Automation Rules, at the very bottom of the list of conditions you can select from is the Custom “where” SQL Clause. This condition lets you write your own SQL against the HelpSpot database. Why would you want to do this? Well, there’s a number of reasons. You may want to create a filter that lists requests based on a condition HelpSpot doesn’t natively support for example. That’s the most common case, but there are also other reasons. Sometimes you may want to filter based on data in another non-HelpSpot database table for example. The custom where can also always be combined with other conditions and it usually is combined with other conditions. This lets you limit the scope of your results.

How to Write Your Custom SQL

The SQL you write in your custom where clause is integrated into the overall SQL query used when filtering requests. This query uses a FROM of HS_Request. So, you can directly work with that table if you like. For example, you could limit the filter to only showing you requests where the customer’s first name is Ian.

sFirstName = "Ian"

This works great, the only thing is most of the columns you’d want to query in this way are already available as their own conditions in the condition list. So it’s much better to simply use the customer first name condition rather than writing your own SQL to do the same thing. What you’ll more often be interested in doing is limiting the results of the filter based on an additional query. This is usually called a sub-select in SQL terms. For example, maybe we want to show all requests where our agents used a particular response to reply. HelpSpot tracks all response usages for the Response Usage report in the reports tab. Normally this data isn’t exposed as a filter condition, but with a custom where clause we can use this data. This query will show us all requests that use response number 74 (you can get the response ID from the Manage Responses page).

xRequest IN (
SELECT xRequest 
FROM HS_Stats_Responses 
WHERE HS_Stats_Responses.xRequest = xRequest 
AND HS_Stats_Responses.xResponse = 74)

The above is EXACTLY what you’d enter in the customer where clause filter condition. What it’s doing is running a sub-select on the HS_Stats_Responses table to find all the times we used response 74. It then returns all those xRequest IDs to the main filter query which limits the results to those (that’s what the xRequest IN part is all about). Here’s another example. Currently there’s no way to find all the requests that Don’t have a reporting tag added. This custom where will let us do that.

xRequest NOT IN (
SELECT xRequest 
FROM HS_Request_ReportingTags 
WHERE HS_Request_ReportingTags.xRequest = xRequest)

Connecting to Non-HelpSpot Tables

Some customers store other data alongside HelpSpot’s own data in the database. Using a custom where clause you could include that date in your filter queries. For example, if you have a crminfo table which holds customer data imported from your CRM tool we can access that. Below we’ll limit the results to only requests from customers who have a contract according to our crminfo table.

sEmail IN (
SELECT crminfo.email 
FROM crminfo 
WHERE crminfo.has_contract = 1)

How To See The Full Filter SQL Query

It can be useful to see the full context of the WHERE clause you’ve created. When you’re in the Create Filter screen, after adding your where clause, run the filter as a test. In the result set you’ll see a button that will popup the fully generated filter query. This makes it easy to see what went wrong or how you may want to optimize the query further.

A Word of Caution

With great power comes great responsibility! HelpSpot provides this very unique feature, that few if any other help desk software tools can. Enjoy it, but use it responsibly 🙂 and always remember to:

• Only use a custom where when there’s no built in conditional option for the same data.
• Keep your sub-selects as simple as possible to avoid hurting your system performance.
• From time to time check your Filter Performance screen (Admin->System->Filter Management) to make sure your filters are performing well.