For this tutorial in video form, click here
In this article, we’re going to show you how to use Zoho’s COQL query API, allowing you to more efficiently search for records in your CRM.
The Process
First, we need to set up a connection. In your CRM, click setup on the top right. In Developer Space, click “Connections”.
You’ll see a list of your current connections. Click “Add Connection+” on the top right.
Here, click Zoho OAuth.
We’ll make our name and link name “Zoho CRM COQL”, then we’ll scroll down and add the following scopes. Now you can click “Create and Connect”.
From here, it’ll ask you to grant permissions and then show you some sample code. Copy the sample code.
Now go to “functions” on the bottom left under developer space.
Click on it, and then click “+ New Function” on the top right. We’ve named outs coql_test and made it a standalone.
Setting Up Your Function
Now, you can paste your code and set up a query.
We’ll need to know the API names of the fields we want to use in the query. Navigate to Setup > Developer Space > APIs > API names.
In this section, we’ll get the API name of the desired module.
We’ll also find the API names of the fields. For this example we’ll use Date, Created_By and Client_Reporting. Our function is now looking something like this:
Now we’ll add a WHERE clause.
For the URL, we’ll have a look at what it has in the COQL docs:
Our final product is this:
Here you can see the result:
As you can see, getting all this data was very fast. It means that you can fetch more than 200 records if you need to. Check out our example of a function that got even more (just look at that scrollbar!)
Compared to search records, you have a lot more filtering options. Here’s another example:
This is advantageous because we’re looking at specific localities that contain “A”, which can’t be done in search records (you can only do “equals” or “starts with”). You can think of this like the “ctrl + f” of your records which is super convenient. We have quite a few operators available:
There’s a lot to play with here. For one, there’s the null options, and “in” for a list of possible values. This follows a similar pattern for lookups. For date, it has greater than/less than or equal to. This is something you can’t find in search records.
Concluding Notes
As you can see, there are a lot of advtantages to COQL within a custom function. It’s easier, and provides a lot more data and lots of choices for searching. Another key advantage is the ability to do SQL joins. What’s an SQL join? Essentially, it lets you connect data from multiple modules. Here is an example where we retrieve the Accountant field from the Business Name lookup field:
