このページでは、CData Cloud でのZoho Projects への接続の確立 のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

Zoho Projects への接続

接続の確立 は、CData Cloud にデータベースを作成するためのZoho Projects への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してZoho Projects からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメント で詳しく説明します。

Database タブで対応するアイコンを選択して、Zoho Projects に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

Zoho Projects への接続

Zoho Projects は、データに接続する2つの方法を提供します。

• デスクトップ経由で自分のローカルデータに接続する場合（非ブラウザ接続）は、CData が提供する埋め込みOAuth アプリケーションを使用します。
• ネットワーク経由で共有データに接続する場合（ブラウザ接続）は、カスタムOAuth アプリケーションを使用します。

すべてのOAuth フロー（埋め込みまたはカスタム）で、この認証を有効にするにはAuthScheme をOAuth に設定する必要があります。

以下のサブセクションでは、3つの一般的な認証フローでのZoho Projects への認証について詳しく説明します。

• デスクトップ：ユーザーのローカルマシン上でのサーバーへの接続で、テストやプロトタイピングによく使用されます。埋め込みOAuth またはカスタムOAuth で認証されます。
• Web：共有ウェブサイト経由でデータにアクセスします。カスタムOAuth でのみ認証されます。
• ヘッドレスサーバー：他のコンピュータやそのユーザーにサービスを提供する専用コンピュータで、モニタやキーボードなしで動作するように構成されています。埋め込みOAuth またはカスタムOAuth で認証されます。

カスタムOAuth アプリケーションの作成についての情報と、埋め込みOAuth 認証情報を持つ認証フローでもカスタムOAuth アプリケーションを作成したほうがよい場合の説明については、カスタムOAuth アプリケーションの作成 を参照してください。

Zoho Projects で利用可能な接続文字列プロパティの全リストは、Connection を参照してください。

アクセストークンの期限が切れたときは、Cloud は自動でアクセストークンをリフレッシュします。

OAuth アクセストークンの自動リフレッシュ：

Cloud がOAuth アクセストークンを自動的にリフレッシュするようにするには、次のように設定します。

1. はじめてデータに接続する際、次の接続プロパティを設定します。
   • InitiateOAuth：REFRESH。
   • OAuthClientId：アプリケーション設定のクライアントId。
   • OAuthClientSecret：アプリケーション設定のクライアントシークレット。
   • OAuthAccessToken：GetOAuthAccessToken によって返されたアクセストークン。
   • OAuthSettingsLocation：Cloud がOAuth 値を保存する場所のパス。これは接続間で維持されます。
2. その後のデータ接続では、以下を設定します。
   • InitiateOAuth
   • OAuthSettingsLocation

OAuth アクセストークンの手動リフレッシュ：

OAuth アクセストークンを手動でリフレッシュするために必要な唯一の値は、OAuth リフレッシュトークンです。

1. ExpiresIn 期間（GetOAuthAccessToken が返す）が経過した後にOAuthAccessToken を手動でリフレッシュするには、RefreshOAuthAccessToken ストアドプロシージャを呼び出します。
2. 次の接続プロパティを設定します。
   • OAuthClientId：アプリケーション設定のクライアントId。
   • OAuthClientSecret：アプリケーション設定のクライアントシークレット。

3. RefreshOAuthAccessToken を呼び出し、OAuthRefreshToken にGetOAuthAccessToken によって返されたOAuth リフレッシュトークンを設定します。
4. 新しいトークンが取得できたら、OAuthAccessToken プロパティにRefreshOAuthAccessToken によって返された値を設定します。これで新規接続が開かれます。

OAuth リフレッシュトークンを保存し、OAuth アクセストークンの有効期限が切れた後に手動でリフレッシュできるようにします。

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

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

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 テーブル

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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 ビュー

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Gets the time logs under a specific bug.

Select

• BillStatus
• Date
• User
• ViewType
• ComponentType

SELECT * FROM MyTimelogs WHERE User = '123450000123'

Columns

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

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

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 テーブル

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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