How to Fetch records from ZOHO CRM with CRM Object Query Language (COQL)?

Introduction

CRM Object Query Language (COQL) fetches data (records) from Zoho CRM using Field and Module API names. ZOHO is a leading Customer Relationship Management Platform (CRMs) widely used in various industries.

Moreover, working on CRM means accessing and manipulating data in such a way that it can yield maximum profits. Furthermore, ZOHO offers multiple features to customize the org per the user/organization’s needs.

One such feature is custom functions, which perform custom actions and are associated with workflows, program client scripts, etc. Besides, these functions can access data, manipulate it to a large extent, and then update responses in your CRM.

In addition, there are two major ways by which one can access data in functions:

1. ZOHO CRM Integration Tasks
2. Query API (COQL)

This blog post will highlight COQL, get data in Deluge from the ZOHO database using the Query API, popularly known as CRM Object Query Language (COQL), and implement COQL in a Deluge Function. 

What is CRM Object Query Language (COQL)? 

• COQL is a query Language similar to SOQL (Salesforce Object Query Language), and SQL (Structured Query Language) syntax.
• It can also be used in Deluge (Scripting language used in ZOHO Functions) to fetch records and perform desired operations on fetched records.

Some Important Points Related to COQL

• One can use only a SELECT query in combination with WHERE FROM, ORDER BY, LIMIT, and OFFSET clauses.
• All these Keywords are not case-sensitive.
• If ORDER BY is not included in the query, then the system sorts the records in ascending order of record ID by default.
• One can fetch a maximum of 10000 records but with a limit of 200 records per API call.
• By default, the LIMIT values are 200 and OFFSET 0 in a query.
• One can relate two modules of CRM using . (dot) with the help of lookup fields in the modules.

Get Records Using COQL Query in Deluge

Here, we will discuss two examples of COQL in Deluge. In the first example we will fetch Account Records, and in the second example we will fetch Account records related to their respective Contact Records using the . (dot) operator.

• Prerequisites

Firstly, to perform above mentioned operations, we need to create a Connection in ZOHO CRM. This OAuth Connection in ZOHO will allow access to CRM Records.

Steps to Create OAuth Connection

1. Open ZOHO CRM Org.
2. Now open Setup ( click the gear icon on the page’s top-left corner).
   
   
3. Open My Connections and then click on the Create Connection button.
   
   
4. Go to DEVELOPER SPACE and then click Connections.

5. Now in the Default Services tab, select ZOHO OAuth.
   
   
6. After that we have to configure our Connection. Hence we enter the below-mentioned details in the respective fields.

8.  New tab will open now. Click the Connect button.

9.  Page will reload. Then click the Accept button.

10. New Connection will be created successfully, and then your connection details will be visible now(refer screenshot below).

Note- If you edit/revoke the Connection, please reconnect it again to avoid any errors.

• B. Implement COQL IN a Deluge function

I hope you didn’t face any difficulties while creating the OAuth connection; now it’s time to implement COQL in Deluge. Please follow below mentioned steps and don’t forget to implement it on your own after this guided implementation.

Steps to implement COQL in Deluge Function 

Example 1: Fetch Account Records using COQL.

1. Go to DEVELOPER SPACE again and then open Functions.
   
   
2. After that click the New Function button on the left.

3. Next Create New Function form will pop up. 

4. Then enter the following details in respective fields.

Field	Value
Function Name	coql_implementation_1
Display Name	coql_implementation_1
Description	Example 1 COQL implementation
Category	Standalone

5. Click Create, and a new screen will open up. Now we will write Deluge Script/Function.

6. Next copy the following code and get it pasted into the Deluge editor.

query = {“select_query” : “SELECT Account_Name, Account_Type, Industry FROM Accounts WHERE Account_Name != Null LIMIT 5 OFFSET 5”};

response = invokeurl

[

url : “https://www.zohoapis.in/crm/v2/coql”

type: POST

parameters: query.toString()

connection: “zoho_coql”

];

info response;

info ” ———————————————————————-“;

count = 0;

accounts = response.get(“data”).toJSONList();

for each  record in accounts

{

count = count + 1;

info (count + “. Name : ” + record.get(“Account_Name”));

info “Account Type : ” + record.get(“Account_Type”);

info “Industry : ” + record.get(“Industry”);

}

return “”;

7. Click on the Save & Execute button in the top-left corner of the Editor.
8. Then wait for a few seconds, and a Console will appear on the left side of the window showing fetched records in JSON(Object) format.
   • This console will also show the error messages if any.

9. Now you can edit the code and try your hand at it, OR you can go through the second example below first.

Example 2: Fetch Account records related to their respective Contact Records using the.(dot) operator. 

1. Follow Steps 1 to 3 from example one and reach the Create New Function Pop-Up.
2. Then enter the following details in respective fields.

Field	Value
Function Name	coql_implementation_2
Display Name	coql_implementation_2
Description	Example 2 COQL implementation
Category	Standalone

3. Click Create, and the Deluge Editor screen will open.
4. Copy the following code and then get it pasted into the Deluge editor.

query = {“select_query” : “SELECT First_Name, Last_Name, Account_Name, Account_Name.Account_Name FROM Contacts WHERE Last_Name != Null LIMIT 3 };

response = invokeurl

[

url :”https://www.zohoapis.in/crm/v2/coql”

type:POST

parameters:query.toString()

connection:”zoho_coql”

];

info response;

info ” ———————————————————————-“;

count = 0;

accounts = response.get(“data”).toJSONList();

for each  record in accounts

{

count = count + 1;

info count + “. Contact Name : ” + record.get(“First_Name”) + ” ” + record.get(“Last_Name”);

info “Account Id : ” + record.get(“Account_Name”).get(“id”);

info “Account Name : ” + record.get(“Account_Name.Account_Name”);

}

return “”;

5. After that click on the Save & Execute button on the top-left corner of Editor.
6. Next wait for a few seconds, and then a Console appears on the left side of the window showing fetched records in JSON(Object) format.

Key Takeaways

• Returned JSON Object in COQL has two components inside it: data and info.

• data: It is an array that has all the fetched records.
• info: It is an object with two key-value pairs of  

• count: displays record count.
• more_records: if the value is true, then more records can be fetched. 

• This JSON Object can be converted into a List/Map to use this data further in the function.

Conclusion 

To conclude COQL helps users to write their queries to obtain records from Zoho CRM. Besides, it is also known as the Query API and returns records in JSON format. Moreover, it uses field API names instead of column names and module API names in place of table names.

Furthermore, you can filter and fetch records using this API based on specific criteria instead of creating custom views. Finally, since every module’s API names are unique, there is no need to specify the module alias explicitly when building a relationship between two modules. 

Cloud Analogy, one of the top Certified Zoho Consulting companies, will guide you with their business advice on the benefits of omni-channel. 

We offer affordable Zoho CRM Consultancy Services, implementation, integration, customization, and more to grow your business in various ways and act as your trusted Zoho partner. Connect with our competent and certified team at Cloud Analogy and start your project today.

Follow us on our Social Media platforms, YouTube, LinkedIn, Instagram, Facebook and Twitter, to stay updated with the latest technology and trends.

Thank you for reading!