Reference

Table [Dynamic Table]

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_[dynamic_endpoint_name]
INSERT post_[dynamic_endpoint_name]
UPDATE put_[dynamic_endpoint_name]
UPSERT upsert_[dynamic_endpoint_name]
DELETE delete_[dynamic_endpoint_name]
LOOKUP search_[dynamic_endpoint_name]

Examples

SSIS

Use Zoho CRM Connector in API Source component to read data or in API Destination component to read/write data:

Read from [Dynamic Table] table using API Source

API Source - Zoho CRM
Zoho CRM Connector can be used to integrate Zoho CRM API in your App / BI Tools. You can exchange data on Accounts, Leads, Contacts and many other modules.
Zoho CRM
[Dynamic Table]
Optional Parameters
Module [$parent.api_name$]
Fetch records modified after (local time format: yyyy-MM-ddTHH:mm:ss) 1900-12-31T00:00:00
Custom View ID
DisableChildEndPoint True
FieldsValue id
Fields (Only for V2 API)
sort_by
sort_order
territory_id
include_child
converted
PagingMode ByUrlParameter
NextUrlAttributeOrExpr $.info.next_page_token
NextUrlSuffix page_token=<%nextlink%>
SSIS API Source - Read from table or endpoint

Read/write to [Dynamic Table] table using API Destination

API Destination - Zoho CRM
Zoho CRM Connector can be used to integrate Zoho CRM API in your App / BI Tools. You can exchange data on Accounts, Leads, Contacts and many other modules.
Zoho CRM
[Dynamic Table]
Select
Optional Parameters
Module [$parent.api_name$]
Fetch records modified after (local time format: yyyy-MM-ddTHH:mm:ss) 1900-12-31T00:00:00
Custom View ID
DisableChildEndPoint True
FieldsValue id
Fields (Only for V2 API)
sort_by
sort_order
territory_id
include_child
converted
PagingMode ByUrlParameter
NextUrlAttributeOrExpr $.info.next_page_token
NextUrlSuffix page_token=<%nextlink%>
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read all rows 

SELECT * from Accounts

Update Owner of the record(s) - Account, Contacts, Deals, Leads (Update Lookup field)

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

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)

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 

SELECT * from Accounts Where Id=1558554000105110008

Read reacord(s) modified after certain date 

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

Update table for specific record 

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

Update table for specific record (older version) 

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

Get accounts modified after certain date 

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

Search accounts by specific field (server side filter)

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 

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

Create a new account record (with show output on / off) 

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

Delete single record by Id

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

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) 

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) 

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)

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)

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
   '
)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read all rows 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Update Owner of the record(s) - Account, Contacts, Deals, Leads (Update Lookup field)

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) 

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

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Update Account or Contact (Lookup field) by Name or Id for Deals

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. 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Deals 
SET Account_Name=''{"name": "Company ABCD"}'',    --by name or id 
    Contact_Name=''{"id": "1558554000186378001"}''  --by id 
Where Id=''1558554000137221573''';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

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

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

DECLARE @MyQuery NVARCHAR(MAX) = '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"}
   )';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Read single row by ID 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts Where Id=1558554000105110008';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Read reacord(s) modified after certain date 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts WITH(ModifiedSince = ''2020-01-07T00:00:00'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Update table for specific record 

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Leads SET Designation=''VP Sales'', Company=''Test'' Where id=1558554000012181009';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Update table for specific record (older version) 

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Leads SET id=''1558554000012181009'' /* id must be supplied */, Designation=''VP Sales'', Company=''Test''';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Get accounts modified after certain date 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts Where Account_Name LIKE ''Test%'' WITH(ModifiedSince = ''2020-01-07T00:00:00'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Search accounts by specific field (server side filter)

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

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM search_Accounts WITH(criteria=''Account_Name:starts_with:test'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Create a new record 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Accounts(Account_Name, Phone) VALUES(''Company ABC'',''111-567-8888'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Create a new account record (with show output on / off) 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Accounts(Account_Name, Phone) VALUES(''Company ABC'',''111-567-8888'') WITH(Output=1)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Delete single record by Id

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

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Accounts WHERE id=11111111111';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Delete multiple records by Ids

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

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Accounts WITH(Id=''11111,22222,33333'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Upsert account record (Update or Insert - based on unique field(s) for module) 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO Accounts(Account_Name, Phone) VALUES(''Company ABC'',''111-567-8888'') WITH(Output=1)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Upsert lead record (Update or Insert - based on unique field(s) for module) 

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

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Create new account(s) in BULK (read / write from external source)

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 

DECLARE @MyQuery NVARCHAR(MAX) = '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
   ''
)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

UPSERT (Update or insert) account(s) in BULK (read / write from external source)

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) 

DECLARE @MyQuery NVARCHAR(MAX) = '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
   ''
)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];