Zoho CRM Connector

Read all rows    [Read more...]

SELECT * from Accounts

Update Owner of the record(s) - Account, Contacts, Deals, Leads (Update Lookup field)    [Read more...]

This examples shows how to update a lookup type fields (e.g. Account, Contact, Owner) for any module. Lookup fields are jsonobject type of fields which has id, name and sometimes email (only for Owner). This example shows how to update using unique field (E.g. email, id or name). Below example shows how to set Owner of the Account (e.g.) 1558554000137221573. We will set owner to bob-the-salesman@abc.com (or you can set by id)

UPDATE Accounts 
SET Owner='{email: "bob-the-salesman@abc.com"}'
--SET Owner='{id: "1558554000186378001"}' --you can also use Id
Where Id='1558554000137221573'

Update Account or Contact (Lookup field) by Name or Id for Deals    [Read more...]

This examples shows how to update Account and Contact field on Deals module. We used name for account and id for contact just to show how unique field can be used.

UPDATE Deals 
SET Account_Name='{"name": "Company ABCD"}',    --by name or id 
    Contact_Name='{"id": "1558554000186378001"}'  --by id 
Where Id='1558554000137221573'

Create a Deal with Lookup fields (e.g. set Account, Contact, Owner etc by Id or Name)    [Read more...]

This examples shows how to create a new record with Lookup field(s) e.g account, contact or owner.

INSERT INTO Deals(
	Deal_name,
	Amount,
	Lead_Source,
	Account_Name,
	Contact_Name,
	Owner
	)
VALUES
   (
   'My Test Deal Creatyed on <<FUN_NOW>>', --deal name
   1000.50, --amount
   'Cold Call', --lead source
   '{name:"ZappySys"}', --account name or use id '{id:"1558554000030180013"}'
   '{id:"1558554000089352998"}' --contact id 
   '{id:"1558554000089352912"}' --owner id or use email {email: "bob-the-salesman@abc.com"}
   )

Read single row by ID    [Read more...]

SELECT * from Accounts Where Id=1558554000105110008

Read reacord(s) modified after certain date    [Read more...]

SELECT * from Accounts WITH(ModifiedSince = '2020-01-07T00:00:00')

Read using COQL (Zoho Serverside Query Language)    [Read more...]

You can write server side query so you dont have to read full data on client side. For COQL LIMIT clause must be 200 or less. For more information check this URL https://zappysys.com/links?url=https://www.zoho.com/crm/developer/docs/api/COQL-Overview.html

SELECT * from get_module_data_coql WITH(sql_query= 'select Last_Name, First_Name, Company from Leads where Company like ''Test'' limit 200')

Update table for specific record    [Read more...]

Update Leads SET Designation='VP Sales', Company='Test' Where id=1558554000012181009

Update table for specific record (older version)    [Read more...]

Update Leads SET id='1558554000012181009' /* id must be supplied */, Designation='VP Sales', Company='Test'

Get accounts modified after certain date    [Read more...]

SELECT * from Accounts Where Account_Name LIKE 'Test%' WITH(ModifiedSince = '2020-01-07T00:00:00')

Search accounts by specific field (server side filter)    [Read more...]

You can use criteria listed here https://www.zoho.com/crm/developer/docs/api/v2/search-records.html

SELECT * FROM search_Accounts WITH(criteria='Account_Name:starts_with:test')

Create a new record    [Read more...]

INSERT INTO Accounts(Account_Name, Phone) VALUES('Company ABC','111-567-8888')

Create a new account record (with show output on / off)    [Read more...]

INSERT INTO Accounts(Account_Name, Phone) VALUES('Company ABC','111-567-8888') WITH(Output=1)

Delete single record by Id    [Read more...]

Delete exising record by Id (single row). You can supply upto 100 comma seperated Ids

DELETE FROM Accounts WHERE id=11111111111

Delete multiple records by Ids    [Read more...]

This example shows how to delete Account Records by multiple Ids. You can supply upto 100 comma seperated Ids

DELETE FROM Accounts WITH(Id='11111,22222,33333')

Upsert account record (Update or Insert - based on unique field(s) for module)    [Read more...]

UPSERT INTO Accounts(Account_Name, Phone) VALUES('Company ABC','111-567-8888') WITH(Output=1)

Upsert lead record (Update or Insert - based on unique field(s) for module)    [Read more...]

UPSERT INTO Leads(Last_Name, Email) VALUES('Patel','zpatel@abc.com') WITH(Output=1)

Create new account(s) in BULK (read / write from external source)    [Read more...]

This examples shows how to use SOURCE clause to read data from MS SQL Server (or other external system) and send data to Zoho using Bulk API

INSERT INTO Accounts
SOURCE(
  'MSSQL' --ODBC or OLEDB
  ,'Data Source=localhost;Initial Catalog=Test;Integrated Security=true'
  ,'select ''Test Account-A'' as Account_Name,''111-111-1111'' as Phone
    UNION
    select ''Test Account-B'' as Account_Name,''222-222-2222'' as Phone
   '
)

UPSERT (Update or insert) account(s) in BULK (read / write from external source)    [Read more...]

This examples shows how to use SOURCE clause to read data from MS SQL Server (or other external system) and send data to Zoho using Bulk API. Record uniqueness is checked based on Unique field setup for module (i.e. email, account name, phone etc)

UPSERT INTO Accounts
SOURCE(
  'MSSQL' --ODBC or OLEDB
  ,'Data Source=localhost;Initial Catalog=Test;Integrated Security=true'
  ,'select ''Test Account-A'' as Account_Name,''111-111-1111'' as Phone
    UNION
    select ''Test Account-B'' as Account_Name,''222-222-2222'' as Phone
   '
)

Update Tag(s) for exising records (add or overwrite)    [Read more...]

SELECT * FROM post_tags
WITH(
	  module='Accounts'
	, ids='1558554000105151002'
	, tag_names='mytag1,mytag2'
  , over_write='true'
)