Zoho Projects Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 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

Connect to Zoho Projects

Set Region to the Top Level Domain (TLD) in the server URL.

Authenticate to Zoho Projects

Zoho Projects provides two ways to connect to data:

• To connect to your own local data via the desktop (non-browser connection), use the

  -supplied embedded OAuth application.

• To connect to shared data over the network (browser connection), use a custom OAuth application.

To enable this authentication from all OAuth flows (embedded or custom), you must set AuthScheme to OAuth.

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

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

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

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

Desktop Applications

provides an embedded OAuth application that simplifies authentication at the desktop.

You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the Zoho Projects console. For further information, see Creating a Custom OAuth Application.

Before you connect, set the following variables:

• InitiateOAuth: GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
• Custom OAuth applications only:
  • OAuthClientId: The client ID assigned when you registered your custom OAuth application.
  • OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
  • CallbackURL: The redirect URI defined when you registered your custom OAuth application.

When you connect, the connector opens Zoho Projects's OAuth endpoint in your default browser. Log in and grant permissions to the application.

After you grant permissions to the application, the connector completes the OAuth process:

1. The connector obtains an access token from Zoho Projects and uses it to request data.
2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes it automatically.

Automatic refresh of the OAuth access token:

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

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

Manual refresh of the OAuth access token:

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

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set the following connection properties:
   • OAuthClientId: The Client ID in your application settings.
   • OAuthClientSecret: The Client Secret in your application settings.
3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

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

Create a Custom OAuth Application

Create a Custom OAuth Application

embeds OAuth Application Credentials with branding that can be used when connecting to Zoho Projects via a desktop application or a headless machine. If you want to use the embedded OAuth application, all you need to do to connect is to:

• set AuthScheme to OAuth,
• get and set the OAuthAccessToken, and
• set the necessary configuration parameters.

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Establishing a Connection".)

However, you must create a custom OAuth application to connect to Zoho Projects via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog;
• control the redirect URI that the application redirects the user to after the user authenticates; or
• customize the permissions that you are requesting from the user.

Procedure

To create a custom OAuth application and obtain the OAuthClientId, OAuthClientSecret, and CallbackURL:

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 the user will connect via either desktop or Web, set a callback URL:
   • For desktop applications, set the callback URL to http://localhost:33333, or another port number of your choice.
   • For Web applications, set a callback URL to specify what page the user should land on after they grant your application access.
5. Click Create.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit 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

The connector exposes all of the portals and projects tied to your Zoho Projects account, so you can access all of your Zoho Projects data with a single connection.

You can access:

• portal-specific data
• project-specific data
• data pertaining to all portals

User Portals

The connector models each portal associated with your account as a catalog, named after the portal, containing a portal-specific schema called "ZohoProjects".

The User Portals section is a generic example of such a portal-specific schema.

User Projects

The connector models each portal associated with your account as a catalog, named after the portal. For every project in each of these catalogs, there is a project-specific schema, which is named after the project.

The User Projects section is a generic example of such a project-specific schema.

All Portals

The All Portals section contains data pertaining to all portals tied to your account.

User Portals

Overview

The connector models each portal associated with your account as a catalog, named after the portal, containing a portal-specific schema called "ZohoProjects".

Tables

Tables describes the available tables. Tables are statically defined to model Zoho Projects entities such as contacts, teams, projects, etc.

Views

Views describes the available views. Views are read-only tables that are statically defined to model Zoho Projects entities such as project and task layouts.

Tables

The connector models the entities for which Zoho Projects provides full or limited CRUD support as tables.

It models each portal tied to your account as a schema with the following tables.

Navigate to an individual table's page for a breakdown of its columns, as well as its supported filters and CRUD operations.

Zoho Projects Connector Tables

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

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

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

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

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.

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

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.

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

Users

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

Columns

Pseudo-Columns

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

Views

The connector models the entities for which Zoho Projects only provides read-only access as views.

It exposes each portal tied to your account as a schema with the following views.

Navigate to an individual view's page for a breakdown of its columns and supported filters.

Zoho Projects Connector Views

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

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.

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

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.

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

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.

MyTimelogs

Gets the time logs under a specific bug.

Select

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

• BillStatus
• User
• ComponentType

Columns that support the >,>=,=,<,<= operators:

• Date
• LastModifiedDate

For example, the following queries are processed server side:

SELECT * FROM MyTimelogs WHERE User = '123450000123'

SELECT * FROM MyTimelogs WHERE Date > '2024-01-01'

SELECT * FROM MyTimelogs WHERE LastModifiedDate > '2024-01-01'

Querying MyTimeLogs without filters will return data for only the current month:

SELECT * FROM MyTimelogs

Columns

ProjectCustomFields

Gets all the project custom fields.

Select

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

SELECT * FROM ProjectCustomFields

Columns

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

ProjectLayouts

Gets list of project layouts.

Select

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

SELECT * FROM ProjectLayouts

Columns

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

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

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.

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

PortalUsersRemaining

Gets the details of all users in a specific portal.

Columns

User Projects

Overview

The connector models each portal associated with your account as a catalog, named after the portal. For every project in each of these catalogs, there is a project-specific schema, which is named after the project.

Tables

Tables describes the available tables. Tables are statically defined to model Zoho Projects entities such as bugs, events, tasks, etc.

Views

Views describes the available views. Views are read-only tables that are statically defined to model Zoho Projects entities such as activities, clients, documents, etc.

Tables

The connector models the entities for which Zoho Projects provides full or limited CRUD support as tables.

It models each project tied to your account as a schema with the following tables.

Navigate to an individual table's page for a breakdown of its columns, as well as its supported filters and CRUD operations.

Zoho Projects Connector Tables

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

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

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

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

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

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.

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

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

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.

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

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

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

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

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.

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

ProjectUsers

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

Columns

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

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

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

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.

TaskCustomfields

Usage information for the operation TaskCustomfields.rsd.

Columns

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

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

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.

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

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.

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

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

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