Kaizen 81 - COQL API - Part II

Kaizen 81 - COQL API - Part II

Hello everyone! 
Welcome to another week of Kaizen! 
In continuation to last week's post on COQL API, we will discuss the rest of the field types and their supported operators with detailed examples in this post. 

Supported field types and Operators

Please refer to the COQL API - Part I Kaizen post for the first three field types and their supported operators.

4) Boolean

The supported operator for Boolean field is =. You should provide the value as either true or false.

Sample Query

{
 "select_query" : "select Product_Name, Product_Code from Products where Product_Active = 'false' limit 2"
}

This sample query selects the Product_Name and Product_Code fields from the Products module where Product_Active is false, and limits the result to only 2 records.
Sample Response:
{
    "data": [
        {
            "Product_Code": "SR-001",
            "Product_Name": "Sigma",
            "id": "4876876000001036109"
        },
        {
            "Product_Code": "SR-003",
            "Product_Name": "Alpha",
            "id": "4876876000001039006"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

what_Id support in COQL

=, !=, in, not in, is null, is not null are the supported operators. what_Id support is extended only to Tasks, Calls and Events. The what_Id field could be a lead or a contact.

Sample Query

{
    "select_query": "select 'What_Id->Leads.Last_Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))"
}

This sample query queries for the last name of leads associated with tasks where the lead ID is either '4876876000000900134' or '4876678000000900987', and the lead's industry is not 'ERP'.
Sample Response:
{
    "data": [
        {
            "What_Id->Leads.Last_Name": "Perry",
            "id": "4876876000001103044"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Roles and Profiles support in COQL

COQL supports roles and profiles lookup fields, allowing users to retrieve the owner's profile details such as ID, name etc, and the owner's role details using COQL queries. The supported comparators are =, !=, in, not in, is null, is not null.

Sample Query

{
    "select_query": "select Owner.role.id,Owner.role.name,Owner.role.reporting_to, Owner.role.share_data_with_peers, Owner.role.description, Owner.profile.id from Leads where Last_Name is not null LIMIT 1"
}

This query fetches the Profile details and Role details of the owner of one record from the Leads module. 

Sample Response:
{
    "data": [
        {
            "Owner.role.reporting_to": null,
            "Owner.profile.id": "4876876000000026011",
            "Owner.role.name": "CEO",
            "Owner.role.description": "crm.security.role.admin.desc",
            "Owner.role.share_data_with_peers": true,
            "Owner.role.id": "4876876000000026005",
            "id": "4876876000000333403"
        }
    ],
    "info": {
        "count": 1,
        "more_records": true
    }
}

Alias support

Supported operators : =, !=, in, not in, is null, is not null
Alias support in COQL allows users to simplify complex SELECT field names by giving them custom names or aliases. Using aliases in the SELECT column and ORDER BY class can make column names easier to understand and remember. For instance,  What_Id->Leads.Last_Name from our previous example can be given an alias in the SELECT query as follows.

Sample Query

{
    "select_query": "select 'What_Id->Leads.Last_Name' AS 'Lead Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))"
}

Sample Response:
{
    "data": [
        {
            "id": "4876876000001103044",
            "Lead Name": "Perry"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

If you compare this response with the sample response in the what_Id section above, you can see that the key 'What_Id->Leads.Last_Name'  is replaced with the Alias 'Lead Name'  in here. 

Aggregate Function Support in COQL

Aggregate functions in COQL allow you to perform calculations on groups of records in a module. The available aggregate functions include SUM, MIN, MAX, AVG, and COUNT.

1. SUM

Use this function to sum up the values of an aggregate field in a module. 

Sample Query 1

{
    "select_query":"select SUM(Grand_Total) from Quotes WHERE Quote_Stage = 'Draft' "
}

This Query uses the SUM function to calculate the total value of the Grand_Total field for all quotes in the Draft stage in the Quotes module.
Sample Response:
{
    "data": [
        {
            "SUM(Grand_Total)": 1073660
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Sample Query 2

{
    "select_query": "select SUM(Grand_Total), Account_Name.Account_Name AS 'Account Name', Billing_Country from Invoices where ((Status != 'Paid') and ((Billing_Country ='USA') OR (Billing_Country = 'Canada'))) GROUP BY Account_Name.Account_Name, Billing_Country ORDER BY 'Account Name' ASC"
}

This query retrieves the total SUM of the Grand_Total field, along with the Account_Name and Billing_Country from the Invoices module for those records whose Status field is not equal to 'Paid' and the Billing_Country field is either 'USA' or 'Canada'. The results are grouped by the Account_Name and Billing_Country fields and ordered in ascending order based on the 'Account Name' field.

Note that the 'Account Name' field is specified using the AS keyword to provide an alias for the Account_Name.Account_Name field. We have used the alias for the ORDER BY clause.
Sample Response:
{
    "data": [
        {
            "Account Name": "SR Systems",
            "Billing_Country": "USA",
            "SUM(Grand_Total)": 2430000
        },
        {
            "Account Name": "SR Systems",
            "Billing_Country": "Canada",
            "SUM(Grand_Total)": 60000
        },
        {
            "Account Name": "Zylker systems",
            "Billing_Country": "Canada",
            "SUM(Grand_Total)": 378000
        }
    ],
    "info": {
        "count": 3,
        "more_records": false
    }
}

The response JSON contains the SUM of the Grand_Total fields, Account Name and Billing_Country for each unique combination of Account Name and Billing Country fields that satisfies the filter criteria specified in the query. 

2. MAX

Use this aggregate function to find the largest value of a field in a module. 

Sample Query

{
    "select_query": "select MAX(Amount) AS 'Maximum Deal Amount', Account_Name.Account_Name from Deals where ((Stage = 'Closed Won') AND  (Type = 'Existing Business')) Group by Account_Name.Account_Name"
}

This Query selects the maximum Deal Amount for each account from the Deals module where the stage is Closed Won and the type is Existing Business.
Sample Response:
{
    "data": [
        {
            "Maximum Deal Amount": 6750000,
            "Account_Name.Account_Name": "Benton"
        },
        {
            "Maximum Deal Amount": null,
            "Account_Name.Account_Name": "King"
        },
        {
            "Maximum Deal Amount": 945000,
            "Account_Name.Account_Name": "Morlong Associates"
        }
    ],
    "info": {
        "count": 3,
        "more_records": false
    }
}

3. MIN

Use this aggregate function to find the smallest value of a field in a module. For example, you can use this function to find the Lowest Annual Revenue in the Leads module.

Sample Query: 1:

{
        "select_query": "select MIN(Annual_Revenue) from Leads where Annual_Revenue is not null"
}

Sample Response:
{
    "data": [
        {
            "MIN(Annual_Revenue)": 348.3
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

4. AVG

Use this aggregate function to find the average value of a field in a module. 

Sample Query

{
    "select_query": "SELECT AVG(Amount) AS 'Average Deal Amount' FROM Deals WHERE Type = 'New Business'"
}

This query calculates the average deal amount for all new business deals in the "Deals" module. 
Sample Response:
{
    "data": [
        {
            "Average Deal Amount": 877366.8359999999
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

5. COUNT

Use this aggregate function to count the number of records in a module satisfying a certain criteria. You can use this aggregate function only for number, lookup, and picklist fields.

Sample Query:

{
    "select_query": "SELECT COUNT(Product_Category) FROM Products WHERE Product_Active = 'false'"
}
This sample query queries for the total number of inactive products in the Product module. Please be aware that if there are any products with no value for the product category field, they will not be included in the count.

Important points to remember

  • COQL keywords are not case-sensitive.
  • To prevent ambiguity, the criteria listed in the WHERE clause must be properly enclosed in brackets. If WHERE has more than two criteria such as A, B, and C, use them as either of the following:
    • (A and (B and C))
    • ((A and B) and C)
    • A and (B and C)
    • (A and B) and C.
  • COQL supports only SELECT queries with WHERE, FROM, ORDER BY, LIMIT, and OFFSET clauses and aggregate functions SUM(), MAX(), MIN(), AVG(), and COUNT().
  • The default sort order is ascending based on record ID if ORDER BY is not specified.
  • Use the following format for :
    • Date : YYYY-MM-DD
    • DateTime : YYYY-MM-DDTHH:mm:ss+/-HH:mm
  • The default value for LIMIT is 200 and OFFSET is 0.
  • A maximum of 10,000 records can be fetched using the API.
  • Multi-line fields cannot be used in criteria.
  • COQL doesn't support consent_lookup, subform, profileimage, multiselectlookup, fileupload datatypes.
  • A maximum of 25 criteria can be included in the WHERE clause.
  • A maximum of 50 field API names can be used in the SELECT column of the query.

     Data Type
     Supported Operators
     text
     =, !=, like, not like, in, not in, is null, is not null
     picklist
     =, !=, like, not like, in, not in, is null, is not null
     ownerlookup
     =, !=, in, not in, is null, is not null
     lookup
     =, !=, in, not in, is null, is not null
     date
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     datetime
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     currency
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     boolean
     =
     email
     =, !=, like, not like, in, not in, is null, is not null
     phone
     =, !=, like, not like, in, not in, is null, is not null
     website
     =, !=, like, not like, in, not in, is null, is not null
     double
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     bigint
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     integer
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     formula
     If the return type is:
          Decimal/Currency/Date/Datetime : =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
          String : =, !=, like, not like, in, not in, is null, is not null
          Boolean : =
     autonumber
     =, !=, like, not like, in, not in, is null, is not null
We hope you found this post useful. We will meet you next week with another interesting topic.

If you have any questions or feedback, please let us know in the comments below, or write to us at support@zohocrm.com. We would love to hear from you! 


Additional Reading:








    • Recent Topics

    • 【Zoho CRM】ウィザード機能のアップデート

      ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 今回は「Zoho CRM アップデート情報」の中からウィザード機能のアップデート をご紹介します。 今回のアップデートにより、ウィザードの「条件処理ルール」機能が改良され、以前の入力画面の項目を基に条件を設定できるようになりました。 これまでは条件設定が1つの画面内に限られていたため、画面間で情報を関連付ける際に、余分な項目を作成する必要がありました。 今回のアップデートにより、前の画面の情報を利用して次の画面に条件を適用できるため、設定がより効率的になります。

    • Partner with HDFC And Sbi Bank.

      Hdfc and sbi both are very popular bank if zoho books become partner with this banks then many of the zoho books users will benefit premium features of partnered banks.

    • Send a formatted CV to multiple contacts at multiple clients - and have it associate with both candidate client

      I would like to send a formatted CV of a candidate to multiple contacts at multiple clients and have it kept on the record of all three; client, contact and candidate. I understand how to send an email to multiple contacts at multiple clients - and indeed I am able to attach the CV of a candidate through 'browse'. Obviously this does not associate it with the candidate though, only the contact it is sent to. Is there any way to do this? Thanks in advance.

    • Set organization level access to Knowledge Base and manage duplicate article permalinks (with date appended to them)

      Hello everyone, We have introduced two important updates in the Knowledge Base module: Permissions - A new permission called "Admin access" has been introduced under module permission. Permalink alert - Users will be alerted when an article with the same

    • Language Field on Contact Person-level

      Dear at Zoho Books, would it be possible for you to have a Field for 'Language' for the Contact Persons under a Company. In CRM and Bigin we could create a Custom Field (Dropdown) for this effect but without any present in Zoho Books we could never sync

    • Zoho Books email notifications

      Hi, We are currently implementing Zoho books for our organisation. I have 2 questions. 1. Are we able to embed HTML into our emails to make them look as part of our business ? Branding is very big issue for us and i would like to make sure that all branding efforts are carried across into the notification emails.. 2. Is there a way to preview the notifications that are sent out to our customers so we can make sure the look and feel is as we require ? thanks

    • Plug Samples #12: Verify Customer Mobile Numbers with OTP to Avoid Spam Entries

      Hi Everyone! Verifying customer information is critical in business to filter out junk data and spammers. Whether it’s lead generation, appointment booking, or any other operation, ensuring quality data is essential for driving meaningful outcomes. A

    • All notes disappeared

      I've been using the notebook app for over five years on my phone without being logged into an account. A few days ago I opened the app and all my notes had disappeared. Since then I tried restarting my phone, updating the app and logging into my account,

    • Cost Center accounting

      Hello all. We are looking at switching from MYOB to Zoho books. Love all the features in books - esp. CRM integration, but the big gap for us is the lack of cost-center accounting feature. Are there firm plans to develop this feature in Zoho Books? If

    • Impuesto automatico en cotizaciones

      Buen dia Mi pregunta es como se puede poner alguna operacion para que las cotizaciones me salgan automaticamente con impuestos ya que uno al cargarla al final tienen que añadir el porcentaje de impuesto Saludos

    • Formatting of Gantt Chart for PDF export

      Hi all, There really needs to be some improvements to the presentation of exported PDF Gantt Charts. An exported format is the way that most people on a project (other than the PM directly) will consume this data. The current PDF export options are lacking a lot of control. - I'd like to firstly have control over the export size of the PDFs (A4, A3 etc), Currently it seems arbitrary - very large aspect ratio print sizes. - Next i'd like to be able to specify the print date range, and scale (show

    • How to Iterate a Function in Zoho Desk Workflow with Delay Between Calls?

      Hi everyone, I’m working on a function in Zoho Desk that searches for a specific ticket record. If the ticket is not found, I need to retry the search multiple times with a delay between each attempt until the ticket is located or a maximum number of

    • タスクを問い合わせテンプレートに追加

      タスクを問い合わせテンプレートに自動で追加されるようにしたいです。どうしたらいいですか

    • Placeholder for Agent Signature in Email Templates

      Dear Zoho Team, I hope this message finds you well. We currently face a limitation when designing email templates in Zoho Desk. While we can create email templates and include a footer at the end, the agent signature is always appended by default at the

    • Custom Profile Agents Unable to Update Mandatory Phone Number in tickets under Zoho Desk

      Hi, While working in the Ticket module in Zoho Desk, agents with a custom profile are unable to update the Phone Number field (which is mandatory) under Account Information. The agents need to enter values such as "NA" or "Nil" in the field to close the

    • Updating existing values in a list

      Is it possible to update an existing value in a list? For example if I wanted to append text to an existing string value that I had previously added to a list is that possible? I'm able to extract the existing value (get() function) and append the additional text, but can't find a way to add the new value back to the list in the same index location. One work around I found was to delete the existing list entry and then add the new value, however this changes the index location to the last entry in

    • Host not reachable

      Good morning. I have four mail accounts and they have been working well for years. Now, suddenly, I am unable to send any e-mails from any of them. I allways have this pop-up: Thank you. Regards. Luis Fernández

    • Zoho Marketing Campaign

      I want a details report of marketing API . which API i can use to get a full flexed detail of email campaign , sms , social media ,and all other campaigns ?

    • Zoho Marketing Automation APIs

      When I want to create New lead in marketing automation , I want to add First Name and Last Name as well along with Email but there is no option like this in API . Can you please give me the API which will fulfill my condition ?

    • Adding Folders in Android App

      Is it possible to create a new email folder within the Zoho Mail Android app?  Or can this only be done from the desktop version of Zoho Mail? Cheers!

    • Collaborative editing of spreadsheets by multiple users simultaneously - Is it really possible?

      I work in a SharePoint 2010/SharePoint Server 2010 environment and we use MS Office 2007(Excel 2007) . I have been trying to create a MS Office document which will allow multiple users to work on a spreadsheet simultaneously. For that I have: 1) Created a excel workbook and published as a workspace as suggested by Andrea Kalli in her link: http://www.wonderhowto.com/how-to-collaborate-files-between-sharepoint-and-office-2007-182172/ 2) Set up and saved the Excel Workbook as "Shared" and made it available

    • Ability for customer to give feedback after receipt of an order

      Is there any way we can receive feedback from customers regarding their order after delivery (other than just an email, obviuosly)? This is not the same as product reviews, as it may concern other points, but would ideally have an inbuilt reference to

    • Zoho Books Sandbox environment

      Hello. Is there a free sandbox environment for the developers using Zoho Books API? I am working on the Zoho Books add-on and currently not ready to buy a premium service - maybe later when my add-on will start to bring money. Right now I just need a

    • Zoho Books | Product Updates | November 2024

      Hello users, We are back with exciting new features and enhancements in Zoho Books. From multi-criteria workflows to writing off customer opening balances, discover the new updates designed to make your bookkeeping swift. Attach XRechnung to Invoices

    • Spell check sucks

      Come on guys, it's 2024 and your spell check is completely retarded. You gotta fix it.

    • How to send certain invoices for Approval based on condition?

      To streamline operations, I need the invoices to go for approval when certain conditions are met. For example, if my staff gives a discount of x amount, it must go for approval. If there is no discount, then the invoice will be approved automatically.

    • Signature image logo not displaying in recipient mail clients

      Whenever I send an email to another person the image that forms part of my logo is not displayed just a broken link to: https://mail.zoho.com/mail/ImageSignature?fileName=Email-log.jpg&accountId=47238000000007001&storeName=NN1:-3205531763309921492 I can see the logo because I've got a zoho account and I'm logged on but others without a zoho account can't see it. How do I fix this? Thanks -Martin

    • Sales Order, what are the statuses under Confirmed and Closed

      Hi, I have to build a workflow in Deluge which should be triggered when Sales Order status is Confirmed or Closed. But these 2 states don't exist when you fetch a sales order. Which of the statuses are considered as Confirmed or Closed ? Here is a list

    • Notifications for calendar meetings

      So we have been happy users for a few months now. This is an important problem we face, since I have forgotten meetings already cause of lack of notifications. The problem: I have notifications (email, popup and notifications) set for my calendar by default.

    • Reusuable Sections & Universal Brand Settings

      Zoho Email Campaign setups take longer than other software I've used because it doesn't have reusable sections or universal brand settings (fonts and colors). These enhancements should be added to help us create our campaigns more easily.

    • Blockchain technology

      Blockchain technology is being used to revolutionize accounting and financial reporting. With blockchain, financial transactions are recorded on a decentralized ledger, making tracking transactions and verifying their accuracy easier. This technology

    • Este domínio já está associado a esta conta

      Fui fazer meu cadastro na zoho e quando digitei meu domínio recebi essa mensagem que meu domínio estava associado a uma conta que eu nem faço idéia de quem seja. Como que faço pra resolver isso? Atenciosamente, Anderson Souza.

    • not able to hit inventory api

      when i hit the api with my account keys and the access is provide by the client

    • 553 Relaying disallowed error

      Hi, I am receiving a "553 Relaying disallowed" error when sending emails to my domain email address which I have configured in Zoho. However, I am able to receive emails from my domain email to my gmail account. The SPF and MX entries are all configured

    • !! URGENT My sent mail goes to spam

      I tested a few times and every time I send mail out it goes the recipients spam box.  Why is it marking my mail as spam? please help me ! thanks

    • Event Time Zone in meeting invites are confusing users

      When sending calendar invites to internal and external users, the first section "Event Time Zone" is confusing people and they are automatically declining events. Can this section please be removed??? It already shows the correct time zone next to the

    • Request For Quotation (RFQ) module

      Hello, Do you have any plans to implement a RFQ module in to ZOHO Inventory? I would like to chose items that I require a price for, select a number of different suppliers to e-mail and have them submit there pricing online. I would then like to see a

    • Constant Sync Errors 🙄

      I'm constantly getting sync error notifications with no actual resolution. Also... I have folders and files with a (!) on the cloud icon, indicating it's not accessible. This has also led to the complete loss of certain folders. Like... the go missing!

    • In Zoho Projects, is there a way to create a folders template under documents that can be used once a project is created?

      We have a specific folder structure that we would like to use that is standard across every project. Instead of having to create this structure every time a project is created, is there a way to create a template for the folders that can be added?

    • javax.mail.authenticationfailedexception 535 authentication failed

      Hi, I am facing 535 authentication failed error when trying to send email from zoho desktop as well as in webmail. Can you suggest to fix this issue,. Regards, Rekha

    • Next Page