Skip to Content

Zoho Projects Connection Details

Introduction

Connector Version

This documentation is based on version 22.0.8676 of the connector.

Get Started

Zoho Projects Version Support

The connector leverages the Zoho Projects API (both restapi and the v3 API are used) to enable bidirectional access to project management data from Zoho Projects.

Establish a Connection

Authenticate to Zoho Projects

The connector is already registered with Zoho Projects as an OAuth application. As such, it will, by default, automatically use its Embedded Credentials to connect.

If you use the embedded credentials, no further action is required to establish a connection.

If you would prefer to use your own custom OAuth app, see Custom Credentials.

Use OAuth Authentication

OAuth requires the authenticating user to interact with Zoho Projects using the browser. The connector facilitates this in various ways as described below.

Embedded Credentials

See Embedded Credentials to connect with the connector's embedded credentials and skip creating a custom OAuth app.

Custom Credentials

Instead of connecting with the connector's embedded credentials, you can register an app to obtain the OAuthClientId and OAuthClientSecret.

When to Create a Custom OAuth App

Creating a custom OAuth app is optional as the connector is already registered with Zoho Projects and you can connect with its embedded credentials. You might want to create a custom OAuth app to change the information displayed when users log into the Zoho Projects OAuth endpoint to grant permissions to the connector.

Create a Custom OAuth App

See Creating a Custom OAuth App for a procedure.

Embedded Credentials

Embedded OAuth Credentials
Desktop Authentication with the Embedded OAuth App

You can connect without setting any connection properties for your user credentials. After setting the following, you are ready to connect: InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken. When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process.

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Obtains a new access token when the old one expires.
  3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Custom Credentials

Desktop Authentication with your OAuth App

Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.

Get an OAuth Access Token

After setting the following, you are ready to connect:

  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client Secret in your app settings.
  • CallbackURL: Set this to the Redirect URL in your app settings.
  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Obtains a new access token when the old one expires.
  3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Headless Machines

Use OAuth on a Headless Machine

To create Zoho Projects data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

  1. Instead of installing the connector on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the connector on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
  2. You can then configure the connector to automatically refresh the access token from the headless machine.

You can follow the headless OAuth authentication flow using the connector's embedded OAuth credentials or using the OAuth credentials for your custom OAuth app.

Use the Credentials for a Custom OAuth App

Create a Custom OAuth App

See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.

Obtain a Verifier Code

Set the following properties on the headless machine:

  • InitiateOAuth: Set this to OFF.
  • OAuthClientId: Set this to the App ID in your app settings.
  • OAuthClientSecret: Set this to the App Secret in your app settings.

You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.

  1. Call the GetOAuthAuthorizationURL stored procedure with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
  2. Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
  3. Save the value of the verifier code. You will set this in the OAuthVerifier connection property.
  4. Save the value of the accounts-server. You will set this in the AccountsServer connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

  • OAuthClientId: Set this to the consumer key in your app settings.
  • OAuthClientSecret: Set this to the consumer secret in your app settings.
  • AccountsServer: Set this to the accounts server you obtained from the redirection.
  • OAuthVerifier: Set this to the verifier code.
  • OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.
  • InitiateOAuth: Set this to REFRESH.

Connect to Data

After the OAuth settings file is generated, set the following properties to connect to data:

  • OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.
  • InitiateOAuth: Set this to REFRESH.

Transfer OAuth Settings

Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.

On a second machine, install the connector and connect with the following properties set:

  • OAuthSettingsLocation: Set this to a writable text file.
  • InitiateOAuth: Set this to GETANDREFRESH.
  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client Secret in your app settings.
  • CallbackURL: Set this to the Callback URL in your app settings.

Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

  • InitiateOAuth: Set this to REFRESH.
  • OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Create a Custom OAuth App

Zoho Projects uses the OAuth authentication standard, which requires the authenticating user to interact with Zoho Projects via the browser. The connector facilitates the OAuth exchange in various ways as described below.

Register an OAuth Application

Before you get started with authorization and make any calls to the Zoho CRM API, you need to register your application with Zoho CRM. You can follow the procedure below to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties.

  1. Navigate to the Zoho Developer Console.

  2. Click Add Client, then Server-Based Application.

  3. Enter the client name, homepage URL, and redirect URL.

  4. If you are connecting from a desktop application, set the callback URL to http://localhost:33333, or another port number of your choice.

    If you are connecting from a Web application, set the callback URL you want to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access.

  5. Click Create.

Authenticate to Zoho Projects from a Desktop Application

After setting the following connection properties, you are ready to connect:

  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
  • OAuthClientId: Set this to the client ID you defined for your OAuth app.
  • OAuthClientSecret: Set this to the client secret you defined for your OAuth app.
  • CallbackURL: Set this to the callback URL you defined for your OAuth app.

When you connect, the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Obtains a new access token when the old one expires.
  3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.
Authenticate to Zoho Projects from a Web Application

Follow the steps below to get and manage the OAuth token values in the Web flow.

Get an Access Token

Set the following connection properties to obtain the OAuthAccessToken:

  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client Secret in your app settings.

You can then call stored procedures to complete the OAuth exchange:

  1. Call the GetOAuthAuthorizationURL stored procedure. Set the AuthMode input to WEB and set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.
  2. Log in and authorize the application. You are redirected back to the callback URL.
  3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL.
Connect to Data and Refresh the Token

The OAuthAccessToken returned by GetOAuthAccessToken has a limited lifetime. To automatically refresh the token, set the following on the first data connection. Alternatively, use the RefreshOAuthAccessToken stored procedure to manually refresh the token.

  • InitiateOAuth: Set this to REFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
  • OAuthSettingsLocation: Set this to the path where the connector will save the OAuth values, to be persisted across connections.
  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client Secret in your app settings.
  • OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
  • OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.

On subsequent data connections, set the following:

  • InitiateOAuth
  • OAuthSettingsLocation

Important Notes

Configuration Files and Their Paths

  • All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to the Zoho Projects API.

Key Features

  • The connector models Zoho Projects entities like projects, events, and users as relational views, allowing you to write SQL to query Zoho Projects data.
  • Stored procedures allow you to execute operations to Zoho Projects.
  • Live connectivity to these objects means any changes to your Zoho Projects account are immediately reflected when using the connector.

Tables

Tables describes the available tables. Tables are statically defined to model projects, events, users, etc.

Views

Views describes the available views. Views are statically defined to model Clients, Documents, Activities, etc.

Stored Procedures

Stored Procedures are function-like interfaces to Zoho Projects. Stored procedures allow you to execute operations to Zoho Projects, including approving timelogs and reordering tasks.

Tables

The connector models the data in Zoho Projects into a list of tables that can be queried using standard SQL statements.

Generally, querying Zoho Projects tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Zoho Projects Connector Tables

Name Description
BugAssociatedTasks Fetches the details of the task associated with a bug.
BugComments Fetch comments for the bug.
BugEntityProperties Retrieves the data stored against an entity. The argument of this method should be the key that was used when the same data was stored.
BugFollowers Get list of followers for the bug.
Bugs Gets all the tasks in the given project. It fetches only the main tasks and not the subtasks.
BugTimelogs Gets the time logs under a specific bug.
Contacts Fetches all client contacts from a portal.
Events Fetches all the tags present in a specific portal.
ForumCategories Gets all the forum categories.
ForumComments Gets all the forum comments.
Forums Gets all the forums in the given project.
Milestones Gets list of project layouts.
PortalClients Returns the list of client companies in the portal.
Portals Gets all the portals for the logged in user.
ProjectEntityProperties Retrieves the data stored against an entity. The argument of this method should be the key that was used when the same data was stored.
ProjectGroups Gets all the groups for the specified project.
Projects Gets all the projects for the logged in user.
ProjectUsers Gets the details of all the users in a specific project.
Statuses Gets the statuses for the given project.
Tags Fetches all the tags present in a specific portal.
TaskActivities Retrieve details of who modified a specific task and when.
TaskComments Get all the task comments.
TaskCustomfields Usage information for the operation TaskCustomfields.rsd.
TaskEntityProperties Retrieves the data stored against an entity. The argument of this method should be the key that was used when the same data was stored.
Tasklists Get all the task lists in the given project.
Tasks Gets all the tasks in the given project. It fetches only the main tasks and not the subtasks.
TaskSubtasks View all the subtasks of the given task.
TaskTimelogs Gets the time logs under a specific task.
Teams Fetch the team details from the portal.
TeamUsers Fetch details of a particular team.
Users Gets the details of all the users in a specific project.

BugAssociatedTasks

Fetches the details of the task associated with a bug.

Table Specific Information

This table shows associations between Bugs and Tasks.

Select
  • BugId supports the following operator: =.

For example, the following query is processed server side:

SELECT * FROM BugAssociatedTasks WHERE BugId = '123439000000045005'
Insert

To add a new bug-task association specify the BugId and TaskId fields.

INSERT INTO BugAssociatedTasks (BugId, TaskId) VALUES ('123418000000045005', '["123418000000044005"]')
Delete

To delete an existing bug-task association specify the BugId and TaskId fields.

DELETE FROM BugAssociatedTasks WHERE BugId = '123418000000045005' AND TaskId = '123418000000044005'
Columns
Name Type ReadOnly Description
BugId String False Bug Id.
TaskId String False Associated Task Id.
TaskName String False Associated Task Name.
Prefix String False Associated Task Prefix.
ProjectId String False Associated Task Project Id.
TaskOwners String False Associated Task Owners.
TaskPercentageCompleted String False Associated Task Percentage Completed.
TaskPriority String False Associated Task Priority.
TaskListId String False Associated Task List Id.

BugComments

Fetch comments for the bug.

Select
  • BugId is required to retrieve Bug Comments.

For example, the following query is processed server side:

SELECT * FROM BugComments WHERE BugId = '123456000000045005'
Insert

To add a Bug Comment specify the BugId and Comment fields.

INSERT INTO BugComments (BugId, Comment) VALUES ('123456000000045005', 'Test Comment #1')
Delete

Bug Comments can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM BugComments WHERE BugId = '123456000000045005' AND ID = '123456000000045042'
Columns
Name Type ReadOnly Description
BugId [KEY] String False Bug Id.
Id [KEY] String False Bug Comment Id.
Comment String False Bug Comment Content.
AddedBy String False Bug Comment Added By.
AddedPersonName String False Bug Comment Added Person Name.
CreatedDate Date False Bug Comment Created Date.
CreatedDateTime Datetime False Bug Comment Created Date Time.
UpdatedBy String False Bug Comment Updated By.
UpdatedPerson String False Bug Comment Updated Person.
UpdatedDate Date False Bug Comment Updated Date.
UpdatedDateTime Datetime False Bug Comment Updated Date Time.

BugEntityProperties

Retrieves the data stored against an entity. The argument of this method should be the key that was used when the same data was stored.

Table Specific Information

Entity-properties are key-value pairs stored against the bug entities. They can be used for storing details that are needed for API integrations but don't have to be visible in the UI. The key has to be a String and the Value could be a simple string or a json object. You can store multiple key-value pair for a single entity. These entity properties can be stored / retrieved / updated / deleted using the following queries.

Select
  • BugId and PropertyKey are required to retrieve Bug Entity Properties.

For example, the following query is processed server side:

SELECT * FROM BugEntityProperties WHERE BugId = '123456000000034087' AND PropertyKey = 'key1'
Insert

To add a new Bug Entity Property specify the BugId, PropertyKey and PropertyValue fields.

INSERT INTO BugEntityProperties (BugId, PropertyKey, PropertyValue) VALUES ('123456000000034087', 'key1', 'This is a test value')
Update

ZohoProjects allows updates for the PropertyValue column.

UPDATE BugEntityProperties SET PropertyValue = 'This is an updated property value' WHERE BugId = '123456000000034087' AND PropertyKey = 'key1'
Delete

Bug Entity Properties can be deleted by providing Id, BugId, PropertyKey and issuing a DELETE statement.

DELETE FROM BugEntityProperties WHERE BugId = '166135000000034087' AND PropertyKey = 'key1' AND ID = '166135000000035001'
Columns
Name Type ReadOnly Description
Id [KEY] String False Bug Entity Property Id.
BugId [KEY] String False Bug Id.
PropertyKey [KEY] String False Bug Entity Property Key.
PropertyValue String False Bug Entity Property Value.

BugFollowers

Get list of followers for the bug.

Select
  • BugId is required to retrieve Bug Followers.

For example, the following query is processed server side:

SELECT * FROM BugFollowers WHERE BugId = '123456000000042110'
Insert

To add a Bug Follower specify the BugId and Follower fields.

INSERT INTO BugFollowers (BugId, BugFollowers) VALUES ('123456000000042110', '12345649448')
Delete

Bug Followers can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM BugFollowers WHERE BugId = '123456000000042110'  AND  FollowerId = '12345649448'
Columns
Name Type ReadOnly Description
BugId [KEY] String False Bug Id.
FollowerId String False Bug Follower Id.
FollowerName String False Bug Follower Name.
BugFollowers String False Bug Followers.

Bugs

Gets all the tasks in the given project. It fetches only the main tasks and not the subtasks.

Select

Columns that support the = operator:

  • Id
  • CreatedTime
  • Flag
  • StatusType
  • CustomViewId

Columns that support the = and IN operator:

  • AssigneeId
  • ClassificationId
  • EscalationLevel
  • ModuleId
  • SeverityId
  • StatusId
  • ReporterId
  • MilestoneId
  • AffectedMilestoneId

For example, the following query is processed server side:

SELECT * FROM Bugs
    WHERE ReporterId IN ('12341021738')
    AND AssigneeId IN ('12341021738')
    AND ClassificationId IN ('123456000000030015')
    AND EscalationLevel IN ('0')
    AND Flag = 'Internal'
    AND ModuleId IN ('123456000000030145')
    AND SeverityId IN ('123456000000030151')
AND StatusId IN ('123456000000030083')
Insert

To create a new Bug, you can specify the following fields:

  • Title
  • AssigneeId
  • ClassificationId
  • Description
  • DueDate
  • Flag
  • ModuleId
  • ReproducibleId
  • SeverityId
  • HourlyRate
  • BugFollowers
  • MilestoneId

To create a new Bug, specify the following fields:

INSERT INTO Bugs (Title, Description, AssigneeId, flag, ClassificationId, DueDate, ModuleId, SeverityId, ReproducibleId, HourlyRate, CustomField1)
VALUES ('SQL Inserted Bug #5', 'Test Description', '12341249448', 'Internal', '123456000000031015', '2022-03-20', '123456000000031145', '123456000000031149', '123456000000031053', '2', 'TestCustomField')
Update

You can use the below query to update a bug:

UPDATE Bugs
SET Title = 'SQL Inserted Bug #5',
    Description = 'Another Test Description',
    AssigneeId = '20081249448',
    ClassificationId = '165818000000031015',
    DueDate = '2022-03-20',
    ModuleId = '165818000000031145',
    SeverityId = '165818000000031149',
    ReproducibleId = '165818000000031053',
    HourlyRate = '2',
    cf1 = 'Custom Field Edited #1'
WHERE ID = '165818000000042126'
Delete

Bugs can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Bugs WHERE ID = '123456000000042126'
Columns
Name Type ReadOnly Description
Id [KEY] String False Bug Id.
Key String False Bug Key.
Title String False Bug Title.
AssigneeId String False Bug Assignee Id.
AssigneeName String False Bug Assignee Name.
AssigneeZpuid String False Bug Assignee ZPUID.
AttachmentCount String False Bug Attachment Count.
BugNumber String False Bug Number.
BugPrefix String False Bug Prefix.
ClassificationId Long False Bug Classification Id.
ClassificationType String False Bug Classification Type.
Closed Boolean False Bug Closed.
CommentCount String False Bug Comment Count.
CreatedTime Date False Bug Created Time.
CreatedDateTime Datetime False Bug Created Date Time.
Description String False Bug Description.
DueDate Date False Bug Due Date.
DueDateTime Datetime False Bug Due Date Time.
EscalationLevel String False Bug Escalation Level.
Flag String False Bug Flag. The allowed values are internal, external.
LinkSelfUrl String False Bug Link Self Url.
LinkTimesheetUrl String False Bug Link Timesheet Url.
LinkWebUrl String False Bug Link Web Url.
ModuleId Long False Bug Module Id.
ModuleName String False Bug Module Name.
ReportedPerson String False Bug Reported Person.
ReporterEmail String False Bug Reporter Email.
ReporterId String False Bug Reporter Id.
ReporterNonZuser String False Bug Reporter Non User.
ReproducibleId Long False Bug Reproducible Id.
ReproducibleType String False Bug Reproducible Type.
SeverityId Long False Bug Severity Id.
SeverityType String False Bug Severity Type.
StatusColorcode String False Bug Status Color Code.
StatusId String False Bug Status Id.
StatusType String False Bug Status Type. The allowed values are open, closed.
UpdatedDate Date False Bug Updated Date.
UpdatedDateTime Datetime False Bug Updated Date Time.
GroupNameAssociatedTeamsAnyTeam String False Bug Group Name Associated Teams by Any Team.
GroupNameAssociatedTeamsCount Integer False Bug Group Name Associated Teams Count.
GroupNameIsTeamUnassigned Boolean False Bug Group Name Is Team Unassigned.
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.

Name Type Description
HourlyRate String Bug Hourly Rate.
BugFollowers String Bug Bug Followers.
CustomViewId String Bug Custom View Id.
MilestoneId String Bug Milestone Id.
AffectedMilestoneId String Bug Affected Milestone Id.

BugTimelogs

Gets the time logs under a specific bug.

Select
  • BugId is required to retrieve Bug Time Logs.

For example, the following query is processed server side:

SELECT * FROM BugTimelogs WHERE BugId = '123456000000045005'
Insert

To add a Bug TimeLog specify the BugId and Follower fields.

INSERT INTO BugTimelogs (BugId, LogDate, BillStatus, HoursDisplay, Notes)
VALUES ('123456000000045005', '2022-03-03', 'Non Billable', '01:20', 'This is a test timelog')
Update

To update a Bug TimeLog specify the BugId and ID fields.

UPDATE BugTimelogs
    SET Notes = 'This is an updated SQL Note'
WHERE BugId = '165818000000045005' AND ID = '123456000000044025'
Delete

Bug TimeLogs can be deleted by providing the BugId and ID and issuing a DELETE statement.

DELETE FROM BugTimelogs WHERE BugId = '123456000000045005' AND ID = '123456000000044025'
Columns
Name Type ReadOnly Description
BugId [KEY] String False Bug Id.
Id [KEY] String False Bug Time Log Id.
AddedByName String False Bug Time Log Added By Name.
AddedByZpuid String False Bug Time Log Added By ZPUID.
AddedByZuid Long False Bug Time Log Added By ZUID.
ApprovalStatus String False Bug Time Log Approval Status.
ApproverName String False Bug Time Log Approver Name.
BillStatus String False Bug Time Log Bill Status. The allowed values are Billable, Non Billable.
BugTitle String False Bug Time Log Bug Title.
CreatedDate Date False Bug Time Log Created Date.
CreatedDateTime Datetime False Bug Time Log Created Date Time.
Hours Integer False Bug Time Log Hours.
Minutes Integer False Bug Time Log Minutes.
HoursDisplay String False Bug Time Log Hours Display.
LastModifiedDate Date False Bug Time Log Last Modified Date.
LastModifiedDateTime Datetime False Bug Time Log Last Modified Date Time.
LinkSelfUrl String False Bug Time Log Link Self Url.
LogDate Date False Bug Time Log Date.
LogDateTime Datetime False Bug Time Log Date Time.
Notes Unknown False Bug Time Log Notes.
OwnerId String False Bug Time Log Owner Id.
OwnerName String False Bug Time Log Owner Name.
TotalMinutes Integer False Bug Time Log Total Minutes.

Contacts

Fetches all client contacts from a portal.

Select
  • ClientId is required to retrieve Contacts.

For example, the following query is processed server side:

SELECT * FROM Contacts WHERE ClientId = '123456000000045005'
Insert

To create a new Contact you can specify the following fields:

  • ClientId
  • FirstName
  • LastName
  • Email
  • InvoiceRate
  • WorkProjects
INSERT INTO Contacts(WorkProjects, ClientId, FirstName, LastName, Email, Invoicerate)
VALUES ('123456000000031899', '123456000000039025', 'TestFirstname222', 'TestLastname222', 'testemail@example.com', '15')
Update

To update a Contact specify the ID field.

UPDATE Contacts
    SET FirstName = 'updatedname', LastName = 'updatedlastname', email = 'updatedemail@example.com'
WHERE ID = '166135000000038075'
Delete

Contacts can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Contacts WHERE ID = '123456000000040053'
Columns
Name Type ReadOnly Description
ClientId String False Contact Client Id.
Id [KEY] String False Contact Id.
CrmContactId String False Contact Crm Contact Id.
DisplayName String False Contact Display Name.
FirstName String False Contact First Name.
LastName String False Contact Last Name.
Email String False Contact Email.
InvoiceRate String False Contact Invoice Rate.
WorkProjects String False Array of Project Ids. Multiple Ids can be separated with comma.

Events

Fetches all the tags present in a specific portal.

Select
  • Status supports the following operator =.

For example, the following query is processed server side:

SELECT * FROM Events WHERE Status = 'open'
Insert

To create a new Event you can specify the following fields:

  • Title
  • DurationHour
  • DurationMinutes
  • Location
  • Participants
  • Reminder
  • Repeat
  • ScheduledOn
  • RepeatTimes
INSERT INTO Events (Title, ScheduledOn, DurationHour, DurationMinutes, Participants, Reminder, Repeat, Location)
VALUES ('SQL Event #2', '2022-03-20T18:00:00', '2', '30', '20081249448', '15 mins', 'only once', 'Test Location 2')
Update

To update an Event specify the ID field.

UPDATE Events
    SET title = 'Updated Title #1', DurationHour = '4', DurationMinutes = '10', ScheduledOn = '2022-05-01T23:12:00', Participants = '20081249448'
WHERE ID = '123456000000045018'
Delete

Events can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Events WHERE ID = '123456000000045018'
Columns
Name Type ReadOnly Description
Id [KEY] String False Event Id.
Title String False Event Title.
CreatedBy String False Event Created By.
CreatedByZpuid String False Event Created By ZPUID.
CreatedOn Long False Event Created On.
DurationHour String False Event Duration Hour.
DurationMinutes String False Event Duration Minutes.
Location String False Event Location.
Occurred Integer False Event Occurred.
Occurrences Integer False Event Occurrences.
Participants String False Event Participants.
ProjectName String False Event Project Name.
Reminder String False Event Reminder. The allowed values are on time, 15 mins, 30 mins, 1 hour, 2 hours, 6 hours, 12 hours, 1 day.
Repeat String False Event Repeat. The allowed values are only once, everyday, everyweek, everymonth, everyyear.
ScheduledOn String False Event Scheduled On.
ScheduledOnLong String False Event Scheduled On Long.
ScheduledOnTo String False Event Scheduled On To.
ScheduledOnToLong String False Event Scheduled On To Long.
RepeatTimes String False Event Repeat Times. The allowed values are 2, 3, 4, 5, 6, 7, 8, 9, 10.
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.

Name Type Description
Status String Event Status. The allowed values are open, closed.

ForumCategories

Gets all the forum categories.

Select

You can use the below query to get all ForumCategories:

SELECT * FROM ForumCategories
Insert

To create a new Forum Category you can use the below query:

INSERT INTO ForumCategories (name) VALUES ('New Forum Category')
Delete

ForumCategories can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM ForumCategories WHERE ID = '123456000000045018'
Columns
Name Type ReadOnly Description
Id [KEY] String False Forum Category Id.
Name String False Forum Category Name.

ForumComments

Gets all the forum comments.

Select
  • ForumId is required to retrieve Forum Comments.

You can use the below query to get all Forum Comments:

SELECT * FROM ForumComments WHERE ForumId = '123456000000045018'
Insert

To create a new Forum Comment you can use the below query:

INSERT INTO ForumComments (ForumId, Content, ParentId)
VALUES ('123456000000045028', 'Forum Comment Reply', '123456000000043083')
Delete

Forum Comments can be deleted by providing the Id, ForumId and issuing a DELETE statement.

DELETE FROM ForumComments WHERE ID = '123456000000043083' AND ForumId = '123456000000045028'
Columns
Name Type ReadOnly Description
ForumId [KEY] Long False Forum Id.
Id [KEY] String False Forum Comment Id.
Content String False Forum Comment Content.
Type String False Forum Comment Type.
Attachments String False Forum Comment Attachments.
IsBestAnswer Boolean False Forum Comment Is Best Answer.
Level String False Forum Comment Level.
ParentId String False Forum Comment Parent Id.
ParentPostedBy String False Forum Comment Parent Posted By.
ParentPostedByZpuid String False Forum Comment Parent Posted By ZPUID.
PostDate Date False Forum Comment Post Date.
PostDateTime Datetime False Forum Comment Post Date Time.
PostedBy String False Forum Comment Posted By.
PostedByZpuid String False Forum Comment Posted By Zpuid.
PostedPerson String False Forum Comment Posted Person.
RootId String False Forum Comment Root Id.
ThirdPartyAttachments String False Forum Comment Third Party Attachments.

Forums

Gets all the forums in the given project.

Select
  • Id and CategoryId support the following operator: =.

For example, the following query is processed server side:

SELECT * FROM Forums WHERE ID = '123456000000043771'
Insert

To create a new Forum, you can specify the following fields:

  • Name
  • Type
  • CategoryId
  • Content
  • Flag
  • IsAnnouncementPost
  • IsStickyPost
  • Notify

To create a new Forum, you can use the below query:

INSERT INTO Forums (Name, Type, CategoryId, Content, Flag, IsAnnouncementPost, IsStickyPost)
VALUES ('Forum Post', 'normal', '123456000000039039', 'Content for Forum Post', 'internal', false, false)
Update

To update a Forum, specify the ID field.

UPDATE Forums
    SET Name = 'Updated Post Name', CategoryId = '123456000000093039', Content = 'Updated forum post content'
WHERE ID = '123456000000043771' 
Delete

Forums can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Forums WHERE ID = '123456000000043771'
Columns
Name Type ReadOnly Description
Id [KEY] String False Forum Id.
Name String False Forum Name.
Type String False Forum Type. The allowed values are normal, question.
Attachments String False Forum Attachments.
CategoryId String False Forum Category Id.
CategoryName String False Forum Category Name.
CommentCount Integer False Forum Comment Count.
Content String False Forum Content.
Flag String False Forum Flag.
IsAnnouncementPost Boolean False Forum Is Announcement Post.
IsStickyPost Boolean False Forum Is Sticky Post.
LastActivityDate Date False Forum Last Activity Date.
LastActivityDateTime Datetime False Forum Last Activity Date Time.
LastModifiedDate Date False Forum Last Modified Date.
LastModifiedDateTime Datetime False Forum Last Modified Date Time.
LinkSelfUrl String False Forum Link Self Url.
PostDate Date False Forum Post Date.
PostDateTime Datetime False Forum Post Date Time.
PostedBy String False Forum Posted By.
PostedByZpuid String False Forum Posted By ZPUID.
PostedPerson String False Forum Posted Person.
ThirdPartyAttachments String False Forum Third Party Attachments.
Notify String False Enter the user mail IDs to be notified (Multiple emails can be comma-separated).

Milestones

Gets list of project layouts.

Select

Columns that support the = operator:

  • Id
  • Flag
  • OwnerId
  • Status
  • DisplayType
  • LastModifiedTime

For example, the following query is processed server side:

SELECT * FROM Milestones WHERE ID = '123456000000043771' WHERE flag = 'internal'
Insert

To create a new Milestone you can specify the following fields:

  • Name
  • Flag
  • StartDate
  • EndDate
  • OwnerId

To create a new Milestone you can use the below query:

INSERT INTO Milestones (name, StartDate, EndDate, OwnerId, flag)
VALUES ('New Milestone name', '2022-03-01', '2022-03-10', '1234567890', 'internal')
Update

To update a Milestone specify the ID field.

UPDATE Milestones
    SET Name = 'Updated Name',
        Flag = 'external',
        StartDate = '2022-02-01',
        EndDate = '2022-03-01',
        OwnerId = '1234567890' " +
WHERE ID = '123456000000043021'
Delete

Milestones can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Milestones WHERE ID = '123456000000043021'
Columns
Name Type ReadOnly Description
Id [KEY] String False Project Milestone Id.
Name String False Project Milestone Name.
Flag String False Milestone Flag. The allowed values are internal, external.
Closed Boolean False Milestone Closed.
CreatedDate Date False Milestone Created Date.
CreatedDateTime Datetime False Milestone Created Date Time.
StartDate Date False Milestone Start Date.
StartDateTime Datetime False Milestone Start Date Time.
EndDate Date False Milestone End Date.
EndDateTime Datetime False Milestone End Date Time.
Actual Integer False Milestone Actual.
ActualCostFormatted String False Milestone Actual Cost Formatted.
ActualHoursFormatted String False Milestone Actual Hours Formatted.
Balance Integer False Milestone Balance.
BalanceCostFormatted String False Milestone Balance Cost Formatted.
BcyActual Integer False Milestone Bcy Actual.
BcyActualCost String False Milestone Bcy Actual Cost.
BcyForecasted Integer False Milestone Bcy Forecasted.
BcyForecastedCost String False Milestone Bcy Forecasted Cost.
BcyPlanned Integer False Milestone Bcy Planned.
BcyPlannedCost String False Milestone Bcy Planned Cost.
BudgetType Integer False Milestone Budget Type.
CurrencyCode String False Milestone Currency Code.
Difference Integer False Milestone Difference.
DifferenceFormatted String False Milestone Difference Formatted.
Forecasted Integer False Milestone Forecasted.
ForecastedCostFormatted String False Milestone Forecasted Cost Formatted.
ForecastedHoursFormatted String False Milestone Forecasted Hours Formatted.
LastCalculatedTime String False Milestone Last Calculated Time.
Planned Integer False Milestone Planned.
PlannedCostFormatted String False Milestone Planned Cost Formatted.
PlannedHoursFormatted String False Milestone Planned Hours Formatted.
IsWorkfieldRemoved Boolean False Milestone Is Work Field Removed.
LastUpdatedDate Date False Milestone Last Updated Date.
LastUpdatedDateTime Datetime False Milestone Last Updated Date Time.
SelfUrl String False Milestone Self Url.
StatusUrl String False Milestone Status Url.
OwnerId String False Milestone Owner Id.
OwnerName String False Milestone Owner Name.
OwnerZpuid String False Milestone Owner ZPUID.
ProjectId String False Project Id.
ProjectName String False Project Name.
Sequence Integer False Milestone Sequence.
Status String False Status. The allowed values are completed, notcompleted.
StatusColorCode String False Status Color Code.
StatusId String False Status Id.
StatusName String False Status Name.
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.

Name Type Description
DisplayType String Milestone Display Type. The allowed values are upcoming, delayed.
LastModifiedTime Datetime Milestone Last Modified Time.

PortalClients

Returns the list of client companies in the portal.

Select

You can use the below query to get all Portal Clients:

SELECT * FROM PortalClients
Insert

To create a new PortalClient you can specify the following fields:

  • Name
  • City
  • Country
  • State
  • FirstAddress
  • SecondAddress
  • WebAddress
  • ZipCode
  • UserEmail
  • WorkProjects
  • PrimaryClient
INSERT INTO PortalClients (Name, UserEmail, WebAddress, FirstAddress, SecondAddress, City, State, Country, PrimaryClient, ZipCode)
VALUES ('Company12', 'email1@example.com', 'www.example.com', 'Address line 1', 'Address line 2', 'Budapest', 'Hungary', 'Hungary', 'Yes', '1025')
Columns
Name Type ReadOnly Description
Id [KEY] String False Client Id.
Name String False Client Name.
City String False Client City.
Country String False Client Country.
State String False Client State.
CrmAccountId String False Client Crm Account Id.
FirstAddress String False Client First Address.
SecondAddress String False Client Second Address.
WebAddress String False Client Web Address.
ZipCode String False Client Zip Code.
UserId String False Client User Id.
UserName String False Client User Name.
UserEmail String False Client User Email.
UserZpuid String False Client User Zpuid.
WorkProjects String False Client Work Projects.
PrimaryClient String False Client Primary Client. The allowed values are Yes, No.

Portals

Gets all the portals for the logged in user.

Select

The connector will use the Zoho Projects API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

  • Id and ViewId support the following operator: =.

For example, the following query is processed server side:

SELECT * FROM Portals WHERE ID = '20081021809'
Columns
Name Type ReadOnly Description
Id [KEY] String False Portal Id.
Name String False Portal Name.
CompanyName String False Portal Company Name.
AvailableClients Integer False Portal Available Clients.
AvailableUsers Integer False Portal Available Users.
AvailableProjects Integer False Portal Available Projects.
BugPlan String False Portal Bug Plan.
BugPlural String False Bug Plural.
BugSingular String False Bug Singular.
CanAddTemplate Boolean False Can Add Portal Template.
CanCreateProject Boolean False Can Create Portal Project.
Default Boolean False Default Portal.
ExtensionsAppSettings String False Portal Extensions Locations App Settings.
ExtensionsAttachmentPicker String False Portal Extensions Locations Attachment Picker.
ExtensionsBlueprintDuring String False Portal Extensions Locations Blueprint During.
ExtensionsIssueTab String False Portal Extensions Locations Issue Tab.
ExtensionsIssuedetailsRightpanel String False Portal Extensions Locations Issue Details Right Panel.
ExtensionsProjectTab String False Portal Extensions Locations Project Tab.
ExtensionsTaskTab String False Portal Extensions Locations Task Tab.
ExtensionsTaskTransition String False Portal Extensions Locations Task Transition.
ExtensionsTaskdetailsRightpanel String False Portal Extensions Locations Task Details Right Panel.
ExtensionsTopBand String False Portal Extensions Locations Top Band.
GmtTimeZone String False Portal GMT Time Zone.
GmtTimeZoneOffset Integer False Portal GMT Time Zone Offset.
MeetingEnabled Boolean False Portal Meeting Enabled.
PeopleEnabled Boolean False Portal People Enabled.
CrmPartner Boolean False Portal CRM Partner.
DisplayProjectPrefix Boolean False Portal Display Project Prefix.
DisplayTaskPrefix Boolean False Portal Display Task Prefix.
NewPlan Boolean False Portal New Plan.
SprintsIntegrated Boolean False Portal Sprints Integrated.
TagsAvailable Boolean False Portal Tags Available.
TimeLogRestricted Boolean False Portal Time Log Restricted.
ProjectsModuleId String False Portal Projects Module ID.
TasksModuleId String False Portal Tasks Module ID.
ProjectUrl String False Portal Project URL.
LocaleCode String False Portal Locale Code.
LocaleCountry String False Portal Locale Country.
Language String False Portal Language.
LoginZpuid Long False Portal Login ZPUID.
MaxClients Integer False Portal Max Clients.
MaxUsers Integer False Portal Max Users.
NewUserPlan Boolean False Portal New User Plan.
PercentageCalculation String False Portal Percentage Calculation.
Plan String False Portal Plan.
ProfileId Long False Portal Profile ID.
ProfileName String False Portal Profile Name.
ProfileType Integer False Portal Profile Type.
ActiveProjects Integer False Portal Active Projects.
ProjectPrefix String False Portal Project Prefix.
Role String False Portal Role.
RoleId String False Portal Role ID.
RoleName String False Portal Role Name.
BusinessHoursEnd Integer False Portal Business Hours End.
BusinessHoursStart Integer False Portal Business Hours Start.
DateFormat String False Portal Date Format.
DefaultCurrency String False Portal Default Currency.
DefaultDependencyType String False Portal Default Dependency Type.
BudgetPermission Boolean False Portal Budget Permission.
Holidays String False Portal Holidays.
BudgetEnabled Boolean False Portal Budget Enabled.
LastSyncTime String False Portal Last Sync Time.
StartDayOfWeek String False Portal Start Day of Week.
TaskDateFormat String False Portal Task Date Format.
TaskDurationType String False Portal Task Duration Type.
TimeZone String False Portal Time Zone.
EditTimeLogRestricted Boolean False Portal Edit Time Log Restricted.
LogFutureTimeAllowed Boolean False Portal Log Future Time Allowed.
LogPastTimeAllowed Boolean False Portal Log Past Time Allowed.
DefaultBillingStatus String False Portal Default billing Status.
TimesheetApprovalEnabled Boolean False Portal Timesheet Approval Enabled.
WorkingDays String False Portal Working Days.
SprintsProjectPermission Boolean False Portal Sprints Project Permission.
StorageType String False Portal Storage Type.
TrialEnabled Boolean False Portal Trial Enabled.
LogHrRestrictedByWorkHr Boolean False Portal Log Hr restricted by Work Hr.
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.

Name Type Description
ViewId String

ProjectEntityProperties

Retrieves the data stored against an entity. The argument of this method should be the key that was used when the same data was stored.

Table Specific Information

Entity-properties are key-value pairs stored against the project entities. They can be used for storing details that are needed for API integrations but don't have to be visible in the UI. Key has to be a String and the Value could be a simple string or a json object. You can store multiple key-value pair for a single entity. These entity properties can be stored / retrieved / updated / deleted using the below queries.

Select
  • PropertyKey is required to retrieve ProjectEntityProperties.

For example, the following query is processed server side:

SELECT * FROM ProjectEntityProperties WHERE PropertyKey = 'key1'
Insert

To add a new Project Entity Property specify the PropertyKey and PropertyValue fields.

INSERT INTO ProjectEntityProperties (PropertyKey, PropertyValue) VALUES ('key1', 'This is a test value')
Update

ZohoProjects allows updates for the PropertyValue column.

UPDATE ProjectEntityProperties SET PropertyValue = 'This is an updated property value' WHERE PropertyKey = 'key1'
Delete

Project Entity Properties can be deleted by providing Id, PropertyKey and issuing a DELETE statement.

DELETE FROM ProjectEntityProperties WHERE PropertyKey = 'key1' AND ID = '166135000000035001'
Columns
Name Type ReadOnly Description
Id [KEY] String False Project Entity Property Id.
PropertyKey [KEY] String False Project Entity Property Key.
PropertyValue String False Project Entity Property Value.

ProjectGroups

Gets all the groups for the specified project.

Select

You can use the below query to get all ProjectGroups:

SELECT * FROM ProjectGroups
Insert

To create a new ProjectGroup you can use the below query:

INSERT INTO ProjectGroups(name) VALUES ('New Project Group')
Delete

ProjectGroups can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM ProjectGroups WHERE ID = '123456000000045018'
Columns
Name Type ReadOnly Description
Id [KEY] String False Project Group Id.
Name String False Project Group Name.
Default Boolean False Is Default Project Group.

Projects

Gets all the projects for the logged in user.

Select

Columns that support the = operator:

  • Id
  • CreatedDate
  • Status
  • LastModifiedTime

Columns that support the = and IN operator:

  • GroupId
  • Assignee

For example, the following query is processed server side:

SELECT * FROM Projects WHERE ID = '123456000000039053'
Insert

To create a new Project you can specify the following fields:

  • Name
  • BillingStatus
  • BillingType
  • Currency
  • Description
  • Public
  • OwnerId
  • BudgetType
  • BudgetValue
  • Rate
  • BudgetTrackingMethod
  • FixedCost
  • TemplateId
  • Threshold
  • StartDate
  • EndDate
  • EnableRollup

To create a new Project specify the following fields:

INSERT INTO Projects (Name, BudgetType, BudgetValue, BillingStatus, BillingType, Currency, Description, Public, BudgetType, BudgetTrackingMethod)
VALUES ('New Project', '2', '2.5', 'Billable', '2', 'EUR', 'TestDescription 123', 'no', '2', '4')
Update

You can use the below query to update a Project:

UPDATE Projects
    SET Currency = 'USD', SingleLinCustomField = 'New Custom Field Value'
WHERE ID = '123456000000039053'
Delete

Projects can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Projects WHERE ID = '123456000000039053'
Columns
Name Type ReadOnly Description
Id [KEY] String False Project Id.
Name String False Project Name.
Key String False Project Key.
BugsEnabled Boolean False Project Bugs Enabled.
BillingStatus String False Project Billing Status. The allowed values are Billable, Non Billable.
BillingType String False Mention the billing method for invoicing. (1 = Based on project hours, 2 = Based on staff hours, 3 = Fixed cost for project, 4 = Based on task / issue hours). The allowed values are 1, 2, 3, 4.
BugClientPermission String False Project Bug Client Permission.
BugsClosed Integer False Project Bugs Closed.
BugsOpen Integer False Project Bugs Open.
BugsDefaultView String False Project Bugs Default View.
BugsPrefix String False Project Bugs Prefix.
CreatedDate Date False Project Created Date.
CreatedDateTime Datetime False Project Created Date Time.
CustomStatusColor String False Project Custom Status Color.
CustomStatusId String False Project Custom Status Id.
CustomStatusName String False Project Custom Status Name.
Currency String False The default currency of your project. Example: USD, INR, etc.
CurrencySymbol String False Project Currency Symbol.
Description String False Project Description.
EnabledTabs String False Project Enabled Tabs.
ChatEnabled Boolean False Project Chat Enabled.
ClientAssignBug String False Project Client Assign Bug.
Public String False Project Public. The allowed values are yes, no.
SprintsProject Boolean False Project Sprints Project.
Strict String False Project Strict.
BugLayoutId String False Project Bug Layout Id.
BugLayoutName String False Project Bug Layout Name.
ProjectLayoutId String False Project Project Layout Id.
ProjectLayoutName String False Project Project Layout Name.
TaskLayoutId String False Project Task Layout Id.
TaskLayoutName String False Project Task Layout Name.
ActivityUrl String False Project Activity Url.
BugUrl String False Project Bug Url.
DocumentUrl String False Project Document Url.
EventUrl String False Project Event Url.
FolderUrl String False Project Folder Url.
ForumUrl String False Project Forum Url.
MilestoneUrl String False Project Milestone Url.
SelfUrl String False Project Self Url.
StatusUrl String False Project Status Url.
TaskUrl String False Project Task Url.
TasklistUrl String False Project Tasklist Url.
TimesheetUrl String False Project Timesheet Url.
UserUrl String False Project User Url.
MilestoneClosed Integer False Project Milestone Closed.
MilestoneOpen Integer False Project Milestone Open.
OwnerId String False Project Owner Id.
OwnerName String False Project Owner Name.
OwnerZpUid String False Project Owner Zp Uid.
ProfileId Long False Project Profile Id.
ProjectPercent String False Project Project Percent.
Role String False Project Role.
ShowProjectOverview Boolean False Project Show Project Overview.
Status String False Project Status. The allowed values are active, archived, template.
SettingsDate Boolean False Project Settings Date.
SettingsLogHours Boolean False Project Settings Log Hours.
SettingsPercentage Boolean False Project Settings Percentage.
SettingsPlan Boolean False Project Settings Plan.
SettingsWorkHours Boolean False Project Settings Work Hours.
TaskClosed Integer False Project Task Closed.
TaskOpen Integer False Project Task Open.
TaskbugPrefix String False Project Taskbug Prefix.
UpdatedDate Date False Project Updated Date.
UpdatedDateTime Datetime False Project Updated Date Time.
WorkspaceId String False Project Workspace Id.
BudgetType String False Type of your budget. Accepted values 0, 1, 2, 3, 4, 5 and 6. (0 = None, 1 = Based on amount, 2 = Based on hours, 3 = Based on milestone amount, 4 = Based on milestone hours, 5 = Based on task amount and 6 = Based on task hours). The allowed values are 1, 2, 3, 4, 5, 6.
BudgetTypeValue String False Project Budget Type Value.
BudgetValue String False Project Budget Value.
Rate String False Project Rate.
BudgetTrackingMethod String False Method to track your project budget. Accepted values 1, 2, and 4. (1 = Project hours, 2 = Staff hours, 4 = Task / Issue hours).
FixedCost String False Project Fixed Cost.
TemplateId String False Project Template Id.
Threshold String False Specify the budget threshold limit (Amount or hours)..
StartDate String False Project Start Date.
EndDate String False Project End Date.
EnableRollup String False Project Enable Rollup. The allowed values are Yes, No.
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.

Name Type Description
LastModifiedTime Datetime Project Last Modified Time.
GroupId String Project Group Id.
Assignee String Project Assignee.

ProjectUsers

Gets the details of all the users in a specific project.

Columns
Name Type ReadOnly Description
Id [KEY] String False User Id.
Name String False User Name.
Active Boolean False User Active.
ChatAccess Boolean False User Chat Access.
Email String False User Email.
PortalProfileName String False User Portal Profile Name.
PortalRoleId String False User Portal Role Id.
PortalRoleName String False User Portal Role Name.
ProfileId String False User Profile Id.
ProfileType String False User Profile Type.
ProjectProfileId String False User Project Profile Id.
Role String False User Role. The allowed values are manager, employee, contractor.
RoleId String False User Role Id.
RoleName String False User Role Name.
Zpuid String False User ZPUID.
WorkProjects String False User Work Projects.
UserType String False User User Type. The allowed values are active, inactive.

Statuses

Gets the statuses for the given project.

Select

You can use the below query to get all Statuses:

SELECT * FROM Statuses
Insert

To create a new Status you can specify the following fields:

INSERT INTO Statuses (Content) VALUES ('My Custom Status')
Columns
Name Type ReadOnly Description
Id [KEY] String False Status Id.
Content String False Status Content.
PostedBy String False Status Posted By.
PostedPerson String False Status Posted Person.
PostedDate Date False Status Posted Date.
PostedDateTime Datetime False Status Posted Date Time.

Tags

Fetches all the tags present in a specific portal.

Select

You can use the below query to get all Tags:

SELECT * FROM Tags
Insert

To create a new Tag you can specify the following fields:

INSERT INTO Tags (Name, ColorClass) VALUES ('New Tag', 'bg-tag16')
Update

To update a Tag specify the ID field.

UPDATE Tags SET Name = 'Updated Name', ColorClass = 'bg-tag99' WHERE ID = '123456000000043057'
Delete

Tags can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Tags WHERE ID = '123456000000043057'
Columns
Name Type ReadOnly Description
Id [KEY] String False Tag Id.
Name String False Tag Name.
ColorClass String False Tag Color Class.
CreatedBy String False Tag Created By.
CreatedByName String False Tag Created By Name.
CreatedByEmail String False Tag Created By Email.
CreatedByFirstName String False Tag Created By First Name.
CreatedByLastName Unknown False Tag Created By Last Name.
CreatedByIsClientUser Boolean False Tag Created By Is Client User.
CreatedByZpuid String False Tag Created By Zpuid.
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.

Name Type Description
Tags String

TaskActivities

Retrieve details of who modified a specific task and when.

Select
  • TaskId is required to retrieve Forum Comments.

You can use the below query to get all Activities for a given Task:

SELECT * FROM TaskActivities WHERE TaskId = '123456789'
Columns
Name Type ReadOnly Description
TaskId String False Task Id.
Id [KEY] String False Task Activity Id.
Name String False Task Activity Name.
ActivityBy String False Task Activity By.
OldValue Unknown False Task Activity Old Value.
NewValue Unknown False Task Activity New Value.
State String False Task Activity State.
Time Date False Task Activity Time.
DateTime Datetime False Task Activity Date Time.
Zuid String False Task Activity ZUID.

TaskComments

Get all the task comments.

Select
  • TaskId is required to retrieve Task Comments.
  • LastModifiedTime supports the following operator: =.

For example, the following query is processed server side:

SELECT * FROM TaskComments WHERE TaskId = '123456000000040013'
Insert

To add a Task Comment specify the TaskId and Comment fields.

INSERT INTO TaskComments (TaskId, content) VALUES ('123456000000040013', 'SQL Comment #6')
Update

To update a Task Comments specify the ID field.

UPDATE TaskComments
    SET Content = 'Updated forum post content'
WHERE ID = '123456000000043771' AND TaskId = '123456000000040013'
Delete

Task Comments can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM TaskComments WHERE TaskId = '123456000000045005' AND ID = '123456000000045042'
Columns
Name Type ReadOnly Description
TaskId [KEY] String False Task Id.
Id [KEY] String False Task Comment Id.
Content String False Task Comment Content.
AddedPersonId String False Task Comment Added Person Id.
AddedPersonName String False Task Comment Added Person Name.
AddedVia String False Task Comment Added Via.
Attachments String False Task Comment Attachments.
CreatedDate Date False Task Comment Created Date.
CreatedDateTime Datetime False Task Comment Created Date Time.
LastModifiedDate Date False Task Comment Last Modified Date.
LastModifiedDateTime Datetime False Task Comment Last Modified Date Time.
ProjectName String False Task Comment Project Name.
SprintsNotesId Integer False Task Comment Sprints Notes Id.
ThirdPartyAttachments String False Task Comment Third Party Attachments.
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.

Name Type Description
LastModifiedTime Datetime Task Comment Last Modified Time.

TaskCustomfields

Usage information for the operation TaskCustomfields.rsd.

Columns
Name Type ReadOnly Description
ColumnName [KEY] String False Task Custom Field Column Name.
ColumnType [KEY] String False Task Custom Field Column Type.
DisplayName [KEY] String False Task Custom Field Display Name.
IsDefault [KEY] String False Task Custom Field Is Default.
IsEncrypted [KEY] String False Task Custom Field Is Encrypted.
IsMandatory String False Task Custom Field Is Mandatory.

TaskEntityProperties

Retrieves the data stored against an entity. The argument of this method should be the key that was used when the same data was stored.

Table Specific Information

Entity-properties are key-value pairs stored against the Task entities. They can be used for storing details that are needed for API integrations but don't have to be visible in the UI. Key has to be a String and the Value could be a simple string or a json object. You can store multiple key-value pair for a single entity. These entity properties can be stored / retrieved / updated / deleted using the below queries.

Select
  • TaskId and PropertyKey are required to retrieve Task Entity Properties.

For example, the following query is processed server side:

SELECT * FROM TaskEntityProperties WHERE TaskId = '123456000000034087' AND PropertyKey = 'key1'
Insert

To add a new Task Entity Property specify the TaskId, PropertyKey and PropertyValue fields.

INSERT INTO TaskEntityProperties (TaskId, PropertyKey, PropertyValue) VALUES ('123456000000034087', 'key1', 'This is a test value')
Update

ZohoProjects allows updates for the PropertyValue column.

UPDATE TaskEntityProperties SET PropertyValue = 'This is an updated property value' WHERE TaskId = '123456000000034087' AND PropertyKey = 'key1'
Delete

Task Entity Properties can be deleted by providing Id, TaskId, PropertyKey and issuing a DELETE statement.

DELETE FROM TaskEntityProperties WHERE TaskId = '166135000000034087' AND PropertyKey = 'key1' AND ID = '166135000000035001'
Columns
Name Type ReadOnly Description
Id [KEY] String False Task Entity Property Id.
TaskId [KEY] String False Task Id.
PropertyKey [KEY] String False Task Entity Property Key.
PropertyValue String False Task Entity Property Value.

Tasklists

Get all the task lists in the given project.

Select
  • MilestoneId and Flag support the following operator: =.

For example, the following query is processed server side:

SELECT * FROM Tasklists WHERE MilestoneId = '12345600000123' AND Flag = 'internal'
Insert

To create a new Tasklist you can specify the following fields:

  • Name
  • MilestoneId
  • TaskTemplateId
  • ShiftDays
  • Flag
INSERT INTO Tasklists (name, flag, ShiftDays)
VALUES ('New Tasklist #2', 'internal', '2022-03-10T01:00:00Z')
Update

To update a Tasklist specify the ID field.

UPDATE Tasklists SET Name = 'SQL Updated tasklist #7' WHERE ID = '123456000000041033'
Delete

Tasklists can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Tasklists WHERE ID = '123456000000041033'
Columns
Name Type ReadOnly Description
Id [KEY] String False Tasklist Id.
Name String False Tasklist Name.
Completed Boolean False Tasklist Completed.
CreatedDate Date False Tasklist Created Date.
CreatedDateTime Datetime False Tasklist Created Date Time.
LastUpdatedDate Date False Tasklist Last Updated Date.
LastUpdatedDateTime Datetime False Tasklist Last Updated Date Time.
LinkSelfUrl String False Tasklist Link Self Url.
LinkTaskUrl String False Tasklist Link Task Url.
MilestoneClosed Boolean False Tasklist Milestone Closed.
MilestoneCreatedDate Date False Tasklist Milestone Created Date.
MilestoneCreatedDateTime Datetime False Tasklist Milestone Created Date Time.
MilestoneEndDate Date False Tasklist Milestone End Date.
MilestoneEndDateTime Datetime False Tasklist Milestone End Date Time.
MilestoneFlag String False Tasklist Milestone Flag.
MilestoneId String False Tasklist Milestone Id.
MilestoneIsWorkfieldRemoved Boolean False Tasklist Milestone Is Workfield Removed.
MilestoneLastUpdatedDate Date False Tasklist Milestone Last Updated Date.
MilestoneLastUpdatedDateTime Datetime False Tasklist Milestone Last Updated Date Time.
MilestoneLinkSelfUrl String False Tasklist Milestone Link Self Url.
MilestoneLinkStatusUrl String False Tasklist Milestone Link Status Url.
MilestoneName String False Tasklist Milestone Name.
MilestoneOwnerId String False Tasklist Milestone Owner Id.
MilestoneOwnerName String False Tasklist Milestone Owner Name.
MilestoneOwnerZpuid String False Tasklist Milestone Owner Zpuid.
MilestoneProjectId String False Tasklist Milestone Project Id.
MilestoneProjectName String False Tasklist Milestone Project Name.
MilestoneSequence Integer False Tasklist Milestone Sequence.
MilestoneStartDate Date False Tasklist Milestone Start Date.
MilestoneStartDateTime Datetime False Tasklist Milestone Start Date Time.
MilestoneStatus String False Tasklist Milestone Status.
MilestoneStatusDetColorcode String False Tasklist Milestone Status Det Colorcode.
MilestoneStatusDetId String False Tasklist Milestone Status Det Id.
MilestoneStatusDetName String False Tasklist Milestone Status Det Name.
Rolled Boolean False Tasklist Rolled.
Sequence Integer False Tasklist Sequence.
TaskCountOpen Integer False Tasklist Task Count Open.
TaskTemplateId String False ID of the task list template.
ShiftDays Datetime False The ISO 8601 Datetime from which the Task start date is calculated, based on shift days.
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.

Name Type Description
Flag String Tasklist Flag. The allowed values are internal, external.

Tasks

Gets all the tasks in the given project. It fetches only the main tasks and not the subtasks.

Select

Columns that support the = operator:

  • Id
  • CreatedBy
  • MilestoneId
  • Priority
  • TasklistId
  • Owner
  • Status
  • Time
  • LastModifiedTime
  • ViewId

  • CreatedDate and LastModifiedTime support: ORDER

For example, the following query is processed server side:

SELECT * FROM Tasks WHERE MilestoneId = '12345600000123' AND Priority = 'High' ORDER BY CreatedDate
Insert

To create a new Task you can specify the following fields:

  • Name
  • Description
  • Owners
  • Duration
  • DurationType
  • EndDate
  • Priority
  • StartDate
  • TasklistId
  • Work
  • GroupNameAssociatedTeamsAnyTeam
  • RatePerHour
  • ReminderString
  • Recurrence
  • BudgetValue
  • BudgetThreshold
INSERT INTO Tasks (Name, Description, Duration, DurationType, Priority,  BudgetValue)
VALUES ('New Task #2', 'This is a test description', '5', 'hrs', 'High', '1000')
Update

To update a Task specify the ID field.

UPDATE Tasks SET Priority = 'Low' WHERE ID = '123456000000034003'
Delete

Tasks can be deleted by providing the ID and issuing a DELETE statement.

DELETE FROM Tasks WHERE ID = '123456000000034003'
Columns
Name Type ReadOnly Description
Id [KEY] String False Task Id.
Name String False Task Name.
Key String False Task Key.
BillingType String False Task Billing Type.
Completed Boolean False Task Completed.
CreatedBy String False Task Created By.
CreatedByEmail String False Task Created By Email.
CreatedByZpuid String False Task Created By Zpuid.
CreatedPerson String False Task Created Person.
CreatedDate Date False Task Created Date.
CreatedDateTime Datetime False Task Created Date Time.
Description String False Task Description.
Owners String False Task Owners.
Duration String False Task Duration.
DurationType String False Task Duration Type. The allowed values are days, hrs.
EndDate Date False Task End Date.
EndDateTime Datetime False Task End Date Time.
CommentAdded Boolean False Task Comment Added.
DocsAssociated Boolean False Task Docs Associated.
ForumAssociated Boolean False Task Forum Associated.
RecurrenceSet Boolean False Task Recurrence Set.
ReminderSet Boolean False Task Reminder Set.
Parent Boolean False Task Parent.
LastUpdatedDate Date False Task Last Updated Date.
LastUpdatedDateTime Datetime False Task Last Updated Date Time.
LinkSelfUrl String False Task Link Self Url.
LinkTimesheetUrl String False Task Link Timesheet Url.
LinkWebUrl String False Task Link Web Url.
LogHoursBillableHours String False Task Log Hours Billable Hours.
LogHoursNonBillableHours String False Task Log Hours Non Billable Hours.
MilestoneId String False Task Milestone Id.
OrderSequence Integer False Task Order Sequence.
PercentComplete String False Task Percent Complete.
Priority String False Task Priority. The allowed values are none, low, medium, high.
StartDate Date False Task Start Date.
StartDateTime Datetime False Task Start Date Time.
StatusColorCode String False Task Status Color Code.
StatusName String False Task Status Name.
StatusId String False Task Status Id.
StatusType String False Task Status Type.
Subtasks Boolean False Task Subtasks.
TaskFollowers String False Task Task Followers.
TaskFollowerSize Integer False Task Task Follower Size.
TasklistId String False Tasklist Id.
TasklistName String False Tasklist Name.
Work String False Task Work.
WorkForm String False Task Work Form.
WorkType String False Task Work Type. The allowed values are work_hrs_per_day.
GroupNameAssociatedTeamsAnyTeam String False Task Group Name Associated Teams Any Team.
GroupNameAssociatedTeamsCount Integer False Task Group Name Associated Teams Count.
GroupNameIsTeamUnassigned Boolean False Task Group Name Is Team Unassigned.
RatePerHour String False Task Rate Per Hour.
ReminderString String False Task Reminder String.
Recurrence String False Task Recurrence.
BudgetValue String False Task Budget Value.
BudgetThreshold String False Task Budget Threshold.
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.

Name Type Description
Owner String Task Owner.
Status String Task Status. The allowed values are completed, notcompleted.
Time String Task Time. The allowed values are overdue, today, tomorrow.
LastModifiedTime Datetime Task Last Modified Time.
ViewId String Task View Id.

TaskSubtasks

View all the subtasks of the given task.

Select
  • TaskId is required to retrieve Task Subtasks.

You can use the below query to get all Task Subtasks:

SELECT * FROM TaskSubtasks WHERE TaskId = '12345600000123'
Insert

To create a new Task you can specify the following fields:

  • TaskId
  • Name
  • Description
  • Duration
  • DurationType
  • Priority
  • StartDate
  • EndDate
  • PersonResponsible
INSERT INTO TaskSubtasks (TaskId, Name, StartDate, EndDate, Duration, DurationType, Priority, Description)
VALUES ('165818000000040013', 'Sql Subtask #4', '2022-03-01', '2022-03-10', '2', 'hrs', 'High', 'Test description of the subtask')
Columns
Name Type ReadOnly Description
TaskId String False Task Id.
Id [KEY] String False Subtask Id.
Name String False Subtask Name.
Key String False Subtask Key.
BillingType String False Subtask Billing Type.
Completed Boolean False Subtask Completed.
CreatedBy String False Subtask Created By.
CreatedByEmail String False Subtask Created By Email.
CreatedByZpuid String False Subtask Created By Zpuid.
CreatedPerson String False Subtask Created Person.
CreatedTime Date False Subtask Created Time.
CreatedDateTime Datetime False Subtask Created Date Time.
Depth Integer False Subtask Depth.
Description String False Subtask Description.
DetailsOwners String False Subtask Details Owners.
Duration String False Subtask Duration.
DurationType String False Subtask Duration Type. The allowed values are days, hrs.
IsCommentAdded Boolean False Subtask Is Comment Added.
IsDocsAssociated Boolean False Subtask Is Docs Associated.
IsForumAssociated Boolean False Subtask Is Forum Associated.
IsRecurrenceSet Boolean False Subtask Is Recurrence Set.
IsReminderSet Boolean False Subtask Is Reminder Set.
IsParent Boolean False Subtask Is Parent.
LastUpdatedTime Date False Subtask Last Updated Time.
LastUpdatedDateTime Datetime False Subtask Last Updated Date Time.
LinkSelfUrl String False Subtask Link Self Url.
LinkTimesheetUrl String False Subtask Link Timesheet Url.
LinkWebUrl String False Subtask Link Web Url.
LogBillableHours String False Subtask Log Billable Hours.
LogNonBillableHours String False Subtask Log Non Billable Hours.
MilestoneId String False Subtask Milestone Id.
OrderSequence Integer False Subtask Order Sequence.
ParentTaskId String False Subtask Parent Task Id.
PercentComplete String False Subtask Percent Complete.
Priority String False Subtask Priority. The allowed values are none, low, medium, high.
RootTaskId String False Subtask Root Task Id.
StatusColorCode String False Subtask Status Color Code.
StatusId String False Subtask Status Id.
StatusName String False Subtask Status Name.
StatusType String False Subtask Status Type.
Subtasks Boolean False Subtask Subtasks.
TaskFollowers String False Subtask Task Followers.
TaskFollowerSize Integer False Subtask Task Follower Size.
TasklistId String False Subtask Tasklist Id.
TasklistName String False Subtask Tasklist Name.
Work String False Subtask Work.
WorkForm String False Subtask Work Form.
WorkType String False Subtask Work Type.
GroupNameAssociatedTeamsAnyTeam String False Subtask Group Name Associated Teams Any Team.
GroupNameAssociatedTeamsCount Integer False Subtask Group Name Associated Teams Count.
GroupNameIsTeamUnassigned Boolean False Subtask Group Name Is Team Unassigned.
StartDate Date False Subtask Start Date.
EndDate Date False Subtask End Date.
PersonResponsible String False Subtask Person Responsible.

TaskTimelogs

Gets the time logs under a specific task.

Select
  • TaskId is required to retrieve Task Time Logs.

For example, the following query is processed server side:

SELECT * FROM TaskTimelogs WHERE TaskId = '123456000000045005'
Insert

To create a new Task Timelog you can specify the following fields:

  • TaskId
  • BillStatus
  • Notes
  • HoursDisplay
  • LogDate
  • OwnerId
INSERT INTO TaskTimelogs (TaskId, LogDate, BillStatus, HoursDisplay, Notes) " +
VALUES ('123456000000040013', '2022-03-03', 'Non Billable', '01:20', 'This is a test timelog')
Update

To update a Task TimeLog specify the TaskId and ID fields.

UPDATE TaskTimelogs
    SET Notes = 'This is an updated SQL Note'
WHERE TaskId = '165818000000040013' AND ID = '165818000000045001'
Delete

Task TimeLogs can be deleted by providing the TaskId and ID and issuing a DELETE statement.

DELETE FROM TaskTimelogs WHERE TaskId = '123456000000045005' AND ID = '123456000000044025'
Columns
Name Type ReadOnly Description
TaskId [KEY] String False Task Id.
Id [KEY] String False Task Time Log Id.
AddedByName String False Task Time Log Added By Name.
AddedByZpuid String False Task Time Log Added By Zpuid.
AddedByZuid Long False Task Time Log Added By Zuid.
ApprovalStatus String False Task Time Log Approval Status.
ApproverName String False Task Time Log Approver Name.
BillStatus String False Task Time Log Bill Status. The allowed values are Billable, Non Billable.
CreatedDate Date False Task Time Log Created Date.
CreatedDateTime Datetime False Task Time Log Created Date Time.
StartTime Datetime False Task Time Log Start Time.
EndTime Datetime False Task Time Log End Time.
Notes String False Task Time Log Notes.
HoursDisplay String False Task Time Log Hours Display.
Hours Integer False Task Time Log Hours.
Minutes Integer False Task Time Log Minutes.
IsParent Boolean False Task Time Log Is Parent.
IsSubTask Boolean False Task Time Log Is Sub Task.
LastModifiedDate Date False Task Time Log Last Modified Date.
LastModifiedDateTime Datetime False Task Time Log Last Modified Date Time.
LinkSelfUrl String False Task Time Log Link Self Url.
LogDate Date False Task Time Log Date.
LogDateTime Datetime False Task Time Log Date Time.
OwnerId String False Task Time Log Owner Id.
OwnerName String False Task Time Log Owner Name.
SubTaskLevel String False Task Time Log Sub Task Level.
TaskName String False Task Time Log Task Name.
TaskListId Long False Task Time Log Task List Id.
TaskListName String False Task Time Log Task List Name.
TotalMinutes Integer False Task Time Log Total Minutes.

Teams

Fetch the team details from the portal.

Select

You can use the below query to get all Teams:

SELECT * FROM Teams
Insert

To create a new Team you can specify the following fields:

  • GroupName
  • EmailAlias
  • UserIdArr
  • ProjIds
  • TeamLead
INSERT INTO Teams (GroupName, UserIdArr, TeamLead, ProjIds)" +
VALUES ('TestTeam5', '["123456000000031003", "123456000000032055"]', '123456000000032055', '["123456000000031899"]
Update

To update a Team specify the GroupId field.

UPDATE Teams
SET TeamLead = '123456000000031003',
    UserIdArr = '["123456000000031003", "123456000000032055"]',
    ProjIds = '["123456000000031899"]',
    GroupName = 'TestTeam5'
WHERE GroupId = '123456000000036001'
Delete

Teams can be deleted by providing the GroupId and issuing a DELETE statement.

DELETE FROM Teams WHERE GroupId = '123456000000044025'
Columns
Name Type ReadOnly Description
GroupId [KEY] String False Group ID of the team.
GroupName String False Group Name of the team.
CreatedBy String False Team Created By.
CreatedTime String False Created Time of the team.
Description String False Description of the team.
EmailAlias String False Email ID of the team.
EmailVerified Boolean False Email Verified of the team.
OrgId String False Organization ID of the team.
OwnerEmail String False Owner Email of the team.
OwnerName String False Owner Name of the team.
OwnerZpuid String False Owner Zpuid of the team.
OwnerZuid String False Owner Zuid of the team.
Prefix String False Prefix of the team.
UpdatedBy String False Updated By of the team.
UpdatedTime String False Updated Time of the team.
HasGroupEdit Boolean False Has Group Edit of the team.
ProjectCount Integer False Project Count of the team.
UserCount Integer False User Count of the team.
UserIdArr String False Array of multiple Zpuid.
UserObj String False User Object of the team.
ProjIds String False Array of multiple project IDs.
TeamLead String False ZPuid of the team lead.

TeamUsers

Fetch details of a particular team.

Select
  • GroupId is required to retrieve Team Users.

You can use the below query to get all Team Users:

SELECT * FROM TeamUsers WHERE GroupId = '123456000000033021'
Insert

To create a new Team you must specify the following fields:

  • GroupId
  • Zpuid
INSERT INTO TeamUsers (GroupId, ZPUID)
VALUES ('123456000000034163', '123456000000031003')
Delete

TeamUsers can be deleted by providing the GroupId and ID and issuing a DELETE statement.

DELETE FROM TeamUsers WHERE GroupId = '123456000000034163' AND ID = '123456000000031003'
Columns
Name Type ReadOnly Description
ProjectId String False Project Id.
GroupId String False Team Id.
Id [KEY] String False User Id.
Name String False User Name.
AddedBy String False User Added By.
AddedTime String False User Added Time.
Confirmed String False User Confirmed.
CreatedBy String False User Created By.
CreatedTime String False User Created Time.
CreatedUser String False User Created User.
Description String False User Description.
DisplayName String False User Display Name.
Email String False User Email.
EntityType String False User Entity Type.
FirstName String False User First Name.
IsClient Boolean False User Is Client.
IsDefault Boolean False User Is Default.
LastAccessedTime String False User Last Accessed Time.
LastName String False User Last Name.
LastUpdatedTime String False User Last Updated Time.
RoleId String False User Role Id.
RoleName String False User Role Name.
Type String False User Type.
UpdatedBy String False User Updated By.
UpdatedDate String False User Updated Date.
UpdatedDateTime String False User Updated Date Time.
UserStatus String False User User Status.
Zpuid String False User Zpuid.
Zuid String False User Zuid.

Users

Gets the details of all the users in a specific project.

Columns
Name Type ReadOnly Description
Id [KEY] String False User Id.
Name String False User Name.
Active Boolean False User Active.
ChatAccess Boolean False User Chat Access.
Email String False User Email.
PortalProfileName String False User Portal Profile Name.
PortalRoleId String False User Portal Role Id.
PortalRoleName String False User Portal Role Name.
ProfileId String False User Profile Id.
ProfileType String False User Profile Type.
ProjectProfileId String False User Project Profile Id.
Role String False User Role. The allowed values are manager, employee, contractor.
RoleId String False User Role Id.
RoleName String False User Role Name.
Zpuid String False User Zpuid.
WorkProjects String False User Work Projects.
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.

Name Type Description
UserType String User Type. The allowed values are active, inactive.

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Zoho Projects Connector Views

Name Description
Activities List all the recent activities of the project.
BugActivities Gets all the activities for the given bug.
BugAttachments Get details of attachments for a bug.
BugCustomFields Gets all the custom fields in the given project.
BugResolution Get bug's resolution.
Clients Returns the list of client companies associated with a specific project ID.
Documents List all the recent activities of the project.
MyBugs Lists all the bugs created by you or assigned to you.
MyMilestones Get all the milestones assigned to a particular user in the given project.
MyTasks Gets all your tasks in the portal.
MyTimelogs Gets the time logs under a specific bug.
ProjectCustomFields Gets all the project custom fields.
ProjectCustomStatus Gets list of all project custom status.
ProjectLayouts Gets list of project layouts.
Timelogs Gets the time logs under a specific bug.
TaskAttachments Retrieve details of attachments associated to the task.
TaskLayouts View the list of all the task layouts available in your portal.
TaskStatusHistory Fetches status history of tasks from the Portal.
Timelogs Gets the time logs under a specific bug.
TimesheetCustomFields Fetch all timesheet custom fields across the project.
PortalUsersRemaining Gets the details of all users in a specific portal.

Activities

List all the recent activities of the project.

Select

This is a project-level view. You can use the below query to get all Activities:

SELECT * FROM Activities
Columns
Name Type Description
Id [KEY] String Project Activity Id.
Name String Project Activity Name.
Activity By String Project Activity Author.
Activity For String Project Activity For Entity.
Display Time String Project Activity Display Time.
State String Project Activity State.
Time String Project Activity Time.

BugActivities

Gets all the activities for the given bug.

Select

This is a bug-level view.

  • BugId is required to retrieve BugActivities.

You can use the below query to get all BugActivities:

SELECT * FROM BugActivities WHERE BugId = '12345600001'
Columns
Name Type Description
BugId String Bug Id.
Action String Bug Activity Action.
Type String Bug Activity Type.
PreviousValue String Bug Activity Previous Value.
CurrentValue String Bug Activity Current Value.
ActionBy String Bug Activity Action By.
ActionField String Bug Activity Action Field.
ActionDate Date Bug Activity Action Date.
ActionDateTime Datetime Bug Activity Action Date Time.
Zuid [KEY] String Bug Activity ZUID.

BugAttachments

Get details of attachments for a bug.

Columns
Name Type Description
BugId String Bug Id.
AttachmentId Long Attachment Id.
FileName String Attached File Name.
FileType String Attached File Type.
FileSize Integer Attached File Size.
FileEncAttachParam String Attached File Enc Attach Param.
FileUri String Attached File Uri.
AttachedDate Date Attached Date.
AttachedDateTime Datetime Attached Date Time.
AuthorId String Attachment Author Id.
AuthorName String Attachment Author Name.
IsDocsAttachment Boolean Is Docs Attachment.

BugCustomFields

Gets all the custom fields in the given project.

Select

This is a project-level view. You can use the below query to get all BugCustomFields:

SELECT * FROM BugCustomFields WHERE BugId = '12345600001'
Columns
Name Type Description
LabelName String Bug Custom Field Label Name.
ColumnName String Bug Custom Field Column Name.
DefaultValue String Bug Custom Field Default Value.
FieldType String Bug Custom Field Field Type.
IsEnc Boolean Bug Custom Field Is Enc.
IsMandatory Boolean Bug Custom Field Is Mandatory.
IsPi Boolean Bug Custom Field Is Pi.
IsVisible Boolean Bug Custom Field Is Visible.
NewColumnName String Bug Custom Field New Column Name.
PicklistValues String Bug Custom Field Picklist Values.

BugResolution

Get bug's resolution.

Columns
Name Type Description
BugId String Bug Id.
Resolver String Bug Resolver.
ResolverId String Bug Resolver Id.
Resolution String Bug Resolution.
ResolutionThirdPartyAttachments String Bug Resolution Third Party Attachments.
ResolvedDate Date Bug Resolved Date.
ResolvedDateTime Datetime Bug Resolved Date Time.
ResolverZpuid String Bug Resolver ZPUID.

Clients

Returns the list of client companies associated with a specific project ID.

Select

This is a project-level view.

  • Id supports the following operator: =.

For example, the following query is processed server side:

SELECT * FROM Clients WHERE ID = '12345600001'
Columns
Name Type Description
Id [KEY] String Client Id.
Name String Client Name.
City String Client City.
Country String Client Country.
CrmAccountId String Client Crm Account Id.
FirstAddress String Client First Address.
SecondAddress String Client Second Address.
State String Client State.
WebAddress String Client Web Address.
ZipCode String Client Zip Code.
UserId String Client User Id.
UserName String Client User Name.
UserEmail String Client User Email.
UserZpuid String Client User ZPUID.

Documents

List all the recent activities of the project.

Columns
Name Type Description
Id [KEY] String Project Activity Id.
Name String Project Activity Name.
Activity By String Project Activity Author.
Activity For String Project Activity For.
Display Time String Project Activity Display Time.
State String Project Activity State.
Time String Project Activity Time.

MyBugs

Lists all the bugs created by you or assigned to you.

Select

This is a portal-level view.

  • StatusType, Owner, ViewType and ViewType support the following operator: =.
  • CreatedDate and LastModifiedTime support: ORDER

For example, the following query is processed server side:

SELECT * FROM MyBugs WHERE Owner = '12345021738' AND ViewType = 'owned' AND StatusType = 'open' ORDER BY CreatedDate
Columns
Name Type Description
Id [KEY] String Bug Id.
Key String Bug Key.
Title String Bug Title.
AssigneeId String Bug Assignee Id.
AssigneeName String Bug Assignee Name.
AssigneeZpuid String Bug Assignee ZPUID.
AttachmentCount String Bug Attachment Count.
BugPrefix String Bug Prefix.
ClassificationId Long Bug Classification Id.
ClassificationType String Bug Classification Type.
Closed Boolean Bug Closed.
CommentCount String Bug Comment Count.
CreatedDate Date Bug Created Date.
CreatedDateTime Datetime Bug Created Date Time.
Description String Bug Description.
DueDate Date Bug Due Date.
DueDateTime Date Bug Due Date Time.
Flag String Bug Flag.
LinkSelfUrl String Bug Link Self Url.
LinkTimesheetUrl String Bug Link Timesheet Url.
ModuleId Long Bug Module Id.
ModuleName String Bug Module Name.
ProjectId Long Bug Project Id.
ProjectIdString String Bug Project ID String.
ProjectName String Bug Project Name.
ReportedPerson String Bug Reported Person.
ReporterId String Bug Reporter Id.
ReproducibleId Long Bug Reproducible Id.
ReproducibleType String Bug Reproducible Type.
SeverityId Long Bug Severity Id.
SeverityType String Bug Severity Type.
StatusId Long Bug Status Id.
StatusType String Bug Status Type. The allowed values are open, closed.
UpdatedDate Date Bug Updated Date.
UpdatedDateTime Datetime Bug Updated Date Time.
UpdatedTimeLong Long Bug Updated Time Long.
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.

Name Type Description
Owner String Bug Owner.
ViewType String Bug View Type. The allowed values are reported, owned.
LastModifiedTime Datetime Bug Last Modified Time.

MyMilestones

Get all the milestones assigned to a particular user in the given project.

Select

This is a portal-level view.

  • ProjectId and Flag support the following operator: =.
  • ProjectId supports the operator: IN
  • CreatedDate and LastModifiedTime support: ORDER

For example, the following query is processed server side:

SELECT * FROM MyMilestones WHERE ProjectId IN ('12345000000030899', '12345000000033005') AND Flag = 'internal' ORDER BY CreatedDate
Columns
Name Type Description
ProjectId String Project Id.
Id [KEY] String Milestone Id.
Name String Milestone Name.
Closed Boolean Milestone Closed.
CreatedDate Date Milestone Created Date.
CreatedDateTime Datetime Milestone Created Date Time.
EndDate Date Milestone End Date.
EndDateTime Datetime Milestone End Date Time.
Flag String Milestone Flag. The allowed values are internal, external.
IsWorkfieldRemoved Boolean Milestone Is Work Field Removed.
LastUpdatedDate Date Milestone Last Updated Date.
LastUpdatedDateTime Datetime Milestone Last Updated Date Time.
SelfUrl String Milestone Self Url.
StatusUrl String Milestone Status Url.
OwnerId String Milestone Owner Id.
OwnerName String Milestone Owner Name.
OwnerZpuid String Milestone Owner ZPUID.
ProjectName String Milestone Project Name.
Sequence Integer Milestone Sequence.
StartDate Date Milestone Start Date.
StartDateTime Datetime Milestone Start Date Time.
Status String Milestone Status.
StatusColorCode String Milestone Status Color Code.
StatusId String Milestone Status Id.
StatusName String Milestone Status Name.
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.

Name Type Description
LastModifiedTime Datetime Milestone Last Modified Time.

MyTasks

Gets all your tasks in the portal.

Select

This is a portal-level view. Columns that support the = operator:

  • Id
  • CreatedBy
  • MilestoneId
  • Priority
  • TasklistId
  • Owner
  • Status
  • Time
  • LastModifiedTime
  • ViewId

For example, the following query is processed server side:

SELECT * FROM MyTasks WHERE MilestoneId = '12345600000123' AND Priority = 'High'
Columns
Name Type Description
ProjectId String Project Id.
Id [KEY] String Task Id.
Name String Task Name.
Key String Task Key.
BillingType String Task Billing Type.
Completed Boolean Task Completed.
CreatedBy String Task Created By.
CreatedByEmail String Task Created By Email.
CreatedByZpuid String Task Created By Zpuid.
CreatedPerson String Task Created Person.
CreatedDate Date Task Created Date.
CreatedDateTime Datetime Task Created Date Time.
Description String Task Description.
Owners String Task Owners.
Duration String Task Duration.
DurationType String Task Duration Type.
EndDate Date Task End Date.
EndDateTime Datetime Task End Date Time.
CommentAdded Boolean Task Comment Added.
DocsAssociated Boolean Task Docs Associated.
ForumAssociated Boolean Task Forum Associated.
RecurrenceSet Boolean Task Recurrence Set.
ReminderSet Boolean Task Reminder Set.
Parent Boolean Task Parent.
LastUpdatedDate Date Task Last Updated Date.
LastUpdatedDateTime Datetime Task Last Updated Date Time.
LinkSelfUrl String Task Link Self Url.
LinkTimesheetUrl String Task Link Timesheet Url.
LinkWebUrl String Task Link Web Url.
LogHoursBillableHours String Task Log Hours Billable Hours.
LogHoursNonBillableHours String Task Log Hours Non Billable Hours.
MilestoneId String Task Milestone Id.
OrderSequence Integer Task Order Sequence.
PercentComplete String Task Percent Complete.
Priority String Task Priority. The allowed values are none, low, medium, high.
StartDate Date Task Start Date.
StartDateTime Datetime Task Start Date Time.
StatusColorCode String Task Status Color Code.
StatusId String Task Status Id.
StatusName String Task Status Name.
StatusType String Task Status Type.
Subtasks Boolean Task Subtasks.
TaskFollowers String Task Task Followers.
TaskFollowerSize Integer Task Task Follower Size.
TasklistId String Task Tasklist Id.
TasklistIdString String Task Tasklist ID String.
TasklistName String Task Tasklist Name.
Work String Task Work.
WorkForm String Task Work Form.
WorkType String Task Work Type.
GroupNameAssociatedTeamsAnyTeam String Task Group Name Associated Teams Any Team.
GroupNameAssociatedTeamsCount Integer Task Group Name Associated Teams Count.
GroupNameIsTeamUnassigned Boolean Task Group Name Is Team Unassigned.
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.

Name Type Description
Owner String Task Owner.
Status String Task Status. The allowed values are completed, notcompleted.
Time String Task Time. The allowed values are overdue, today, tomorrow.
LastModifiedTime Datetime Task Last Modified Time.
CustomViewId String Task Custom View Id.

MyTimelogs

Gets the time logs under a specific bug.

Select

This is a portal-level view. Columns that support the = operator:

  • BillStatus
  • Date
  • User
  • ViewType
  • ComponentType

For example, the following query is processed server side:

SELECT * FROM MyTimelogs WHERE User = '123450000123'
Columns
Name Type Description
Id [KEY] String Time Log Id.
AddedByName String Time Log Added By Name.
AddedByZpuid String Time Log Added By Zpuid.
AddedByZuid Long Time Log Added By Zuid.
ApprovalStatus String Time Log Approval Status.
ApproverName String Time Log Approver Name.
BillStatus String Time Log Bill Status. The allowed values are all, billable, non billable.
CreatedDate Date Time Log Created Date.
CreatedDateTime Datetime Time Log Created Date Time.
StartTime Datetime Time Log Start Time.
EndTime Datetime Time Log End Time.
Notes String Time Log Notes.
HoursDisplay String Time Log Hours Display.
Hours Integer Time Log Hours.
Minutes Integer Time Log Minutes.
IsParent Boolean Time Log Is Parent.
IsSubTask Boolean Time Log Is Sub Task.
LastModifiedDate Date Time Log Last Modified Date.
LastModifiedDateTime Datetime Time Log Last Modified Date Time.
LinkSelfUrl String Time Log Link Self Url.
LogDate Date Time Log Date.
LogDateTime Datetime Time Log Date Time.
OwnerId String Time Log Owner Id.
OwnerName String Time Log Owner Name.
SubTaskLevel String Time Log Sub Task Level.
TaskName String Time Log Task Name.
TaskListId Long Time Log Task List Id.
TaskListName String Time Log Task List Name.
TotalMinutes Integer Time Log Total Minutes.
ProjectId String Time Log Project Id.
ProjectName String Time Log Project Name.
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.

Name Type Description
Date Date Time Log Date.
User String Time Log User.
ViewType String Time Log View Type. The allowed values are day, week, month.
ComponentType String Time Log Component Type. The allowed values are bug, task, general.

ProjectCustomFields

Gets all the project custom fields.

Select

You can use the below query to get all Project Custom Fields:

SELECT * FROM ProjectCustomFields
Columns
Name Type Description
Id [KEY] String Custom Field Id.
Name String Custom Field Name.
FieldType String Custom Field Type.
FieldId String Field Id.
PII Boolean Custom Field PII.
Encrypted Boolean Encrypted.
DefaultValue String Custom Field Default Value.
PickList String Custom Field Pick List Options.

ProjectCustomStatus

Gets list of all project custom status.

Select

You can use the below query to get all Project Custom Statuses:

SELECT * FROM ProjectCustomStatus
Columns
Name Type Description
Id [KEY] String Portal Id.
Name String Portal Name.
Closed Boolean Portal Closed.
Default Boolean Portal Default.
HasDefaultValue Boolean Portal Has Default Value.
Sequence Int Portal Sequence.
StatusColor String Portal Status Color.
StatusColorHexcode String Portal Status Color Hex Code.

ProjectLayouts

Gets list of project layouts.

Select

You can use the below query to get all Project Layouts:

SELECT * FROM ProjectLayouts
Columns
Name Type Description
Id [KEY] String Project Layout Id.
Name String Project Layout Name.
Default Boolean Is Default Project Layout.

Timelogs

Gets the time logs under a specific bug.

Select

This is a project-level view. Columns that support the = operator:

  • BillStatus
  • Date
  • User
  • ViewType
  • ComponentType

For example, the following query is processed server side:

SELECT * FROM ProjectLayouts
Columns
Name Type Description
Id [KEY] String Time Log Id.
AddedByName String Time Log Added By Name.
AddedByZpuid String Time Log Added By ZPUID.
AddedByZuid Long Time Log Added By ZUID.
ApprovalStatus String Time Log Approval Status.
ApproverName String Time Log Approver Name.
BillStatus String Time Log Bill Status. The allowed values are all, billable, non billable.
CreatedDate Date Time Log Created Date.
CreatedDateTime Datetime Time Log Created Date Time.
StartTime Datetime Time Log Start Time.
EndTime Datetime Time Log End Time.
Notes String Time Log Notes.
HoursDisplay String Time Log Hours Display.
Hours Integer Time Log Hours.
Minutes Integer Time Log Minutes.
IsParent Boolean Time Log Is Parent.
IsSubTask Boolean Time Log Is Sub Task.
LastModifiedDate Date Time Log Last Modified Date.
LastModifiedDateTime Datetime Time Log Last Modified Date Time.
LinkSelfUrl String Time Log Link Self Url.
LogDate Date Time Log Date.
LogDateTime Datetime Time Log Date Time.
OwnerId String Time Log Owner Id.
OwnerName String Time Log Owner Name.
SubTaskLevel String Time Log Sub Task Level.
TaskName String Time Log Task Name.
TaskListId Long Time Log Task List Id.
TaskListName String Time Log Task List Name.
TotalMinutes Integer Time Log Total Minutes.
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.

Name Type Description
Date Date Time Log Date.
User String Time Log User.
ViewType String Time Log View Type. The allowed values are day, week, month.
ComponentType String Time Log Component Type. The allowed values are bug, task, general.

Gets the time logs under a specific bug.

Select

This is a project-level view. Columns that support the = operator:

  • BillStatus
  • Date
  • User
  • ViewType
  • ComponentType

For example, the following query is processed server side:

SELECT * FROM ProjectLayouts
Columns
Name Type Description
Id [KEY] String Time Log Id.
AddedByName String Time Log Added By Name.
AddedByZpuid String Time Log Added By Zpuid.
AddedByZuid Long Time Log Added By Zuid.
ApprovalStatus String Time Log Approval Status.
ApproverName String Time Log Approver Name.
BillStatus String Time Log Bill Status. The allowed values are all, billable, non billable.
CreatedDate Date Time Log Created Date.
CreatedDateTime Datetime Time Log Created Date Time.
StartTime Datetime Time Log Start Time.
EndTime Datetime Time Log End Time.
Notes String Time Log Notes.
HoursDisplay String Time Log Hours Display.
Hours Integer Time Log Hours.
Minutes Integer Time Log Minutes.
IsParent Boolean Time Log Is Parent.
IsSubTask Boolean Time Log Is Sub Task.
LastModifiedDate Date Time Log Last Modified Date.
LastModifiedDateTime Datetime Time Log Last Modified Date Time.
LinkSelfUrl String Time Log Link Self Url.
LogDate Date Time Log Date.
LogDateTime Datetime Time Log Date Time.
OwnerId String Time Log Owner Id.
OwnerName String Time Log Owner Name.
SubTaskLevel String Time Log Sub Task Level.
TaskName String Time Log Task Name.
TaskListId Long Time Log Task List Id.
TaskListName String Time Log Task List Name.
TotalMinutes Integer Time Log Total Minutes.
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.

Name Type Description
Date Date Time Log Date.
User String Time Log User.
ViewType String Time Log View Type. The allowed values are day, week, month.
ComponentType String Time Log Component Type. The allowed values are bug, task, general.

TaskAttachments

Retrieve details of attachments associated to the task.

Select

This is a project-level view.

  • TaskId is required to retrieve TaskAttachments.

You can use the below query to get all TaskAttachments:

SELECT * FROM TaskAttachments WHERE TaskId = '123456000000112'
Columns
Name Type Description
TaskId String Task Id.
Id [KEY] String Task Attachment Id.
AuthorId String Task Attachment Author Id.
ContentType String Task Attachment Content Type.
DocsDownloadUrl String Task Attachment Docs Download Url.
DownloadUrl String Task Attachment Download.
Extension String Task Attachment Extension.
Filename String Task Attachment Filename.
Owner String Task Attachment Owner.
Size Integer Task Attachment size in bytes..
TaskId Long Task Attachment Task Id.
ThumbnailUrl String Task Attachment Thumbnail Url.
UploadedTime Long Task Attachment Uploaded Time.

TaskLayouts

View the list of all the task layouts available in your portal.

Select

This is a portal-level view. You can use the below query to get all Task Layouts:

SELECT * FROM TaskLayouts
Columns
Name Type Description
Id [KEY] String Task Layout Id.
Name String Task Layout Name.
Default Boolean Task Layout Default.

TaskStatusHistory

Fetches status history of tasks from the Portal.

Select

This is a portal-level view.

  • LastModifiedTime supports: ORDER

You can use the below query to get all Task Layouts:

SELECT * FROM TaskStatusHistory ORDER BY LastModifiedTime
Columns
Name Type Description
Id [KEY] String Task Status History Id.
Name String Task Status History Name.
StatusId String Task Status History Status Id.
StatusName String Task Status History Status Name.
PreviousStatusId String Task Status History Previous Status Id.
PreviousStatusName String Task Status History Previous Status Name.
UpdatedStatusId String Task Status History Updated Status Id.
UpdatedStatusName String Task Status History Updated Status Name.
TransitionTimeMillis String Task Status History Transition Time Millis.
TransitionTimeDuration String Task Status History Transition Time Duration.
UpdatedBy String Task Status History Updated By.
UpdatedByName String Task Status History Updated By Name.
UpdatedByEmail String Task Status History Updated By Email.
UpdatedOn Datetime Task Status History Updated On.
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.

Name Type Description
LastModifiedTime Datetime Task Status History Last Modified Time.

TimesheetCustomFields

Fetch all timesheet custom fields across the project.

Select

This is a portal-level view. You can use the below query to get all Timesheet Custom Fields:

SELECT * FROM TimesheetCustomFields
Columns
Name Type Description
Id [KEY] String Time Sheet Custom Field Id.
FieldName String Time Sheet Custom Field Name.
DataType String Time Sheet Custom Field Data Type.
UniqueColumn String Time Sheet Custom Field Unique Column.

PortalUsersRemaining

Gets the details of all users in a specific portal.

Select

This is a project-level view. You can use the below query to get the remaining number of users for your portal:

SELECT * FROM PortalUsersRemaining
Columns
Name Type Description
AvailableUsersCount String Available User Count.

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Zoho Projects.

Stored procedures accept a list of parameters, perform their intended function, and then return, if applicable, any relevant response data from Zoho Projects, along with an indication of whether the procedure succeeded or failed.

Zoho Projects Connector Stored Procedures

Name Description
AddUserToClientCompany Adds user to an existing client company.
ApproveTimelog Approve general/bug/task time log.
AssociateBugs Associate related bugs to a task.
AssociateContacts Associates a tag with a specific entity in a specific project.
AssociateTags Associates a tag with a specific entity in a specific project.
DissociateBugs Associate related bugs to a task.
DissociateContacts Dissociate a client contact from a project.
DissociateTags Dissociates a tag with a specific entity in a specific project.
ForumFollow Follow a forum post.
ForumSelectBestAnswer Unfollow a forum post.
ForumUnfollow Unfollow a forum post.
ForumUnselectBestAnswer Unfollow a forum post.
GetOAuthAccessToken Gets an authentication token from ZohoProjects.
GetOAuthAuthorizationURL Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with ZohoProjects.
SelectTeamLead Update the team lead.
TaskFollow Follow a task in the given project.
TaskMove Moves a task from one tasklist to another tasklist. The target tasklist can be of the same project or different project.
TaskReorder Reorders the tasks in the given project. Task will be reordered in-between two tasks based on the previous and next task IDs.
TaskUnfollow Unfollow a task in the given project.

AddUserToClientCompany

Adds user to an existing client company.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC AddUserToClientCompany PortalId = '12345317133', CompanyId = '123456000000039025', ContactEmail = 'test@example.com'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
CompanyId String True Specify the client company ID.
ContactEmail String True Email ID of the client user.
WorkProjects String False Multiple project IDs are separated using comma.
ProfileId String False Specify Profile ID. profile_id is obtained from Get Portal Users API.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

ApproveTimelog

Approve general/bug/task time log.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC ApproveTimelog PortalId = '12345317133', ProjectId = '123456000000031899', LogId = '123456000000031977', Approval = 'approve', Reason = 'Submitted timelog is approved', EntityType = 'general', EntityId = '1234560001'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
LogId String True Specify the log ID.
EntityType String False Specify the entity type to approve the log time. The allowed values are task, bug, general.
EntityId String False ID of entity specified by parameter 'EntityType'
Approval String True The time log is approved if the given value is approve The allowed values are approve, pending, reject.
Reason String False Reason should be mentioned if the time log is rejected. The reason shouldn't exceed 250 characters.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

AssociateBugs

Associate related bugs to a task.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC AssociateBugs PortalId = '12345317133', ProjectId = '123456000000031899', TaskId = '123456000000031977', BugId = '123456000000034087'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
TaskId String True Specify the task ID.
BugId String True ID for multiple bugs must be separated by commas.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

AssociateContacts

Associates a tag with a specific entity in a specific project.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC AssociateContacts PortalId = '12345317133', ProjectId = '123456000000031899', ContactId = '1234560000000123456'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
ContactId String True Array of client contact IDs. Multiple IDs can be separated by comma.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

AssociateTags

Associates a tag with a specific entity in a specific project.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC AssociateTags PortalId = '12345317133', ProjectId = '123456000000031899', TagId = '123456000000031977', EntityType = 'Task',  EntityId = '123456000000034087'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
TagId String True Specify the tag ID.
EntityType String True Specify the entity type to approve the log time. The allowed values are Project, Milestone, Tasklist, Task, Bug, Forum, Status.
EntityId String True ID of entity specified by parameter 'EntityType'
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

DissociateBugs

Associate related bugs to a task.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC DissociateBugs PortalId = '12345317133', ProjectId = '123456000000031899', TaskId = '123456000000031977', BugId = '123456000000034087'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
TaskId String True Specify the task ID.
BugId String True ID for multiple bugs must be separated by commas.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

DissociateContacts

Dissociate a client contact from a project.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC DissociateContacts PortalId = '12345317133', ProjectId = '123456000000031899', ContactId = '1234560000000123456'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
ContactId String True Array of client contact IDs.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

DissociateTags

Dissociates a tag with a specific entity in a specific project.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC DissociateTags PortalId = '12345317133', ProjectId = '123456000000031899', TagId = '123456000000031977', EntityType = 'Task',  EntityId = '123456000000034087'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
TagId String True Specify the tag ID.
EntityType String True Specify the entity type to approve the log time. The allowed values are Project, Milestone, Tasklist, Task, Bug, Forum, Status.
EntityId String True ID of entity specified by parameter 'EntityType'
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

ForumFollow

Follow a forum post.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC ForumFollow PortalId = '12345317133', ProjectId = '123456000000031899', ForumId = '123456000000034053'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
ForumId String True Specify the tag ID.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

ForumSelectBestAnswer

Unfollow a forum post.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC ForumSelectBestAnswer PortalId = '12345317133', ProjectId = '123456000000031899', ForumId = '123456000000034053', CommentId = '123456000000039021'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
ForumId String True Specify the forum ID.
CommentId String True Specify the comment ID.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

ForumUnfollow

Unfollow a forum post.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC ForumUnfollow PortalId = '12345317133', ProjectId = '123456000000031899', ForumId = '123456000000034053'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
ForumId String True Specify the tag ID.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

ForumUnselectBestAnswer

Unfollow a forum post.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC ForumUnselectBestAnswer PortalId = '12345317133', ProjectId = '123456000000031899', ForumId = '123456000000034053', CommentId = '123456000000039021'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
ForumId String True Specify the forum ID.
CommentId String True Specify the comment ID.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

GetOAuthAccessToken

Gets an authentication token from ZohoProjects.

Input
Name Type Required Description
AuthMode String False The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app. The allowed values are APP, WEB. The default value is APP.
Scope String False A comma-separated list of permissions to request from the user. Please check the ZohoProjects API for a list of available permissions. The default value is ZohoProjects.portals.ALL,ZohoProjects.projects.ALL,ZohoProjects.status.ALL,ZohoProjects.milestones.ALL,ZohoProjects.tasklists.ALL,ZohoProjects.tasks.ALL,ZohoProjects.tags.ALL,ZohoProjects.bugs.ALL,ZohoProjects.events.ALL,ZohoProjects.forums.ALL,ZohoProjects.users.ALL,ZohoProjects.clients.ALL,ZohoProjects.documents.ALL,ZohoProjects.search.READ,ZohoProjects.entity_properties.ALL,ZohoProjects.activities.READ,ZohoProjects.timesheets.ALL.
CallbackUrl String False The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the ZohoProjects app settings. Only needed when the Authmode parameter is Web.
Verifier String False The verifier returned from ZohoProjects after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL.
State String False Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the ZohoProjects authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
AccountsServer String False The full Account Server URL that will be used to retrieve the OAuth Access Token.
Result Set Columns
Name Type Description
OAuthAccessToken String The access token used for communication with ZohoProjects.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token. A -1 denotes that it will not expire.
AccountsServer String The full Account Server URL.
APIDomain String The full URL of the API domain.

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.

Input
Name Type Required Description
CallbackUrl String False The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the ZohoProjects app settings.
Scope String False A comma-separated list of scopes to request from the user. Please check the ZohoProjects API documentation for a list of available permissions. The default value is ZohoProjects.portals.ALL,ZohoProjects.projects.ALL,ZohoProjects.status.ALL,ZohoProjects.milestones.ALL,ZohoProjects.tasklists.ALL,ZohoProjects.tasks.ALL,ZohoProjects.tags.ALL,ZohoProjects.bugs.ALL,ZohoProjects.events.ALL,ZohoProjects.forums.ALL,ZohoProjects.users.ALL,ZohoProjects.clients.ALL,ZohoProjects.documents.ALL,ZohoProjects.search.READ,ZohoProjects.entity_properties.ALL,ZohoProjects.activities.READ,ZohoProjects.timesheets.ALL.
State String False Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the ZohoProjects authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Result Set Columns
Name Type Description
URL String The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with ZohoProjects.

Input
Name Type Required Description
OAuthRefreshToken String True The refresh token returned from the original authorization code exchange.
AccountsServer String False The full Account Server URL that will be used to retrieve a new access token.
Result Set Columns
Name Type Description
OAuthAccessToken String The access token returned from ZohoProjects. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.

SelectTeamLead

Update the team lead.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC SelectTeamLead PortalId = '12345317133', ProjectId = '123456000000031899', UserId = '123456000000033053'
Input
Name Type Required Description
PortalId String False Specify the portal ID.
TeamId String False Specify the group ID.
UserId String False ZPUID of the team lead.
Result Set Columns
Name Type Description
Success String Specifies whether the update was successful.

TaskFollow

Follow a task in the given project.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC TaskFollow PortalId = '12345317133', ProjectId = '123456000000031899', TaskId = '123456000000034053', UserId = '123456000000039021'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
TaskId String True Specify the task ID.
UserId String True User ID for multiple followers must be separated by commas.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

TaskMove

Moves a task from one tasklist to another tasklist. The target tasklist can be of the same project or different project.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC TaskMove PortalId = '12345317133', ProjectId = '123456000000031899', TaskId = '123456000000034053', FromTasklist = '123456000000031932', ToTasklist = '123456000000031941'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
TaskId String True Specify the task ID.
FromTasklist String True ID of the tasklist in which the task exists.
ToTasklist String True ID of the tasklist to which the task has to be moved.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

TaskReorder

Reorders the tasks in the given project. Task will be reordered in-between two tasks based on the previous and next task IDs.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC TaskReorder PortalId = '12345317133', ProjectId = '123456000000031899', TaskId = '123456000000034053', PreviousTaskId = '123456000000031932', NextTaskId = '123456000000031941'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
TaskId String True Specify the Task ID.
PreviousTaskId String True Specify the previous Task ID.
NextTaskId String True Specify the next Task ID.
Result Set Columns
Name Type Description
Success String Specifies whether the update was successful.

TaskUnfollow

Unfollow a task in the given project.

Procedure-Specific Information

An example query for executing this stored procedure is shown below:

EXEC TaskUnfollow PortalId = '12345317133', ProjectId = '123456000000031899', TaskId = '123456000000034053', UserId = '123456000000039021'
Input
Name Type Required Description
PortalId String True Specify the portal ID.
ProjectId String True Specify the group ID.
TaskId String True ZPUID of the team lead.
UserId String True User ID for multiple followers must be separated by commas.
Result Set Columns
Name Type Description
Success String Specifies whether the execution was successful.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Zoho Projects:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Portals table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Portals'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the ApproveTimelog stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='ApproveTimelog' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Portals table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Portals'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:zohoprojects:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Property Description
Region The Top-level domain in the Server URL.

Connection

Property Description
PortalID Restrict exposed entities based on Portal ID. If left empty all available portals will be exposed.
ProjectID Restrict exposed entities based on Project ID. If left empty all available portals will be exposed.

OAuth

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, this can be held in memory by specifying a value starting with memory://.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property Description
AccountsServer The full Account Server URL.
APIDomain The full URL of the API domain.
MaxRows Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Zoho Projects.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
Region The Top-level domain in the Server URL.

Region

The Top-level domain in the Server URL.

Possible Values

US, Europe, India, Australia

Data Type

string

Default Value

US

Remarks

If your account resides in a different domain, then the Region should change accordingly.

Region Domain
US .com
Europe .eu
India .in
Australia .com.au

Connection

This section provides a complete list of connection properties you can configure.

Property Description
PortalID Restrict exposed entities based on Portal ID. If left empty all available portals will be exposed.
ProjectID Restrict exposed entities based on Project ID. If left empty all available portals will be exposed.

PortalID

Restrict exposed entities based on Portal ID. If left empty all available portals will be exposed.

Data Type

string

Default Value

""

Remarks

Restrict exposed entities based on Portal ID. If left empty all available portals will be exposed.

ProjectID

Restrict exposed entities based on Project ID. If left empty all available portals will be exposed.

Data Type

string

Default Value

""

Remarks

Restrict exposed entities based on Project ID. If left empty all available portals will be exposed.

OAuth

This section provides a complete list of OAuth properties you can configure.

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, this can be held in memory by specifying a value starting with memory://.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

OFF

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

OAuthAccessToken

The access token for connecting using OAuth.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, this can be held in memory by specifying a value starting with memory://.

Data Type

string

Default Value

%APPDATA%\ZohoProjects Data Provider\OAuthSettings.txt

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the connector saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and allowing the credentials to be shared across connections or processes.

Alternatively to specifying a file path, memory storage can be used instead. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (ex: memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike with the file based storage, you must manually store the credentials when closing the connection with memory storage to be able to set them in the connection when the process is started again. The OAuth property values can be retrieved with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties should be read from the last connection to be closed.

If left unspecified, the default location is "%APPDATA%\ZohoProjects Data Provider\OAuthSettings.txt" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

CallbackURL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.

Data Type

string

Default Value

""

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH to automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthRefreshToken

The OAuth refresh token for the corresponding OAuth access token.

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\ZohoProjects Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\ZohoProjects Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
AccountsServer The full Account Server URL.
APIDomain The full URL of the API domain.
MaxRows Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Zoho Projects.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

AccountsServer

The full Account Server URL.

Data Type

string

Default Value

""

Remarks

You only need to supply this when executing the RefreshOAuthAccessToken stored procedure with InitiateOAuth=Off. Most of the time, the value of this property will be https://accounts.zoho.com/, but if your account resides in a different location, then the domain should change accordingly (.eu, .in, .com.au, ...).

APIDomain

The full URL of the API domain.

Data Type

string

Default Value

""

Remarks

You only need to supply this when using your own OAuth access token with InitiateOAuth=Off. Otherwise, the API domain will be retrieved from the OAuth flow. Most of the time, the value of this property will be https://www.zohoapis.com, but if your account resides in a different location, then the domain should change accordingly (.eu, .in, .com.au, ...).

MaxRows

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from Zoho Projects.

Data Type

int

Default Value

100

Remarks

The Pagesize property affects the maximum number of results to return per page from Zoho Projects. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Portals WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"