Sync App アプリケーションの接続 ページに移動し、接続の追加 パネルで対応するアイコンを選択して、Zoho Projects への接続を作成します。Zoho Projects アイコンが利用できない場合は、Add More アイコンをクリックしてCData サイトからZoho Projects コネクタをダウンロードおよびインストールします。

必須プロパティは［設定］タブにリストされています。［Advanced］タブには、通常は必要ない接続プロパティが表示されます。

Zoho Projects への認証

Sync App はすでにZoho Projects にOAuth アプリケーションとして登録されています。そのため、デフォルトでは、自動的に埋め込みクレデンシャル を使用して接続します。

埋め込みクレデンシャルを使用する場合は、接続を確立するために必要な操作はありません。

独自のカスタムOAuth アプリを使用したい場合は、カスタムクレデンシャル を参照してください。

概要

このセクションでは、利用可能なAPI オブジェクトを示し、Zoho Projects API へのSQL の実行について詳しく説明します。

主要機能

• Sync App はプロジェクト、イベント、ユーザーなどのZoho Projects エンティティをリレーショナルビューとしてモデル化し、SQL を記述してZoho Projects データをクエリできるようにします。
• ストアドプロシージャを使用するとZoho Projects の操作を実行できます。
• これらのオブジェクトへのライブ接続により、Sync App を使用するとZoho Projects アカウントへのあらゆる変更が即座に反映されます。

テーブル

テーブル では、利用可能なテーブルを説明します。テーブルは、プロジェクト、イベント、ユーザーなどを静的にモデル化するように定義されています。

ビュー

ビュー では、利用可能なビューを説明します。ビューは、Clients、Documents、Activities などを静的にモデル化するように定義されています。

ストアドプロシージャ

ストアドプロシージャ は、Zoho Projects のファンクションライクなインターフェースです。ストアドプロシージャを使用すると、タイムログの承認やタスクの並べ替えなど、Zoho Projects の操作を実行できます。

Sync App はZoho Projects のデータを、標準のSQL ステートメントを使用してクエリできるテーブルのリストにモデル化します。

一般的には、Zoho Projects テーブルのクエリは、リレーショナルデータベースのテーブルのクエリと同じです。時には特別なケースもあります。例えば、テーブルの特定のカラムデータを取得するために特定のカラムをWHERE 句に含める必要がある場合などです。これは通常、特定のカラムを取得するために行ごとに個別のリクエストを行う必要がある場合に必要です。これらの特別な状況は、以下にリンクされているテーブルページの上部に明確に文書化されています。

Zoho Projects Connector for CData Sync テーブル

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.
Contacts	Fetches all client contacts from a portal.
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.
PortalClients	Returns the list of client companies in the portal.
Portals	Gets all the portals for the logged in user.
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.
ProjectGroups	Gets all the groups for the specified project.
Projects	Gets all the projects for the logged in user.
ProjectUsers	Gets the details of all the users in a specific project.
Statuses	Gets the statuses for the given project.
Tags	Fetches all the tags present in a specific portal.
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.
Teams	Fetch the team details from the portal.
TeamUsers	Fetch details of a particular team.
Users	Gets the details of all the users in a specific project.

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

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.

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 portals for the logged in user.

Select

The Sync App will use the Zoho Projects API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id and ViewId support the following operator: =.

For example, the following query is processed server side:

SELECT * FROM Portals WHERE Id = '20081021809'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	False	Portal Id.
Name	String	False	Portal Name.
CompanyName	String	False	Portal Company Name.
AvailableClients	Integer	False	Portal Available Clients.
AvailableUsers	Integer	False	Portal Available Users.
AvailableProjects	Integer	False	Portal Available Projects.
BugPlan	String	False	Portal Bug Plan.
BugPlural	String	False	Bug Plural.
BugSingular	String	False	Bug Singular.
CanAddTemplate	Boolean	False	Can Add Portal Template.
CanCreateProject	Boolean	False	Can Create Portal Project.
Default	Boolean	False	Default Portal.
ExtensionsAppSettings	String	False	Portal Extensions Locations App Settings.
ExtensionsAttachmentPicker	String	False	Portal Extensions Locations Attachment Picker.
ExtensionsBlueprintDuring	String	False	Portal Extensions Locations Blueprint During.
ExtensionsIssueTab	String	False	Portal Extensions Locations Issue Tab.
ExtensionsIssuedetailsRightpanel	String	False	Portal Extensions Locations Issue Details Right Panel.
ExtensionsProjectTab	String	False	Portal Extensions Locations Project Tab.
ExtensionsTaskTab	String	False	Portal Extensions Locations Task Tab.
ExtensionsTaskTransition	String	False	Portal Extensions Locations Task Transition.
ExtensionsTaskdetailsRightpanel	String	False	Portal Extensions Locations Task Details Right Panel.
ExtensionsTopBand	String	False	Portal Extensions Locations Top Band.
GmtTimeZone	String	False	Portal GMT Time Zone.
GmtTimeZoneOffset	Integer	False	Portal GMT Time Zone Offset.
MeetingEnabled	Boolean	False	Portal Meeting Enabled.
PeopleEnabled	Boolean	False	Portal People Enabled.
CrmPartner	Boolean	False	Portal CRM Partner.
DisplayProjectPrefix	Boolean	False	Portal Display Project Prefix.
DisplayTaskPrefix	Boolean	False	Portal Display Task Prefix.
NewPlan	Boolean	False	Portal New Plan.
SprintsIntegrated	Boolean	False	Portal Sprints Integrated.
TagsAvailable	Boolean	False	Portal Tags Available.
TimeLogRestricted	Boolean	False	Portal Time Log Restricted.
ProjectsModuleId	String	False	Portal Projects Module ID.
TasksModuleId	String	False	Portal Tasks Module ID.
ProjectUrl	String	False	Portal Project URL.
LocaleCode	String	False	Portal Locale Code.
LocaleCountry	String	False	Portal Locale Country.
Language	String	False	Portal Language.
LoginZpuid	Long	False	Portal Login ZPUID.
MaxClients	Integer	False	Portal Max Clients.
MaxUsers	Integer	False	Portal Max Users.
NewUserPlan	Boolean	False	Portal New User Plan.
PercentageCalculation	String	False	Portal Percentage Calculation.
Plan	String	False	Portal Plan.
ProfileId	Long	False	Portal Profile ID.
ProfileName	String	False	Portal Profile Name.
ProfileType	Integer	False	Portal Profile Type.
ActiveProjects	Integer	False	Portal Active Projects.
ProjectPrefix	String	False	Portal Project Prefix.
Role	String	False	Portal Role.
RoleId	String	False	Portal Role ID.
RoleName	String	False	Portal Role Name.
BusinessHoursEnd	Integer	False	Portal Business Hours End.
BusinessHoursStart	Integer	False	Portal Business Hours Start.
DateFormat	String	False	Portal Date Format.
DefaultCurrency	String	False	Portal Default Currency.
DefaultDependencyType	String	False	Portal Default Dependency Type.
BudgetPermission	Boolean	False	Portal Budget Permission.
Holidays	String	False	Portal Holidays.
BudgetEnabled	Boolean	False	Portal Budget Enabled.
LastSyncTime	String	False	Portal Last Sync Time.
StartDayOfWeek	String	False	Portal Start Day of Week.
TaskDateFormat	String	False	Portal Task Date Format.
TaskDurationType	String	False	Portal Task Duration Type.
TimeZone	String	False	Portal Time Zone.
EditTimeLogRestricted	Boolean	False	Portal Edit Time Log Restricted.
LogFutureTimeAllowed	Boolean	False	Portal Log Future Time Allowed.
LogPastTimeAllowed	Boolean	False	Portal Log Past Time Allowed.
DefaultBillingStatus	String	False	Portal Default billing Status.
TimesheetApprovalEnabled	Boolean	False	Portal Timesheet Approval Enabled.
WorkingDays	String	False	Portal Working Days.
SprintsProjectPermission	Boolean	False	Portal Sprints Project Permission.
StorageType	String	False	Portal Storage Type.
TrialEnabled	Boolean	False	Portal Trial Enabled.
LogHrRestrictedByWorkHr	Boolean	False	Portal Log Hr restricted by Work Hr.

Pseudo-Columns

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

Name	Type	Description
ViewId	String

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

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.

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

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.

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.

Fetch details of a particular team.

Select

• GroupId is required to retrieve Team Users.

You can use the below query to get all Team Users:

SELECT * FROM TeamUsers WHERE GroupId = '123456000000033021'

Insert

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

• GroupId
• Zpuid

INSERT INTO TeamUsers (GroupId, ZPUID)
VALUES ('123456000000034163', '123456000000031003')

Delete

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

DELETE FROM TeamUsers WHERE GroupId = '123456000000034163' AND Id = '123456000000031003'

Columns

Name	Type	ReadOnly	Description
ProjectId	String	False	Project Id.
GroupId	String	False	Team Id.
Id [KEY]	String	False	User Id.
Name	String	False	User Name.
AddedBy	String	False	User Added By.
AddedTime	String	False	User Added Time.
Confirmed	String	False	User Confirmed.
CreatedBy	String	False	User Created By.
CreatedTime	String	False	User Created Time.
CreatedUser	String	False	User Created User.
Description	String	False	User Description.
DisplayName	String	False	User Display Name.
Email	String	False	User Email.
EntityType	String	False	User Entity Type.
FirstName	String	False	User First Name.
IsClient	Boolean	False	User Is Client.
IsDefault	Boolean	False	User Is Default.
LastAccessedTime	String	False	User Last Accessed Time.
LastName	String	False	User Last Name.
LastUpdatedTime	String	False	User Last Updated Time.
RoleId	String	False	User Role Id.
RoleName	String	False	User Role Name.
Type	String	False	User Type.
UpdatedBy	String	False	User Updated By.
UpdatedDate	String	False	User Updated Date.
UpdatedDateTime	String	False	User Updated Date Time.
UserStatus	String	False	User User Status.
Zpuid	String	False	User Zpuid.
Zuid	String	False	User Zuid.

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

ビューは、カラムと疑似カラムで構成されます。ビューは、データを示すという点でテーブルに似ていますが、ビューでは更新はサポートされません。通常、ビューとして表されるエンティティは、読み取り専用のエンティティです。多くの場合、これらのデータはストアドプロシージャを使用することで更新できます（その機能がデータソースに適用できる場合）。

ビューに対しては、通常のテーブルと同じようにクエリを実行でき、このときに返されるデータも同様です。

ビューとして公開されるクエリなどの動的ビューや、project_team ワークアイテムの特定の組み合わせを検索するためのビューがサポートされています。

Zoho Projects Connector for CData Sync ビュー

Name	Description
Activities	List all the recent activities of the project.
BugActivities	Gets all the activities for the given bug.
BugAttachments	Get details of attachments for a bug.
BugCustomFields	Gets all the custom fields in the given project.
BugResolution	Get bug's resolution.
Clients	Returns the list of client companies associated with a specific project ID.
Documents	List all the recent activities of the project.
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.
Timelogs	Gets the time logs under a specific bug.
TaskAttachments	Retrieve details of attachments associated to the task.
TaskLayouts	View the list of all the task layouts available in your portal.
TaskStatusHistory	Fetches status history of tasks from the Portal.
Timelogs	Gets the time logs under a specific bug.
TimesheetCustomFields	Fetch all timesheet custom fields across the project.
PortalUsersRemaining	Gets the details of all users in a specific portal.

List all the recent activities of the project.

Select

This is a project-level view. You can use the below query to get all Activities:

SELECT * FROM Activities

Columns

Name	Type	Description
Id [KEY]	String	Project Activity Id.
Name	String	Project Activity Name.
Activity By	String	Project Activity Author.
Activity For	String	Project Activity For Entity.
Display Time	String	Project Activity Display Time.
State	String	Project Activity State.
Time	String	Project Activity Time.

Gets all the activities for the given bug.

Select

This is a bug-level view.

• BugId is required to retrieve BugActivities.

You can use the below query to get all BugActivities:

SELECT * FROM BugActivities WHERE BugId = '12345600001'

Columns

Name	Type	Description
BugId	String	Bug Id.
Action	String	Bug Activity Action.
Type	String	Bug Activity Type.
PreviousValue	String	Bug Activity Previous Value.
CurrentValue	String	Bug Activity Current Value.
ActionBy	String	Bug Activity Action By.
ActionField	String	Bug Activity Action Field.
ActionDate	Date	Bug Activity Action Date.
ActionDateTime	Datetime	Bug Activity Action Date Time.
Zuid [KEY]	String	Bug Activity ZUID.

Get details of attachments for a bug.

Columns

Name	Type	Description
BugId	String	Bug Id.
AttachmentId	Long	Attachment Id.
FileName	String	Attached File Name.
FileType	String	Attached File Type.
FileSize	Integer	Attached File Size.
FileEncAttachParam	String	Attached File Enc Attach Param.
FileUri	String	Attached File Uri.
AttachedDate	Date	Attached Date.
AttachedDateTime	Datetime	Attached Date Time.
AuthorId	String	Attachment Author Id.
AuthorName	String	Attachment Author Name.
IsDocsAttachment	Boolean	Is Docs Attachment.

Gets all the custom fields in the given project.

Select

This is a project-level view. You can use the below query to get all BugCustomFields:

SELECT * FROM BugCustomFields WHERE BugId = '12345600001'

Columns

Name	Type	Description
LabelName	String	Bug Custom Field Label Name.
ColumnName	String	Bug Custom Field Column Name.
DefaultValue	String	Bug Custom Field Default Value.
FieldType	String	Bug Custom Field Field Type.
IsEnc	Boolean	Bug Custom Field Is Enc.
IsMandatory	Boolean	Bug Custom Field Is Mandatory.
IsPi	Boolean	Bug Custom Field Is Pi.
IsVisible	Boolean	Bug Custom Field Is Visible.
NewColumnName	String	Bug Custom Field New Column Name.
PicklistValues	String	Bug Custom Field Picklist Values.

Get bug's resolution.

Columns

Name	Type	Description
BugId	String	Bug Id.
Resolver	String	Bug Resolver.
ResolverId	String	Bug Resolver Id.
Resolution	String	Bug Resolution.
ResolutionThirdPartyAttachments	String	Bug Resolution Third Party Attachments.
ResolvedDate	Date	Bug Resolved Date.
ResolvedDateTime	Datetime	Bug Resolved Date Time.
ResolverZpuid	String	Bug Resolver ZPUID.

Returns the list of client companies associated with a specific project ID.

Select

This is a project-level view.

• Id supports the following operator: =.

For example, the following query is processed server side:

SELECT * FROM Clients WHERE Id = '12345600001'

Columns

Name	Type	Description
Id [KEY]	String	Client Id.
Name	String	Client Name.
City	String	Client City.
Country	String	Client Country.
CrmAccountId	String	Client Crm Account Id.
FirstAddress	String	Client First Address.
SecondAddress	String	Client Second Address.
State	String	Client State.
WebAddress	String	Client Web Address.
ZipCode	String	Client Zip Code.
UserId	String	Client User Id.
UserName	String	Client User Name.
UserEmail	String	Client User Email.
UserZpuid	String	Client User ZPUID.

List all the recent activities of the project.

Columns

Name	Type	Description
Id [KEY]	String	Project Activity Id.
Name	String	Project Activity Name.
Activity By	String	Project Activity Author.
Activity For	String	Project Activity For.
Display Time	String	Project Activity Display Time.
State	String	Project Activity State.
Time	String	Project Activity Time.

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.