Zoho Projects Connection Details¶

Introduction¶

Connector Version

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

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; that is, in situations where the user is using a local server not connected to the internet.

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

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
• Date
• User
• ViewType
• ComponentType

For example, the following query is processed server side:

SELECT * FROM MyTimelogs WHERE User = '123450000123'

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.

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¶