このページでは、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 への接続

Region をサーバーURL のトップレベルドメイン（TLD）に設定します。

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

Name	Description
Contacts	Fetches all client contacts from a portal.
PortalClients	Returns the list of client companies in the portal.
ProjectGroups	Gets all the groups for the specified project.
Projects	Gets all the projects for the logged in user.
Tags	Fetches all the tags present in a specific portal.
Teams	Fetch the team details from the portal.
Users	Gets the details of all the users in a specific project.

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', '[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

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

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

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Client Id.
Name	String	False	Client Name.
City	String	False	Client City.
Country	String	False	Client Country.
State	String	False	Client State.
CrmAccountId	String	False	Client Crm Account Id.
FirstAddress	String	False	Client First Address.
SecondAddress	String	False	Client Second Address.
WebAddress	String	False	Client Web Address.
ZipCode	String	False	Client Zip Code.
UserId	String	False	Client User Id.
UserName	String	False	Client User Name.
UserEmail	String	False	Client User Email.
UserZpuid	String	False	Client User Zpuid.
WorkProjects	String	False	Client Work Projects.
PrimaryClient	String	False	Client Primary Client. 使用できる値は次のとおりです。Yes, No

Gets all the groups for the specified project.

Select

You can use the below query to get all ProjectGroups:

SELECT * FROM ProjectGroups

Insert

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

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

Delete

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

DELETE FROM ProjectGroups WHERE Id = '123456000000045018'  

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Project Group Id.
Name	String	False	Project Group Name.
Default	Boolean	False	Is Default Project Group.

Gets all the projects for the logged in user.

Select

Columns that support the = operator:

• Id
• CreatedDate
• Status
• LastModifiedTime

Columns that support the = and IN operator:

• GroupId
• Assignee

For example, the following query is processed server side:

SELECT * FROM Projects WHERE Id = '123456000000039053' 

Insert

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

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

To create a new Project specify the following fields:

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

Update

You can use the below query to update a Project:

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

Delete

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

DELETE FROM Projects WHERE Id = '123456000000039053' 

Columns

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

Pseudo-Columns

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

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

Fetches all the tags present in a specific portal.

Select

You can use the below query to get all Tags:

SELECT * FROM Tags

Insert

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

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

Update

To update a Tag specify the Id field.

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

Delete

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

DELETE FROM Tags WHERE Id = '123456000000043057' 

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Tag Id.
Name	String	False	Tag Name.
ColorClass	String	False	Tag Color Class.
CreatedBy	String	False	Tag Created By.
CreatedByName	String	False	Tag Created By Name.
CreatedByEmail	String	False	Tag Created By Email.
CreatedByFirstName	String	False	Tag Created By First Name.
CreatedByLastName	Unknown	False	Tag Created By Last Name.
CreatedByIsClientUser	Boolean	False	Tag Created By Is Client User.
CreatedByZpuid	String	False	Tag Created By Zpuid.

Pseudo-Columns

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

Name	Type	Description
Tags	String

Fetch the team details from the portal.

Select

You can use the below query to get all Teams:

SELECT * FROM Teams

Insert

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

• GroupName
• EmailAlias
• UserIdArr
• ProjIds
• TeamLead

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

Update

To update a Team specify the GroupId field.

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

Delete

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

DELETE FROM Teams WHERE GroupId = '123456000000044025' 

Columns

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

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

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	User Id.
Name	String	False	User Name.
Active	Boolean	False	User Active.
ChatAccess	Boolean	False	User Chat Access.
Email	String	False	User Email.
PortalProfileName	String	False	User Portal Profile Name.
PortalRoleId	String	False	User Portal Role Id.
PortalRoleName	String	False	User Portal Role Name.
ProfileId	String	False	User Profile Id.
ProfileType	String	False	User Profile Type.
ProjectProfileId	String	False	User Project Profile Id.
Role	String	False	User Role. 使用できる値は次のとおりです。manager, employee, contractor
RoleId	String	False	User Role Id.
RoleName	String	False	User Role Name.
Zpuid	String	False	User Zpuid.
WorkProjects	String	False	User Work Projects.

Pseudo-Columns

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

Name	Type	Description
UserType	String	User Type. 使用できる値は次のとおりです。active, inactive

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

Name	Description
MyBugs	Lists all the bugs created by you or assigned to you.
MyMilestones	Get all the milestones assigned to a particular user in the given project.
MyTasks	Gets all your tasks in the portal.
MyTimelogs	Gets the time logs under a specific bug.
ProjectCustomFields	Gets all the project custom fields.
ProjectCustomStatus	Gets list of all project custom status.
ProjectLayouts	Gets list of project layouts.
TaskLayouts	View the list of all the task layouts available in your portal.
TaskStatusHistory	Fetches status history of tasks from the Portal.
TimesheetCustomFields	Fetch all timesheet custom fields across the project.
PortalUsersRemaining	Gets the details of all users in a specific portal.

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

Select

This is a portal-level view.

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

For example, the following query is processed server side:

SELECT * FROM MyBugs WHERE Owner = '12345021738' AND ViewType = 'owned' AND StatusType = 'open' ORDER BY CreatedDate

Columns

Name	Type	Description
Id [KEY]	String	Bug Id.
Key	String	Bug Key.
Title	String	Bug Title.
AssigneeId	String	Bug Assignee Id.
AssigneeName	String	Bug Assignee Name.
AssigneeZpuid	String	Bug Assignee ZPUID.
AttachmentCount	String	Bug Attachment Count.
BugPrefix	String	Bug Prefix.
ClassificationId	Long	Bug Classification Id.
ClassificationType	String	Bug Classification Type.
Closed	Boolean	Bug Closed.
CommentCount	String	Bug Comment Count.
CreatedDate	Date	Bug Created Date.
CreatedDateTime	Datetime	Bug Created Date Time.
Description	String	Bug Description.
DueDate	Date	Bug Due Date.
DueDateTime	Date	Bug Due Date Time.
Flag	String	Bug Flag.
LinkSelfUrl	String	Bug Link Self Url.
LinkTimesheetUrl	String	Bug Link Timesheet Url.
ModuleId	Long	Bug Module Id.
ModuleName	String	Bug Module Name.
ProjectId	Long	Bug Project Id.
ProjectIdString	String	Bug Project Id String.
ProjectName	String	Bug Project Name.
ReportedPerson	String	Bug Reported Person.
ReporterId	String	Bug Reporter Id.
ReproducibleId	Long	Bug Reproducible Id.
ReproducibleType	String	Bug Reproducible Type.
SeverityId	Long	Bug Severity Id.
SeverityType	String	Bug Severity Type.
StatusId	Long	Bug Status Id.
StatusType	String	Bug Status Type. 使用できる値は次のとおりです。open, closed
UpdatedDate	Date	Bug Updated Date.
UpdatedDateTime	Datetime	Bug Updated Date Time.
UpdatedTimeLong	Long	Bug Updated Time Long.

Pseudo-Columns

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

Name	Type	Description
Owner	String	Bug Owner.
ViewType	String	Bug View Type. 使用できる値は次のとおりです。reported, owned
LastModifiedTime	Datetime	Bug Last Modified Time.

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

Select

This is a portal-level view.

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

For example, the following query is processed server side:

SELECT * FROM MyMilestones WHERE ProjectId IN ('12345000000030899', '12345000000033005') AND Flag = 'internal' ORDER BY CreatedDate

Columns

Name	Type	Description
ProjectId	String	Project Id.
Id [KEY]	String	Milestone Id.
Name	String	Milestone Name.
Closed	Boolean	Milestone Closed.
CreatedDate	Date	Milestone Created Date.
CreatedDateTime	Datetime	Milestone Created Date Time.
EndDate	Date	Milestone End Date.
EndDateTime	Datetime	Milestone End Date Time.
Flag	String	Milestone Flag. 使用できる値は次のとおりです。internal, external
IsWorkfieldRemoved	Boolean	Milestone Is Work Field Removed.
LastUpdatedDate	Date	Milestone Last Updated Date.
LastUpdatedDateTime	Datetime	Milestone Last Updated Date Time.
SelfUrl	String	Milestone Self Url.
StatusUrl	String	Milestone Status Url.
OwnerId	String	Milestone Owner Id.
OwnerName	String	Milestone Owner Name.
OwnerZpuid	String	Milestone Owner ZPUID.
ProjectName	String	Milestone Project Name.
Sequence	Integer	Milestone Sequence.
StartDate	Date	Milestone Start Date.
StartDateTime	Datetime	Milestone Start Date Time.
Status	String	Milestone Status.
StatusColorCode	String	Milestone Status Color Code.
StatusId	String	Milestone Status Id.
StatusName	String	Milestone Status Name.

Pseudo-Columns

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

Name	Type	Description
LastModifiedTime	Datetime	Milestone Last Modified Time.

Gets all your tasks in the portal.

Select

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

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

For example, the following query is processed server side:

SELECT * FROM MyTasks WHERE MilestoneId = '12345600000123' AND Priority = 'High' 

Columns

Name	Type	Description
ProjectId	String	Project Id.
Id [KEY]	String	Task Id.
Name	String	Task Name.
Key	String	Task Key.
BillingType	String	Task Billing Type.
Completed	Boolean	Task Completed.
CreatedBy	String	Task Created By.
CreatedByEmail	String	Task Created By Email.
CreatedByZpuid	String	Task Created By Zpuid.
CreatedPerson	String	Task Created Person.
CreatedDate	Date	Task Created Date.
CreatedDateTime	Datetime	Task Created Date Time.
Description	String	Task Description.
Owners	String	Task Owners.
Duration	String	Task Duration.
DurationType	String	Task Duration Type.
EndDate	Date	Task End Date.
EndDateTime	Datetime	Task End Date Time.
CommentAdded	Boolean	Task Comment Added.
DocsAssociated	Boolean	Task Docs Associated.
ForumAssociated	Boolean	Task Forum Associated.
RecurrenceSet	Boolean	Task Recurrence Set.
ReminderSet	Boolean	Task Reminder Set.
Parent	Boolean	Task Parent.
LastUpdatedDate	Date	Task Last Updated Date.
LastUpdatedDateTime	Datetime	Task Last Updated Date Time.
LinkSelfUrl	String	Task Link Self Url.
LinkTimesheetUrl	String	Task Link Timesheet Url.
LinkWebUrl	String	Task Link Web Url.
LogHoursBillableHours	String	Task Log Hours Billable Hours.
LogHoursNonBillableHours	String	Task Log Hours Non Billable Hours.
MilestoneId	String	Task Milestone Id.
OrderSequence	Integer	Task Order Sequence.
PercentComplete	String	Task Percent Complete.
Priority	String	Task Priority. 使用できる値は次のとおりです。none, low, medium, high
StartDate	Date	Task Start Date.
StartDateTime	Datetime	Task Start Date Time.
StatusColorCode	String	Task Status Color Code.
StatusId	String	Task Status Id.
StatusName	String	Task Status Name.
StatusType	String	Task Status Type.
Subtasks	Boolean	Task Subtasks.
TaskFollowers	String	Task Task Followers.
TaskFollowerSize	Integer	Task Task Follower Size.
TasklistId	String	Task Tasklist Id.
TasklistIdString	String	Task Tasklist Id String.
TasklistName	String	Task Tasklist Name.
Work	String	Task Work.
WorkForm	String	Task Work Form.
WorkType	String	Task Work Type.
GroupNameAssociatedTeamsAnyTeam	String	Task Group Name Associated Teams Any Team.
GroupNameAssociatedTeamsCount	Integer	Task Group Name Associated Teams Count.
GroupNameIsTeamUnassigned	Boolean	Task Group Name Is Team Unassigned.

Pseudo-Columns

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

Name	Type	Description
Owner	String	Task Owner.
Status	String	Task Status. 使用できる値は次のとおりです。completed, notcompleted
Time	String	Task Time. 使用できる値は次のとおりです。overdue, today, tomorrow
LastModifiedTime	Datetime	Task Last Modified Time.
CustomViewId	String	Task Custom View Id.

Gets the time logs under a specific bug.

Select

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

• BillStatus
• User
• ComponentType

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

• Date
• LastModifiedDate

For example, the following queries are processed server side:

SELECT * FROM MyTimelogs WHERE User = '123450000123'

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

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

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

SELECT * FROM MyTimelogs

Columns

Name	Type	Description
Id [KEY]	String	Time Log Id.
AddedByName	String	Time Log Added By Name.
AddedByZpuid	String	Time Log Added By Zpuid.
AddedByZuid	Long	Time Log Added By Zuid.
ApprovalStatus	String	Time Log Approval Status.
ApproverName	String	Time Log Approver Name.
BillStatus	String	Time Log Bill Status. 使用できる値は次のとおりです。all, billable, non billable
CreatedDate	Date	Time Log Created Date.
CreatedDateTime	Datetime	Time Log Created Date Time.
StartTime	Datetime	Time Log Start Time.
EndTime	Datetime	Time Log End Time.
Notes	String	Time Log Notes.
HoursDisplay	String	Time Log Hours Display.
Hours	Integer	Time Log Hours.
Minutes	Integer	Time Log Minutes.
IsParent	Boolean	Time Log Is Parent.
IsSubTask	Boolean	Time Log Is Sub Task.
LastModifiedDate	Date	Time Log Last Modified Date.
LastModifiedDateTime	Datetime	Time Log Last Modified Date Time.
LinkSelfUrl	String	Time Log Link Self Url.
LogDate	Date	Time Log Date.
LogDateTime	Datetime	Time Log Date Time.
OwnerId	String	Time Log Owner Id.
OwnerName	String	Time Log Owner Name.
SubTaskLevel	String	Time Log Sub Task Level.
TaskName	String	Time Log Task Name.
TaskListId	Long	Time Log Task List Id.
TaskListName	String	Time Log Task List Name.
TotalMinutes	Integer	Time Log Total Minutes.
ProjectId	String	Time Log Project Id.
ProjectName	String	Time Log Project Name.
Date	Date	Time Log Date.
User	String	Time Log User.
ComponentType	String	Time Log Component Type. 使用できる値は次のとおりです。bug, task, general

Gets all the project custom fields.

Select

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

SELECT * FROM ProjectCustomFields

Columns

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

Gets list of all project custom status.

Select

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

SELECT * FROM ProjectCustomStatus

Columns

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

Gets list of project layouts.

Select

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

SELECT * FROM ProjectLayouts

Columns

Name	Type	Description
Id [KEY]	String	Project Layout Id.
Name	String	Project Layout Name.
Default	Boolean	Is Default Project Layout.

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

Select

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

SELECT * FROM TaskLayouts

Columns

Name	Type	Description
Id [KEY]	String	Task Layout Id.
Name	String	Task Layout Name.
Default	Boolean	Task Layout Default.

Fetches status history of tasks from the Portal.

Select

This is a portal-level view.

• LastModifiedTime supports: ORDER

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

SELECT * FROM TaskStatusHistory ORDER BY LastModifiedTime

Columns

Name	Type	Description
Id [KEY]	String	Task Status History Id.
Name	String	Task Status History Name.
StatusId	String	Task Status History Status Id.
StatusName	String	Task Status History Status Name.
PreviousStatusId	String	Task Status History Previous Status Id.
PreviousStatusName	String	Task Status History Previous Status Name.
UpdatedStatusId	String	Task Status History Updated Status Id.
UpdatedStatusName	String	Task Status History Updated Status Name.
TransitionTimeMillis	String	Task Status History Transition Time Millis.
TransitionTimeDuration	String	Task Status History Transition Time Duration.
UpdatedBy	String	Task Status History Updated By.
UpdatedByName	String	Task Status History Updated By Name.
UpdatedByEmail	String	Task Status History Updated By Email.
UpdatedOn	Datetime	Task Status History Updated On.

Pseudo-Columns

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

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

Fetch all timesheet custom fields across the project.

Select

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

SELECT * FROM TimesheetCustomFields

Columns

Name	Type	Description
Id [KEY]	String	Time Sheet Custom Field Id.
FieldName	String	Time Sheet Custom Field Name.
DataType	String	Time Sheet Custom Field Data Type.
UniqueColumn	String	Time Sheet Custom Field Unique Column.

Gets the details of all users in a specific portal.

Columns

Name	Type	Description
AvailableUsersCount	String	Available User Count.

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

Name	Description
BugAssociatedTasks	Fetches the details of the task associated with a bug.
BugComments	Fetch comments for the bug.
BugEntityProperties	Retrieves the data stored against an entity. The argument of this method should be the key that was used when the same data was stored.
BugFollowers	Get list of followers for the bug.
Bugs	Gets all the tasks in the given project. It fetches only the main tasks and not the subtasks.
BugTimelogs	Gets the time logs under a specific bug.
Events	Fetches all the tags present in a specific portal.
ForumCategories	Gets all the forum categories.
ForumComments	Gets all the forum comments.
Forums	Gets all the forums in the given project.
Milestones	Gets list of project layouts.
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.
ProjectUsers	Gets the details of all the users in a specific project.
Statuses	Gets the statuses for the given project.
TaskActivities	Retrieve details of who modified a specific task and when.
TaskComments	Get all the task comments.
TaskCustomfields	Usage information for the operation TaskCustomfields.rsd.
TaskEntityProperties	Retrieves the data stored against an entity. The argument of this method should be the key that was used when the same data was stored.
Tasklists	Get all the task lists in the given project.
Tasks	Gets all the tasks in the given project. It fetches only the main tasks and not the subtasks.
TaskSubtasks	View all the subtasks of the given task.
TaskTimelogs	Gets the time logs under a specific task.
TeamUsers	Fetch details of a particular team.

Fetches the details of the task associated with a bug.

Table Specific Information

This table shows associations between Bugs and Tasks.

Select

• BugId supports the following operator: =.

For example, the following query is processed server side:

SELECT * FROM BugAssociatedTasks WHERE BugId = '123439000000045005'

Insert

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

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

Delete

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

DELETE FROM BugAssociatedTasks WHERE BugId = '123418000000045005' AND TaskId = '123418000000044005'

Columns

Name	Type	ReadOnly	Description
BugId	String	False	Bug Id.
TaskId	String	False	Associated Task Id.
TaskName	String	False	Associated Task Name.
Prefix	String	False	Associated Task Prefix.
ProjectId	String	False	Associated Task Project Id.
TaskOwners	String	False	Associated Task Owners.
TaskPercentageCompleted	String	False	Associated Task Percentage Completed.
TaskPriority	String	False	Associated Task Priority.
TaskListId	String	False	Associated Task List Id.

Fetch comments for the bug.

Select

• BugId is required to retrieve Bug Comments.

For example, the following query is processed server side:

SELECT * FROM BugComments WHERE BugId = '123456000000045005'

Insert

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

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

Delete

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

DELETE FROM BugComments WHERE BugId = '123456000000045005' AND Id = '123456000000045042'

Columns

Name	Type	ReadOnly	Description
BugId [KEY]	String	False	Bug Id.
Id [KEY]	String	False	Bug Comment Id.
Comment	String	False	Bug Comment Content.
AddedBy	String	False	Bug Comment Added By.
AddedPersonName	String	False	Bug Comment Added Person Name.
CreatedDate	Date	False	Bug Comment Created Date.
CreatedDateTime	Datetime	False	Bug Comment Created Date Time.
UpdatedBy	String	False	Bug Comment Updated By.
UpdatedPerson	String	False	Bug Comment Updated Person.
UpdatedDate	Date	False	Bug Comment Updated Date.
UpdatedDateTime	Datetime	False	Bug Comment Updated Date Time.

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

Table Specific Information

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

Select

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

For example, the following query is processed server side:

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

Insert

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

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

Update

ZohoProjects allows updates for the PropertyValue column.

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

Delete

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

DELETE FROM BugEntityProperties WHERE BugId = '166135000000034087' AND PropertyKey = 'key1' AND Id = '166135000000035001'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Bug Entity Property Id.
BugId [KEY]	String	False	Bug Id.
PropertyKey [KEY]	String	False	Bug Entity Property Key.
PropertyValue	String	False	Bug Entity Property Value.

Get list of followers for the bug.

Select

• BugId is required to retrieve Bug Followers.

For example, the following query is processed server side:

SELECT * FROM BugFollowers WHERE BugId = '123456000000042110'

Insert

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

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

Delete

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

DELETE FROM BugFollowers WHERE BugId = '123456000000042110'  AND  FollowerId = '12345649448'

Columns

Name	Type	ReadOnly	Description
BugId [KEY]	String	False	Bug Id.
FollowerId	String	False	Bug Follower Id.
FollowerName	String	False	Bug Follower Name.
BugFollowers	String	False	Bug Followers.

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

Select

Columns that support the = operator:

• Id
• CreatedTime
• Flag
• StatusType
• CustomViewId

Columns that support the = and IN operator:

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

For example, the following query is processed server side:

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

Insert

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

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

To create a new Bug, specify the following fields:

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

Update

You can use the below query to update a bug:

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

Delete

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

DELETE FROM Bugs WHERE Id = '123456000000042126' 

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Bug Id.
Key	String	False	Bug Key.
Title	String	False	Bug Title.
AssigneeId	String	False	Bug Assignee Id.
AssigneeName	String	False	Bug Assignee Name.
AssigneeZpuid	String	False	Bug Assignee ZPUID.
AttachmentCount	String	False	Bug Attachment Count.
BugNumber	String	False	Bug Number.
BugPrefix	String	False	Bug Prefix.
ClassificationId	Long	False	Bug Classification Id.
ClassificationType	String	False	Bug Classification Type.
Closed	Boolean	False	Bug Closed.
CommentCount	String	False	Bug Comment Count.
CreatedTime	Date	False	Bug Created Time.
CreatedDateTime	Datetime	False	Bug Created Date Time.
Description	String	False	Bug Description.
DueDate	Date	False	Bug Due Date.
DueDateTime	Datetime	False	Bug Due Date Time.
EscalationLevel	String	False	Bug Escalation Level.
Flag	String	False	Bug Flag. 使用できる値は次のとおりです。internal, external
LinkSelfUrl	String	False	Bug Link Self Url.
LinkTimesheetUrl	String	False	Bug Link Timesheet Url.
LinkWebUrl	String	False	Bug Link Web Url.
ModuleId	Long	False	Bug Module Id.
ModuleName	String	False	Bug Module Name.
ReportedPerson	String	False	Bug Reported Person.
ReporterEmail	String	False	Bug Reporter Email.
ReporterId	String	False	Bug Reporter Id.
ReporterNonZuser	String	False	Bug Reporter Non User.
ReproducibleId	Long	False	Bug Reproducible Id.
ReproducibleType	String	False	Bug Reproducible Type.
SeverityId	Long	False	Bug Severity Id.
SeverityType	String	False	Bug Severity Type.
StatusColorcode	String	False	Bug Status Color Code.
StatusId	String	False	Bug Status Id.
StatusType	String	False	Bug Status Type. 使用できる値は次のとおりです。open, closed
UpdatedDate	Date	False	Bug Updated Date.
UpdatedDateTime	Datetime	False	Bug Updated Date Time.
GroupNameAssociatedTeamsAnyTeam	String	False	Bug Group Name Associated Teams by Any Team.
GroupNameAssociatedTeamsCount	Integer	False	Bug Group Name Associated Teams Count.
GroupNameIsTeamUnassigned	Boolean	False	Bug Group Name Is Team Unassigned.

Pseudo-Columns

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

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

Gets the time logs under a specific bug.

Select

• BugId is required to retrieve Bug Time Logs.

For example, the following query is processed server side:

SELECT * FROM BugTimelogs WHERE BugId = '123456000000045005'

Insert

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

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

Update

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

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

Delete

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

DELETE FROM BugTimelogs WHERE BugId = '123456000000045005' AND Id = '123456000000044025'

Columns

Name	Type	ReadOnly	Description
BugId [KEY]	String	False	Bug Id.
Id [KEY]	String	False	Bug Time Log Id.
AddedByName	String	False	Bug Time Log Added By Name.
AddedByZpuid	String	False	Bug Time Log Added By ZPUID.
AddedByZuid	Long	False	Bug Time Log Added By ZUID.
ApprovalStatus	String	False	Bug Time Log Approval Status.
ApproverName	String	False	Bug Time Log Approver Name.
BillStatus	String	False	Bug Time Log Bill Status. 使用できる値は次のとおりです。Billable, Non Billable
BugTitle	String	False	Bug Time Log Bug Title.
CreatedDate	Date	False	Bug Time Log Created Date.
CreatedDateTime	Datetime	False	Bug Time Log Created Date Time.
Hours	Integer	False	Bug Time Log Hours.
Minutes	Integer	False	Bug Time Log Minutes.
HoursDisplay	String	False	Bug Time Log Hours Display.
LastModifiedDate	Date	False	Bug Time Log Last Modified Date.
LastModifiedDateTime	Datetime	False	Bug Time Log Last Modified Date Time.
LinkSelfUrl	String	False	Bug Time Log Link Self Url.
LogDate	Date	False	Bug Time Log Date.
LogDateTime	Datetime	False	Bug Time Log Date Time.
Notes	Unknown	False	Bug Time Log Notes.
OwnerId	String	False	Bug Time Log Owner Id.
OwnerName	String	False	Bug Time Log Owner Name.
TotalMinutes	Integer	False	Bug Time Log Total Minutes.

Fetches all the tags present in a specific portal.

Select

• Status supports the following operator =.

For example, the following query is processed server side:

SELECT * FROM Events WHERE Status = 'open'

Insert

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

• Title
• DurationHour
• DurationMinutes
• Location
• Participants
• Reminder
• Repeat
• ScheduledOn
• RepeatTimes

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

Update

To update an Event specify the Id field.

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

Delete

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

DELETE FROM Events WHERE Id = '123456000000045018'  

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Event Id.
Title	String	False	Event Title.
CreatedBy	String	False	Event Created By.
CreatedByZpuid	String	False	Event Created By ZPUID.
CreatedOn	Long	False	Event Created On.
DurationHour	String	False	Event Duration Hour.
DurationMinutes	String	False	Event Duration Minutes.
Location	String	False	Event Location.
Occurred	Integer	False	Event Occurred.
Occurrences	Integer	False	Event Occurrences.
Participants	String	False	Event Participants.
ProjectName	String	False	Event Project Name.
Reminder	String	False	Event Reminder. 使用できる値は次のとおりです。on time, 15 mins, 30 mins, 1 hour, 2 hours, 6 hours, 12 hours, 1 day
Repeat	String	False	Event Repeat. 使用できる値は次のとおりです。only once, everyday, everyweek, everymonth, everyyear
ScheduledOn	String	False	Event Scheduled On.
ScheduledOnLong	String	False	Event Scheduled On Long.
ScheduledOnTo	String	False	Event Scheduled On To.
ScheduledOnToLong	String	False	Event Scheduled On To Long.
RepeatTimes	String	False	Event Repeat Times. 使用できる値は次のとおりです。2, 3, 4, 5, 6, 7, 8, 9, 10

Pseudo-Columns

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

Name	Type	Description
Status	String	Event Status. 使用できる値は次のとおりです。open, closed

Gets all the forum categories.

Select

You can use the below query to get all ForumCategories:

SELECT * FROM ForumCategories

Insert

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

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

Delete

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

DELETE FROM ForumCategories WHERE Id = '123456000000045018'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Forum Category Id.
Name	String	False	Forum Category Name.

Gets all the forum comments.

Select

• ForumId is required to retrieve Forum Comments.

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

SELECT * FROM ForumComments WHERE ForumId = '123456000000045018'

Insert

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

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

Delete

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

DELETE FROM ForumComments WHERE Id = '123456000000043083' AND ForumId = '123456000000045028'

Columns

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

Gets all the forums in the given project.

Select

• Id and CategoryId support the following operator: =.

For example, the following query is processed server side:

SELECT * FROM Forums WHERE Id = '123456000000043771' 

Insert

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

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

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

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

Update

To update a Forum, specify the Id field.

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

Delete

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

DELETE FROM Forums WHERE Id = '123456000000043771' 

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Forum Id.
Name	String	False	Forum Name.
Type	String	False	Forum Type. 使用できる値は次のとおりです。normal, question
Attachments	String	False	Forum Attachments.
CategoryId	String	False	Forum Category Id.
CategoryName	String	False	Forum Category Name.
CommentCount	Integer	False	Forum Comment Count.
Content	String	False	Forum Content.
Flag	String	False	Forum Flag.
IsAnnouncementPost	Boolean	False	Forum Is Announcement Post.
IsStickyPost	Boolean	False	Forum Is Sticky Post.
LastActivityDate	Date	False	Forum Last Activity Date.
LastActivityDateTime	Datetime	False	Forum Last Activity Date Time.
LastModifiedDate	Date	False	Forum Last Modified Date.
LastModifiedDateTime	Datetime	False	Forum Last Modified Date Time.
LinkSelfUrl	String	False	Forum Link Self Url.
PostDate	Date	False	Forum Post Date.
PostDateTime	Datetime	False	Forum Post Date Time.
PostedBy	String	False	Forum Posted By.
PostedByZpuid	String	False	Forum Posted By ZPUID.
PostedPerson	String	False	Forum Posted Person.
ThirdPartyAttachments	String	False	Forum Third Party Attachments.
Notify	String	False	Enter the user mail Ids to be notified (Multiple emails can be comma-separated).

Gets list of project layouts.

Select

Columns that support the = operator:

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

For example, the following query is processed server side:

SELECT * FROM Milestones WHERE Id = '123456000000043771' WHERE flag = 'internal' 

Insert

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

• Name
• Flag
• StartDate
• EndDate
• OwnerId

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

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

Update

To update a Milestone specify the Id field.

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

Delete

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

DELETE FROM Milestones WHERE Id = '123456000000043021' 

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Project Milestone Id.
Name	String	False	Project Milestone Name.
Flag	String	False	Milestone Flag. 使用できる値は次のとおりです。internal, external
Closed	Boolean	False	Milestone Closed.
CreatedDate	Date	False	Milestone Created Date.
CreatedDateTime	Datetime	False	Milestone Created Date Time.
StartDate	Date	False	Milestone Start Date.
StartDateTime	Datetime	False	Milestone Start Date Time.
EndDate	Date	False	Milestone End Date.
EndDateTime	Datetime	False	Milestone End Date Time.
Actual	Integer	False	Milestone Actual.
ActualCostFormatted	String	False	Milestone Actual Cost Formatted.
ActualHoursFormatted	String	False	Milestone Actual Hours Formatted.
Balance	Integer	False	Milestone Balance.
BalanceCostFormatted	String	False	Milestone Balance Cost Formatted.
BcyActual	Integer	False	Milestone Bcy Actual.
BcyActualCost	String	False	Milestone Bcy Actual Cost.
BcyForecasted	Integer	False	Milestone Bcy Forecasted.
BcyForecastedCost	String	False	Milestone Bcy Forecasted Cost.
BcyPlanned	Integer	False	Milestone Bcy Planned.
BcyPlannedCost	String	False	Milestone Bcy Planned Cost.
BudgetType	Integer	False	Milestone Budget Type.
CurrencyCode	String	False	Milestone Currency Code.
Difference	Integer	False	Milestone Difference.
DifferenceFormatted	String	False	Milestone Difference Formatted.
Forecasted	Integer	False	Milestone Forecasted.
ForecastedCostFormatted	String	False	Milestone Forecasted Cost Formatted.
ForecastedHoursFormatted	String	False	Milestone Forecasted Hours Formatted.
LastCalculatedTime	String	False	Milestone Last Calculated Time.
Planned	Integer	False	Milestone Planned.
PlannedCostFormatted	String	False	Milestone Planned Cost Formatted.
PlannedHoursFormatted	String	False	Milestone Planned Hours Formatted.
IsWorkfieldRemoved	Boolean	False	Milestone Is Work Field Removed.
LastUpdatedDate	Date	False	Milestone Last Updated Date.
LastUpdatedDateTime	Datetime	False	Milestone Last Updated Date Time.
SelfUrl	String	False	Milestone Self Url.
StatusUrl	String	False	Milestone Status Url.
OwnerId	String	False	Milestone Owner Id.
OwnerName	String	False	Milestone Owner Name.
OwnerZpuid	String	False	Milestone Owner ZPUID.
ProjectId	String	False	Project Id.
ProjectName	String	False	Project Name.
Sequence	Integer	False	Milestone Sequence.
Status	String	False	Status. 使用できる値は次のとおりです。completed, notcompleted
StatusColorCode	String	False	Status Color Code.
StatusId	String	False	Status Id.
StatusName	String	False	Status Name.

Pseudo-Columns

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

Name	Type	Description
DisplayType	String	Milestone Display Type. 使用できる値は次のとおりです。upcoming, delayed
LastModifiedTime	Datetime	Milestone Last Modified Time.

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

Table Specific Information

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

Select

• PropertyKey is required to retrieve ProjectEntityProperties.

For example, the following query is processed server side:

SELECT * FROM ProjectEntityProperties WHERE PropertyKey = 'key1' 

Insert

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

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

Update

ZohoProjects allows updates for the PropertyValue column.

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

Delete

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

DELETE FROM ProjectEntityProperties WHERE PropertyKey = 'key1' AND Id = '166135000000035001'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Project Entity Property Id.
PropertyKey [KEY]	String	False	Project Entity Property Key.
PropertyValue	String	False	Project Entity Property Value.

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

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	User Id.
Name	String	False	User Name.
Active	Boolean	False	User Active.
ChatAccess	Boolean	False	User Chat Access.
Email	String	False	User Email.
PortalProfileName	String	False	User Portal Profile Name.
PortalRoleId	String	False	User Portal Role Id.
PortalRoleName	String	False	User Portal Role Name.
ProfileId	String	False	User Profile Id.
ProfileType	String	False	User Profile Type.
ProjectProfileId	String	False	User Project Profile Id.
Role	String	False	User Role. 使用できる値は次のとおりです。manager, employee, contractor
RoleId	String	False	User Role Id.
RoleName	String	False	User Role Name.
Zpuid	String	False	User ZPUID.
WorkProjects	String	False	User Work Projects.
UserType	String	False	User User Type. 使用できる値は次のとおりです。active, inactive

Gets the statuses for the given project.

Select

You can use the below query to get all Statuses:

SELECT * FROM Statuses

Insert

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

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

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Status Id.
Content	String	False	Status Content.
PostedBy	String	False	Status Posted By.
PostedPerson	String	False	Status Posted Person.
PostedDate	Date	False	Status Posted Date.
PostedDateTime	Datetime	False	Status Posted Date Time.

Retrieve details of who modified a specific task and when.

Select

• TaskId is required to retrieve Forum Comments.

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

SELECT * FROM TaskActivities WHERE TaskId = '123456789' 

Columns

Name	Type	ReadOnly	Description
TaskId	String	False	Task Id.
Id [KEY]	String	False	Task Activity Id.
Name	String	False	Task Activity Name.
ActivityBy	String	False	Task Activity By.
OldValue	Unknown	False	Task Activity Old Value.
NewValue	Unknown	False	Task Activity New Value.
State	String	False	Task Activity State.
Time	Date	False	Task Activity Time.
DateTime	Datetime	False	Task Activity Date Time.
Zuid	String	False	Task Activity ZUID.

Get all the task comments.

Select

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

For example, the following query is processed server side:

SELECT * FROM TaskComments WHERE TaskId = '123456000000040013'

Insert

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

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

Update

To update a Task Comments specify the Id field.

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

Delete

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

DELETE FROM TaskComments WHERE TaskId = '123456000000045005' AND Id = '123456000000045042' 

Columns

Name	Type	ReadOnly	Description
TaskId [KEY]	String	False	Task Id.
Id [KEY]	String	False	Task Comment Id.
Content	String	False	Task Comment Content.
AddedPersonId	String	False	Task Comment Added Person Id.
AddedPersonName	String	False	Task Comment Added Person Name.
AddedVia	String	False	Task Comment Added Via.
Attachments	String	False	Task Comment Attachments.
CreatedDate	Date	False	Task Comment Created Date.
CreatedDateTime	Datetime	False	Task Comment Created Date Time.
LastModifiedDate	Date	False	Task Comment Last Modified Date.
LastModifiedDateTime	Datetime	False	Task Comment Last Modified Date Time.
ProjectName	String	False	Task Comment Project Name.
SprintsNotesId	Integer	False	Task Comment Sprints Notes Id.
ThirdPartyAttachments	String	False	Task Comment Third Party Attachments.

Pseudo-Columns

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

Name	Type	Description
LastModifiedTime	Datetime	Task Comment Last Modified Time.

Usage information for the operation TaskCustomfields.rsd.

Columns

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

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

Table Specific Information

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

Select

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

For example, the following query is processed server side:

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

Insert

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

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

Update

ZohoProjects allows updates for the PropertyValue column.

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

Delete

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

DELETE FROM TaskEntityProperties WHERE TaskId = '166135000000034087' AND PropertyKey = 'key1' AND Id = '166135000000035001'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Task Entity Property Id.
TaskId [KEY]	String	False	Task Id.
PropertyKey [KEY]	String	False	Task Entity Property Key.
PropertyValue	String	False	Task Entity Property Value.

Get all the task lists in the given project.

Select

• MilestoneId and Flag support the following operator: =.

For example, the following query is processed server side:

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

Insert

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

• Name
• MilestoneId
• TaskTemplateId
• ShiftDays
• Flag

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

Update

To update a Tasklist specify the Id field.

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

Delete

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

DELETE FROM Tasklists WHERE Id = '123456000000041033'

Columns

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

Pseudo-Columns

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

Name	Type	Description
Flag	String	Tasklist Flag. 使用できる値は次のとおりです。internal, external

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

Select

Columns that support the = operator:

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

• CreatedDate and LastModifiedTime support: ORDER

For example, the following query is processed server side:

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

Insert

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

• Name
• Description
• Owners
• Duration
• DurationType
• EndDate
• Priority
• StartDate
• TasklistId
• Work
• GroupNameAssociatedTeamsAnyTeam
• RatePerHour
• ReminderString
• Recurrence
• BudgetValue
• BudgetThreshold

INSERT INTO Tasks (Name, Description, Duration, DurationType, Priority,  BudgetValue)
VALUES ('New Task #2', 'This is a test description', '5', 'hrs', 'High', '1000')

Update

To update a Task specify the Id field.

UPDATE Tasks SET Priority = 'Low' WHERE Id = '123456000000034003'

Delete

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

DELETE FROM Tasks WHERE Id = '123456000000034003'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Task Id.
Name	String	False	Task Name.
Key	String	False	Task Key.
BillingType	String	False	Task Billing Type.
Completed	Boolean	False	Task Completed.
CreatedBy	String	False	Task Created By.
CreatedByEmail	String	False	Task Created By Email.
CreatedByZpuid	String	False	Task Created By Zpuid.
CreatedPerson	String	False	Task Created Person.
CreatedDate	Date	False	Task Created Date.
CreatedDateTime	Datetime	False	Task Created Date Time.
Description	String	False	Task Description.
Owners	String	False	Task Owners.
Duration	String	False	Task Duration.
DurationType	String	False	Task Duration Type. 使用できる値は次のとおりです。days, hrs
EndDate	Date	False	Task End Date.
EndDateTime	Datetime	False	Task End Date Time.
CommentAdded	Boolean	False	Task Comment Added.
DocsAssociated	Boolean	False	Task Docs Associated.
ForumAssociated	Boolean	False	Task Forum Associated.
RecurrenceSet	Boolean	False	Task Recurrence Set.
ReminderSet	Boolean	False	Task Reminder Set.
Parent	Boolean	False	Task Parent.
LastUpdatedDate	Date	False	Task Last Updated Date.
LastUpdatedDateTime	Datetime	False	Task Last Updated Date Time.
LinkSelfUrl	String	False	Task Link Self Url.
LinkTimesheetUrl	String	False	Task Link Timesheet Url.
LinkWebUrl	String	False	Task Link Web Url.
LogHoursBillableHours	String	False	Task Log Hours Billable Hours.
LogHoursNonBillableHours	String	False	Task Log Hours Non Billable Hours.
MilestoneId	String	False	Task Milestone Id.
OrderSequence	Integer	False	Task Order Sequence.
PercentComplete	String	False	Task Percent Complete.
Priority	String	False	Task Priority. 使用できる値は次のとおりです。none, low, medium, high
StartDate	Date	False	Task Start Date.
StartDateTime	Datetime	False	Task Start Date Time.
StatusColorCode	String	False	Task Status Color Code.
StatusName	String	False	Task Status Name.
StatusId	String	False	Task Status Id.
StatusType	String	False	Task Status Type.
Subtasks	Boolean	False	Task Subtasks.
TaskFollowers	String	False	Task Task Followers.
TaskFollowerSize	Integer	False	Task Task Follower Size.
TasklistId	String	False	Tasklist Id.
TasklistName	String	False	Tasklist Name.
Work	String	False	Task Work.
WorkForm	String	False	Task Work Form.
WorkType	String	False	Task Work Type. 使用できる値は次のとおりです。work_hrs_per_day
GroupNameAssociatedTeamsAnyTeam	String	False	Task Group Name Associated Teams Any Team.
GroupNameAssociatedTeamsCount	Integer	False	Task Group Name Associated Teams Count.
GroupNameIsTeamUnassigned	Boolean	False	Task Group Name Is Team Unassigned.
RatePerHour	String	False	Task Rate Per Hour.
ReminderString	String	False	Task Reminder String.
Recurrence	String	False	Task Recurrence.
BudgetValue	String	False	Task Budget Value.
BudgetThreshold	String	False	Task Budget Threshold.

Pseudo-Columns

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

Name	Type	Description
Owner	String	Task Owner.
Status	String	Task Status. 使用できる値は次のとおりです。completed, notcompleted
Time	String	Task Time. 使用できる値は次のとおりです。overdue, today, tomorrow
LastModifiedTime	Datetime	Task Last Modified Time.
ViewId	String	Task View Id.

View all the subtasks of the given task.

Select

• TaskId is required to retrieve Task Subtasks.

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

SELECT * FROM TaskSubtasks WHERE TaskId = '12345600000123' 

Insert

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

• TaskId
• Name
• Description
• Duration
• DurationType
• Priority
• StartDate
• EndDate
• PersonResponsible

INSERT INTO TaskSubtasks (TaskId, Name, StartDate, EndDate, Duration, DurationType, Priority, Description)
VALUES ('165818000000040013', 'Sql Subtask #4', '2022-03-01', '2022-03-10', '2', 'hrs', 'High', 'Test description of the subtask')

Columns

Name	Type	ReadOnly	Description
TaskId	String	False	Task Id.
Id [KEY]	String	False	Subtask Id.
Name	String	False	Subtask Name.
Key	String	False	Subtask Key.
BillingType	String	False	Subtask Billing Type.
Completed	Boolean	False	Subtask Completed.
CreatedBy	String	False	Subtask Created By.
CreatedByEmail	String	False	Subtask Created By Email.
CreatedByZpuid	String	False	Subtask Created By Zpuid.
CreatedPerson	String	False	Subtask Created Person.
CreatedTime	Date	False	Subtask Created Time.
CreatedDateTime	Datetime	False	Subtask Created Date Time.
Depth	Integer	False	Subtask Depth.
Description	String	False	Subtask Description.
DetailsOwners	String	False	Subtask Details Owners.
Duration	String	False	Subtask Duration.
DurationType	String	False	Subtask Duration Type. 使用できる値は次のとおりです。days, hrs
IsCommentAdded	Boolean	False	Subtask Is Comment Added.
IsDocsAssociated	Boolean	False	Subtask Is Docs Associated.
IsForumAssociated	Boolean	False	Subtask Is Forum Associated.
IsRecurrenceSet	Boolean	False	Subtask Is Recurrence Set.
IsReminderSet	Boolean	False	Subtask Is Reminder Set.
IsParent	Boolean	False	Subtask Is Parent.
LastUpdatedTime	Date	False	Subtask Last Updated Time.
LastUpdatedDateTime	Datetime	False	Subtask Last Updated Date Time.
LinkSelfUrl	String	False	Subtask Link Self Url.
LinkTimesheetUrl	String	False	Subtask Link Timesheet Url.
LinkWebUrl	String	False	Subtask Link Web Url.
LogBillableHours	String	False	Subtask Log Billable Hours.
LogNonBillableHours	String	False	Subtask Log Non Billable Hours.
MilestoneId	String	False	Subtask Milestone Id.
OrderSequence	Integer	False	Subtask Order Sequence.
ParentTaskId	String	False	Subtask Parent Task Id.
PercentComplete	String	False	Subtask Percent Complete.
Priority	String	False	Subtask Priority. 使用できる値は次のとおりです。none, low, medium, high
RootTaskId	String	False	Subtask Root Task Id.
StatusColorCode	String	False	Subtask Status Color Code.
StatusId	String	False	Subtask Status Id.
StatusName	String	False	Subtask Status Name.
StatusType	String	False	Subtask Status Type.
Subtasks	Boolean	False	Subtask Subtasks.
TaskFollowers	String	False	Subtask Task Followers.
TaskFollowerSize	Integer	False	Subtask Task Follower Size.
TasklistId	String	False	Subtask Tasklist Id.
TasklistName	String	False	Subtask Tasklist Name.
Work	String	False	Subtask Work.
WorkForm	String	False	Subtask Work Form.
WorkType	String	False	Subtask Work Type.
GroupNameAssociatedTeamsAnyTeam	String	False	Subtask Group Name Associated Teams Any Team.
GroupNameAssociatedTeamsCount	Integer	False	Subtask Group Name Associated Teams Count.
GroupNameIsTeamUnassigned	Boolean	False	Subtask Group Name Is Team Unassigned.
StartDate	Date	False	Subtask Start Date.
EndDate	Date	False	Subtask End Date.
PersonResponsible	String	False	Subtask Person Responsible.

Gets the time logs under a specific task.

Select

• TaskId is required to retrieve Task Time Logs.

For example, the following query is processed server side:

SELECT * FROM TaskTimelogs WHERE TaskId = '123456000000045005'

Insert

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

• TaskId
• BillStatus
• Notes
• HoursDisplay
• LogDate
• OwnerId

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

Update

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

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

Delete

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

DELETE FROM TaskTimelogs WHERE TaskId = '123456000000045005' AND Id = '123456000000044025'

Columns

Name	Type	ReadOnly	Description
TaskId [KEY]	String	False	Task Id.
Id [KEY]	String	False	Task Time Log Id.
AddedByName	String	False	Task Time Log Added By Name.
AddedByZpuid	String	False	Task Time Log Added By Zpuid.
AddedByZuid	Long	False	Task Time Log Added By Zuid.
ApprovalStatus	String	False	Task Time Log Approval Status.
ApproverName	String	False	Task Time Log Approver Name.
BillStatus	String	False	Task Time Log Bill Status. 使用できる値は次のとおりです。Billable, Non Billable
CreatedDate	Date	False	Task Time Log Created Date.
CreatedDateTime	Datetime	False	Task Time Log Created Date Time.
StartTime	Datetime	False	Task Time Log Start Time.
EndTime	Datetime	False	Task Time Log End Time.
Notes	String	False	Task Time Log Notes.
HoursDisplay	String	False	Task Time Log Hours Display.
Hours	Integer	False	Task Time Log Hours.
Minutes	Integer	False	Task Time Log Minutes.
IsParent	Boolean	False	Task Time Log Is Parent.
IsSubTask	Boolean	False	Task Time Log Is Sub Task.
LastModifiedDate	Date	False	Task Time Log Last Modified Date.
LastModifiedDateTime	Datetime	False	Task Time Log Last Modified Date Time.
LinkSelfUrl	String	False	Task Time Log Link Self Url.
LogDate	Date	False	Task Time Log Date.
LogDateTime	Datetime	False	Task Time Log Date Time.
OwnerId	String	False	Task Time Log Owner Id.
OwnerName	String	False	Task Time Log Owner Name.
SubTaskLevel	String	False	Task Time Log Sub Task Level.
TaskName	String	False	Task Time Log Task Name.
TaskListId	Long	False	Task Time Log Task List Id.
TaskListName	String	False	Task Time Log Task List Name.
TotalMinutes	Integer	False	Task Time Log Total Minutes.