Adding a Connection to Zoho CRM

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Zoho CRM icon is not available, click the Add More icon to download and install the Zoho CRM connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to Zoho CRM

To connect to your own local data via the desktop (non-browser connection), use the CData-supplied embedded OAuth application.

The Sync App is already registered with Zoho CRM as an OAuth application. As such, it will automatically use the embedded application to connect to local data via a Desktop application.

To avoid repeating the OAuth exchange and eliminate the need to manually set the OAuthAccessToken every time you access local data, set InitiateOAuth to GETANDREFRESH.

Note: The Zoho CRM Sync App defaults UseServerSideFiltering to True for higher performance, but this setting might return incorrect results.

To connect to shared data over the network (browser connection), use a custom OAuth application. To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth.

The following subsections describe how to authenticate to Zoho CRM from three common authentication flows:

• Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping. Authenticated via either embedded OAuth or custom OAuth.
• Web: access to data via a shared website. Authenticated via custom OAuth only.
• Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard. Authenticated via embedded OAuth or custom OAuth.

For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that have embedded OAuth credentials, see Creating a Custom OAuth Application.

For a complete list of connection string properties available in Zoho CRM, see Connection.

When the access token expires, the Sync App refreshes it automatically.

Automatic refresh of the OAuth access token:

To have the Sync App automatically refresh the OAuth access token, do the following:

1. The first time you connect to data, set the following connection parameters:
   • InitiateOAuth: REFRESH.
   • OAuthClientId: The client Id in your application settings.
   • OAuthClientSecret: The client secret in your application settings.
   • OAuthAccessToken: The access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation: The path where you want the Sync App to save the OAuth values, which persist across connections.
2. On subsequent data connections, set the following:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAUth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by ) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set the following connection properties:
   • OAuthClientId: The Client Id in your application settings.
   • OAuthClientSecret: The Client Secret in your application settings.

3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

This section details a selection of advanced features of the Zoho CRM Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Zoho CRM and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

CData Sync App models Zoho CRM Modules as relational tables.

Each API Version supported- v2, v2.1 and v4 is assigned it's own data model.

CData Sync App Data Models

CData Sync App models Zoho CRM Modules as relational tables.

Using Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to the Zoho CRM APIs and then processes the rest of the query within the Sync App. The following sections document API limitations and requirements.

Tables

The Sync App connects to Zoho CRM and retrieves the list of Tables and the metadata for the tables by calling the appropriate Web services. Any changes you make to your Zoho CRM account, such as adding a custom module, adding a custom field, or changing the data type of a field, occur immediately when you connect using the Sync App. Tables shows an example, based on the Zoho CRM development environment, of what the entities in your account may look like.

Views

Most Views are defined in static configuration files. Custom Views are not. Instead, custom views are dynamically retrieved by connecting to Zoho CRM, calling the appropriate Web service, and retrieving the list of custom views from the metadata returned. Any changes you make to your Zoho CRM account, such as adding a new custom view, occur immediately when you connect using the Sync App.

To use Custom Views, set the IncludeCustomViews property to true. Its default value is false since custom views introduce an overhead when retrieving the list of tables and views.

Criteria applied in the custom view (either when creating it or when editing it in the UI) will be preserved in CData Zoho CRM Sync App. Nonetheless, further filtering on records is available at any time.

To use Relative Views set the IncludeRelatedLists property to true. Its default value is false since related views introduce an overhead when retrieving the list of tables and views.

EntityId is required to fetch the related module data. This field refers to the parent module Id.

Stored Procedures

Stored Procedures provides additional capabilities in the Zoho CRM API, including working with files.

The Sync App models the data in Zoho CRM as a list of tables in a relational database that can be queried using standard SQL statements.

Zoho CRM Connector for CData Sync Tables

Create, update, delete, and query information regarding accounts.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side. The rest of the filter is executed client-side within the Sync App.

Insert

INSERT INTO Accounts(AccountName) VALUES('my name') 

Required fields: AccountName.

Any field which is not read-only (ReadOnly = false in the table below below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Accounts WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Accounts SET Description = 'Updated from API' WHERE Id = '3152079000000153079'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Delete, and query information regarding activities.

Table-Specific Information

Select

The Sync App uses the Zoho CRM API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the Sync App.

• DueDate supports the '=' operator.
• ClosedTime supports the '=' operator.
• Location supports the '=, LIKE' operators.
• From supports the '=' operator.
• To supports the '=' operator.
• Check-InTime supports the '=' operator.
• Check-InComment supports the '=, LIKE'.
• Check-InSub-Locality supports the '=, LIKE' operators.
• Check-InCity supports the '=, LIKE' operators.
• Check-InState supports the '=, LIKE' operators.
• Check-InCountry supports the '=, LIKE' operators.
• ZipCode supports the '=, LIKE' operators.
• Check-InAddress supports the '=, LIKE' operators.
• CheckedInStatus supports the '=, LIKE' operators.
• CallStartTime supports the '=' operator.
• CallDuration supports the '=, LIKE' operators.
• CallResult supports the '=, LIKE' operators.
• Reminder supports the '=, LIKE' operators.
• CallStatus supports the '=, LIKE' operators.
• Subject supports the '=, LIKE' operators.
• CreatedTime supports the '=' operator.
• ModifiedTime supports the '=' operator.
• ActivityType supports the '=, LIKE' operators.
• Description supports the '=, LIKE' operators.

For example, the following queries are processed server side:

SELECT * FROM Activities WHERE Id = '3152079000000154214'

SELECT * FROM Activities WHERE Subject = 'event'

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Activities WHERE Id = '3152079000000156229'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Delete, and query information regarding attachments.

Table-Specific Information

Select

The Sync App uses the Zoho CRM API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the Sync App.

SELECT * FROM Attachments 

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Attachments WHERE Id = '3152079000000431011'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding calls.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Calls (Subject, ModuleName, RelatedTo_Id,CallType,CallStartTime) VALUES ('A test call', 'Accounts', '5768552000000433001','Outbound','May 20, 2023 12:00 AM')

Required fields: Subject, ModuleName, CallType, CallStartTime and ContactName_Id if ModuleName = Contacts and RelatedTo_Id if any other modulename is specified.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Calls WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Calls SET Subject = 'Updated from API' WHERE Id = '3152079000000484001'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding campaigns.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Campaigns (CampaignName) VALUES ('a test campaign') 

Required fields: CampaignName.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Campaigns WHERE Id = '3152079000000485001'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Campaigns SET CampaignName = 'Updated from API' WHERE Id = '3152079000000485001'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding cases.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Cases (Email, Priority, Description) VALUES ('[email protected]', 'Medium', 'A simple test case')

Required fields: None.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Cases WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Cases SET Priority = 'Low' WHERE Id = '3152079000000473022'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Get, add, update, or delete the list of contact roles

Table-Specific Information

Select

SELECT * FROM ContactRoles

Insert

Insert INTO ContactRoles(SequenceNumber, Name) VALUES(8, 'TEST3')

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM ContactRoles WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE ContactRoles SET Name='Sales Lead 1' where Id= '2732548000006917001'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Create, update, delete, and query information regarding contacts.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Contacts (FirstName, LastName, Email) VALUES ('John', 'Smith', '[email protected]')

Required fields: LastName.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Contacts WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Contacts SET Description = 'John helped us in the past with setting up the servers.' WHERE Id = '3152079000000484021'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Get, add or update currencies to your organization.

Table-Specific Information

Select

The Sync App uses the Zoho CRM API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the Sync App.

• Id supports '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Currencies WHERE Id = '1234';

Insert

INSERT INTO Currencies (ISO_Code, Name, ExchangeRate) VALUES ('1234', 'hello','1.000');

Bulk Insert operation:

INSERT INTO Currencies#TEMP (id, Name, Symbol, IsActive, ExchangeRate, FormatDecimalSeparator, FormatThousandSeparator, FormatDecimalPlaces, PrefixSymbol, ISO_Code) VALUES ('4150868000000236307', 'cdata1','$','true','1.0000','period','comma','2','true','2');

INSERT INTO Currencies#TEMP (id, Name, Symbol, IsActive, ExchangeRate, FormatDecimalSeparator, FormatThousandSeparator, FormatDecimalPlaces, PrefixSymbol, ISO_Code) VALUES ('1234', 'cdata','$','true','2.0000','period','comma','3','true','3');

INSERT INTO Currencies (id, Name, Symbol, IsActive, ExchangeRate, FormatDecimalSeparator, FormatThousandSeparator, FormatDecimalPlaces, PrefixSymbol, ISO_Code) SELECT id, Name, Symbol, IsActive, ExchangeRate, FormatDecimalSeparator, FormatThousandSeparator, FormatDecimalPlaces, PrefixSymbol, ISO_Code FROM currencies#TEMP; 

Required fields: Name, ISO_Code, ExchangeRate.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Update

UPDATE Currencies SET symbol = 'hello', FormatDecimalSeparator = 'period', FormatThousandSeparator = 'comma',FormatDecimalPlaces = '2',prefixsymbol = 'true',exchangerate = '1.0000' WHERE Id = '1234';

Bulk Update Operation:

UPDATE Currencies SET symbol = '$',FormatDecimalSeparator = 'period',FormatThousandSeparator = 'comma',FormatDecimalPlaces = '2';

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated. NOTE: if Id field is not specified in the query, PostData will be created for each id ( driver will execute bulk update operation for each currency id).

Columns

Create, update, delete, and query information regarding deals.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Deals (DealName, Stage) VALUES ('Converting company XYZ into our client.', 'Qualification')

Required fields: DealName.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Deals WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Deals SET Description = 'Updated from API' WHERE Id = '3152079000000153079'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding events.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Events (Title, `From`, To) VALUES ('A test event', '2018/05/29 18:00:00', '2018/05/29 20:30:00')

INSERT INTO Events (Title, `From`, To, ModuleName) VALUES ('A test event', '2018/05/29 18:00:00', '2018/05/29 20:30:00', 'Contacts')

INSERT INTO Events (Title, `From`, To, ModuleName, RelatedTo_Id) VALUES ('A test event', '2018/05/29 18:00:00', '2018/05/29 20:30:00', 'Contacts', '2788704000120533263')

Required fields: Title, From, To, ModuleName if RelatedTo_Id is specified..

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Events WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Events SET Description = 'Changed from the API.' WHERE Id = '3152079000000154219'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

NOTE: This table is supported only in v2.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding invoices. Not available in free and standard ZohoCRM accounts.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Invoices (Status, DueDate, Subject, ProductDetails) VALUES ('Created', '2018/06/06', 'An invoice for the purchase of products XYZ.', '[
            { 
              "product": { 
                "Product_Code": "12342321", 
                "name": "Egg", 
                "id": "3276571000000184076" 
              }, 
              "quantity": 1024, 
              "Discount": 0, 
              "total_after_discount": 1239.04, 
              "net_total": 1239.04, 
              "book": null, 
              "Tax": 0, 
              "list_price": 1.21, 
              "unit_price": 1.21, 
              "quantity_in_stock": -1024, 
              "total": 1239.04, 
              "id": "3276571000000184104", 
              "product_description": null, 
              "line_tax": [] 
            } 
          ]')

Required fields: Subject, InvoicedItems.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Invoices WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Invoices SET Description = 'Updated from API' WHERE Id = '3152079000000153079'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding Leads.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Leads(FirstName, LastName, Title) VALUES('Alert', 'Bat', 'Mr')
INSERT INTO LEADS(FirstName, LastName, Email, Trigger) VALUES('Enthusiastic', 'Panda', '[email protected]', 'workflow,approval,blueprint')

Required fields: LastName.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Leads WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Leads SET Description = 'Changed from the API.' WHERE Id = '3152079000000488014'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding notes.

Table-Specific Information

Select

The Sync App uses the Zoho CRM API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the Sync App.

• NoteTitle supports the '=, LIKE' operators.
• NoteContent supports the '=, LIKE' operators.
• CreatedTime supports the '=' operator.
• ModifiedTime supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Notes WHERE Id = '3152079000000153079'

SELECT * FROM Notes WHERE NoteTitle = 'Deal details'

Insert

INSERT INTO Notes(ParentID_Id, ModuleName, NoteContent) VALUES('3276571000000184007', 'Leads', 'Janet converted this lead.') 

Required fields: ParentID_Id (the Id of the item in which you are inserting the note to), ModuleName (the API name of the module you are inserting the note to), NoteContent.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Notes WHERE Id = '3152079000000488014'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Notes SET NoteTitle = 'Changed from the API.' WHERE Id = '3152079000000304003'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

To fetch the details of all or a specific pipeline in the Deals module. It works only with ApiVersion=2.1.

Table-Specific Information

Select

The Sync App will use the Zoho CRM API to process WHERE clause conditions built with the following column and operator.

For example, the following queries are processed server side:

SELECT * FROM Pipeline WHERE Id = '228122000000223002' AND LayoutId = '228122000000000173'

SELECT * FROM Pipeline WHERE  LayoutId = '228122000000000173'

Insert

Insert Into Pipeline (LayoutId,DisplayValue,default,maps) VALUES ('4917206000000318488','Test_pipee11','true','[\n" +
            "        {\n" +
            "          \"display_value\": \"Qualification\",\n" +
            "          \"sequence_number\": \"2\",\n" +
            "          \"id\": \"4917206000000006801\"\n" +
            "        },\n" +
            "        {\n" +
            "          \"display_value\": \"Needs Analysis\",\n" +
            "          \"sequence_number\": \"1\",\n" +
            "          \"id\": \"4917206000000006803\"\n" +
            "        }\n" +
            "      ]')

Required fields: LayoutId, DisplayValue, Default and maps.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Update

You must specify the Id and LayoutId in the WHERE clause when executing an update against this table.

UPDATE Pipeline Set DisplayValue = 'Pipeline_test12', default = 'false', maps = '[\n" +
            "        {\n" +
            "          \"display_value\": \"Qualification\",\n" +
            "          \"sequence_number\": \"2\",\n" +
            "          \"id\": \"4917206000000006801\"\n" +
            "        },\n" +
            "        {\n" +
            "          \"display_value\": \"Needs Analysis\",\n" +
            "          \"sequence_number\": \"1\",\n" +
            "          \"id\": \"4917206000000006803\"\n" +
            "        }\n" +
            "      ]' WHERE Id = '4917206000000357004' AND LayoutId = '4917206000000318488' ")

Required fields: Id,LayoutId.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Delete

The users can use the TransferAndDeltePipeline stored procedure to delete the pipeline.

Columns

Create, update, delete, and query information regarding price books. Not available in free and standard ZohoCRM accounts.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO PriceBooks (PriceBookName, Active) VALUES ('pricebook_2018', true) 

Required fields: PriceBookName.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM PriceBooks WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE PriceBooks SET Description = 'Changed from the API.' WHERE Id = '3152079000000488023'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding products.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Products(ProductName, ProductActive) VALUES('C-3PO', true)

Required fields: ProductName.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Products WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Products SET Description = 'Changed from the API.' WHERE Id = '3152079000000487023'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding purchase orders. Not available in free and standard ZohoCRM accounts.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO PurchaseOrders (Subject, ProductDetails) VALUES ('Purchase Order', '[
            { 
              "product": { 
                "Product_Code": "12342321", 
                "name": "Egg", 
                "id": "3276571000000184076" 
              }, 
              "quantity": 1024, 
              "Discount": 0, 
              "total_after_discount": 1239.04, 
              "net_total": 1239.04, 
              "book": null, 
              "Tax": 0, 
              "list_price": 1.21, 
              "unit_price": 1.21, 
              "quantity_in_stock": -1024, 
              "total": 1239.04, 
              "id": "3276571000000184104", 
              "product_description": null, 
              "line_tax": [] 
            } 
          ]')

Required fields: Subject, Purchase_Items

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM PurchaseOrders WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE PurchaseOrders SET Description = 'Changed from the API.' WHERE Id = '3152079000000492001'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding quotes. Not available in free and standard ZohoCRM accounts.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Quotes (Subject, QuotedItems) VALUES ('QuotedItems', '[
            { 
              "product": { 
                "Product_Code": "12342321", 
                "name": "Egg", 
                "id": "3276571000000184076" 
              }, 
              "quantity": 1024, 
              "Discount": 0, 
              "total_after_discount": 1239.04, 
              "net_total": 1239.04, 
              "book": null, 
              "Tax": 0, 
              "list_price": 1.21, 
              "unit_price": 1.21, 
              "quantity_in_stock": -1024, 
              "total": 1239.04, 
              "id": "3276571000000184104", 
              "product_description": null, 
              "line_tax": [] 
            } 
          ]')

Required fields: Subject, QuotedItems.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Quotes WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Quotes SET Description = 'Changed from the API.' WHERE Id = '3152079000000490009'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding sales orders. Not available in free and standard ZohoCRM accounts.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO SalesOrders (Subject, ProductDetails) VALUES ('Sales Order', '[
            { 
              "product": { 
                "Product_Code": "12342321", 
                "name": "Egg", 
                "id": "3276571000000184076" 
              }, 
              "quantity": 1024, 
              "Discount": 0, 
              "total_after_discount": 1239.04, 
              "net_total": 1239.04, 
              "book": null, 
              "Tax": 0, 
              "list_price": 1.21, 
              "unit_price": 1.21, 
              "quantity_in_stock": -1024, 
              "total": 1239.04, 
              "id": "3276571000000184104", 
              "product_description": null, 
              "line_tax": [] 
            } 
          ]')

Required fields: Subject, OrderedItems

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM SalesOrders WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE SalesOrders SET Description = 'Changed from the API.' WHERE Id = '3152079000000493027'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding solutions.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

The Sync App uses the Zoho CRM API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the Sync App.

Insert

INSERT INTO Solutions (SolutionTitle, Status) VALUES ('Converting from XML to JSON', 'Created')

Required fields: SolutionTitle.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Solutions WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Solutions SET Question = 'Changed from the API.' WHERE Id = '3152079000000492018'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding tasks.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Tasks(Subject, Status) VALUES ('Finish sending emails to potential leads.', 'Created')

INSERT INTO Tasks(Subject, Status, ModuleName) VALUES ('Finish sending emails to potential leads.', 'Created', 'Contacts')

INSERT INTO Tasks(Subject, Status, ModuleName, RelatedTo_Id) VALUES ('Finish sending emails to potential leads.', 'Created', 'Contacts', '2788704000120533263')

Required fields: Subject, ModuleName if RelatedTo_Id is specified.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Tasks WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Tasks SET Status = 'In progress' WHERE Id = '3152079000000492026'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query information regarding vendors.

Table-Specific Information

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

INSERT INTO Vendors(VendorName, Email, Website) VALUES('XYZVendor', '[email protected]', 'vendeverything.xyz') 

Required fields: VendorName.

Any field which is not read-only (ReadOnly = false in the table below) can be inserted.

Delete

You must specify the Id in the WHERE clause when executing a delete against this table.

DELETE FROM Vendors WHERE Id = '3152079000000153079'

Update

You must specify the Id in the WHERE clause when executing an update against this table.

UPDATE Vendors SET Description = 'Changed from the API' WHERE Id = '3152079000000492032'

Required fields: Id.

Any field which is not read-only (ReadOnly = false in the table below) can be updated.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Zoho CRM Connector for CData Sync Views

Query information regarding performed actions.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query information regarding different histories for a particular custom field.

Table-Specific Information

Select

The Sync App uses the Zoho CRM API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the Sync App.

• ModuleName supports the '=' operator.
• EntityId supports the '=' operator.
• FieldName supports the '=' operator.
• HistoryName supports the '=' operator.

All columns used in the criteria are required. Use FieldName to specify the actual name of the tracked custom field and HistoryName to specify the name of the history tracking section. If you want to retrieve data for a specific entity of a module specify EntityId in the criteria. For example, the following query is processed server side:

SELECT * FROM [CustomFieldHistoryTracking] WHERE ModuleName = 'Leads' AND FieldName = 'pickListField' AND HistoryName = 'Pick List 1'

SELECT * FROM [CustomFieldHistoryTracking] WHERE ModuleName = 'Leads' AND FieldName = 'pickListField' AND HistoryName = 'Pick List 1' AND EntityId = '12345665'

Columns