This page provides a guide to Establishing a Connection to Zoho Projects in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Zoho Projects

Establishing a Connection shows how to authenticate to Zoho Projects and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Zoho Projects through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Zoho Projects by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting 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 CData-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.

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

Automatic refresh of the OAuth access token:

To have the Cloud 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 Cloud 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.

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

Registering 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 and OAuthClientSecret connection properties.

1. Navigate to the Zoho Developer Console.
2. Click Add Client Id, and enter the following application information:
   • Set the Client Name to a new name for this connection.
   • Set the Client Domain to the domain of your Application.
   • Set the Authorized Redirect URI to https://oauth.cdata.com/oauth/.
3. Click Create to save the new application.

Connect to Your Custom Application

After creating your custom app, set the following connection properties in order to establish a connection, and click Connect:

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

Overview

The Cloud 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 Cloud 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 Cloud 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.

Overview

The Cloud 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.

The Cloud 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.

CData Cloud - Zoho Projects Tables

Fetches all client contacts from a portal.

Select

• ClientId is required to retrieve Contacts.

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', '[email protected]', '15')

Update

To update a Contact specify the Id field.

UPDATE Contacts
	SET FirstName = 'updatedname', LastName = 'updatedlastname', email = '[email protected]'
	WHERE Id = '166135000000038075'

Delete

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

DELETE FROM Contacts WHERE Id = '123456000000040053' 

Columns

Returns the list of client companies in the portal.

Select

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', '[email protected]', 'www.example.com', 'Address line 1', 'Address line 2', 'Budapest', 'Hungary', 'Hungary', 'Yes', '1025')

Columns

Gets all the groups for the specified project.

Select

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

Gets all the projects for the logged in user.

Select

• Id
• CreatedDate
• Status
• LastModifiedTime

• GroupId
• Assignee

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.

Fetches all the tags present in a specific portal.

Select

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.

Fetch the team details from the portal.

Select

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

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.

The Cloud 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.

CData Cloud - Zoho Projects Views

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

Select

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

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.

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

Select

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

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.

Gets all your tasks in the portal.

Select

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

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.

Gets the time logs under a specific bug.

Select

• BillStatus
• Date
• User
• ViewType
• ComponentType

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.

Gets all the project custom fields.

Select

SELECT * FROM ProjectCustomFields

Columns

Gets list of all project custom status.

Select

SELECT * FROM ProjectCustomStatus

Columns

Gets list of project layouts.

Select

SELECT * FROM ProjectLayouts

Columns

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

Select

SELECT * FROM TaskLayouts

Columns

Fetches status history of tasks from the Portal.

Select

• LastModifiedTime supports: ORDER

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.

Fetch all timesheet custom fields across the project.

Select

SELECT * FROM TimesheetCustomFields

Columns

Gets the details of all users in a specific portal.

Columns

Overview

The Cloud 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.

The Cloud 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.

CData Cloud - Zoho Projects Tables

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: =.

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

Fetch comments for the bug.

Select

• BugId is required to retrieve Bug Comments.

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

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.

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

Get list of followers for the bug.

Select

• BugId is required to retrieve Bug Followers.

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

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

Select

• Id
• CreatedTime
• Flag
• StatusType
• CustomViewId

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

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.

Gets the time logs under a specific bug.

Select

• BugId is required to retrieve Bug Time Logs.

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

Fetches all the tags present in a specific portal.

Select

• Status supports the following operator =.

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.

Gets all the forum categories.

Select

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

Gets all the forum comments.

Select

• ForumId is required to retrieve 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

Gets all the forums in the given project.

Select

• Id and CategoryId support the following operator: =.

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

Gets list of project layouts.

Select

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

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.

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.

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

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

Columns

Gets the statuses for the given project.

Select

SELECT * FROM Statuses

Insert

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

INSERT INTO Statuses (Content) VALUES ('My Custom Status')

Columns

Retrieve details of who modified a specific task and when.

Select

• TaskId is required to retrieve Forum Comments.

SELECT * FROM TaskActivities WHERE TaskId = '123456789' 

Columns

Get all the task comments.

Select

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

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.

Usage information for the operation TaskCustomfields.rsd.

Columns

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.

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

Get all the task lists in the given project.

Select

• MilestoneId and Flag support the following operator: =.

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.

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

Select

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

• CreatedDate and LastModifiedTime support: ORDER

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.

View all the subtasks of the given task.

Select

• TaskId is required to retrieve 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

Gets the time logs under a specific task.

Select

• TaskId is required to retrieve Task Time Logs.

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