Hi There,
According to the doc here: https://api.clarizen.com/V2.0/services/data/Query
Under the section called "Relations"
"You can include data from entities related to the main entity you are querying if a Relation exists between them"
The example provided is:
SELECT name, (SELECT name FROM Resources)
FROM task
WHERE Manager = '/User/69936402-07d6-4620-9f36-9480a6527f6f'
Why then, where a relationship exists between a Project and a Task, can I not:
SELECT
SysID,
Name,
Description,
State.Name,
(
select
startdate,
duedate,
Duration,
Work,
State,
Name
from
Task
)
FROM
Project
WHERE
LastUpdatedBySystemOn > 2015-08-18T00:00:00
and externalid = '<alphanumeric>'
Running that query results in the following error referenceId 3DvKdZfnN31irJ3huKPrTl
Also, wondering whether CZQL supports the Month() function, so I can "group by" the nested query by "Month(Task.StartDate)"
Thanks,
Mick
Please sign in to leave a comment.
I should add, the reason why I am attempting to do it this way is because I intend for the nested query to be a "group by" query - with a SUM(Work), SUM(Duration), SUM(Cost) and ideally grouped by MONTH(StartDate) ... so I can determine the projected "Expected Cost" for a given Project in the upcoming months.
If MONTH(StartDate) is not available, I'd probably create a custom field for TASK and try to populate it using DATA LOADER?
Hi Michael,
1. Relations between Clarizen objects are well defined. You don't have a 'Task' relation for a project. Instead, you should retrieve from task and use the Project field of the task to filter, i.e. Select ... from task where project='/Project/<external id>'
2. There are no function like MONTH in CZQL. You could use a custom field for that. I suggest you choose a formula field (click Formula below Field Type in the custom field definition page) so you don't have to populate it at all.
Hope this helps,
Ophir
Thanks Ophir,
Regarding 1) I knew I could do:
SELECT Project.name, Sum(Work) as SumWork FROM task LIMIT 10
But what interests me about the "nested query" documented syntax:
SELECT name, (SELECT name FROM Resources) FROM task LIMIT 10
Is it's ability to return one row for each "task" record and multiple rows for it's related "resources".
When looping through the results in a query like the following, I have to detect when "WorkItem" changes from row to row.
SELECT WorkItem.name, Resource.name FROM ResourceLink
Parsing of the documented nested query is "neater" .. (to me).
But I'm not sure when I can or can't use it.
As you say "You don't have a 'Task' relation for a project" but similarly, I'm guessing you don't have a 'Resource' allocation for a 'Task' .. but why does it work for Task? ... Should I be checking "/V2.0/services/data/relationQuery" ?
2) Thanks for your suggestions creating a formula field - got it working without the need to populate!
Cheers,
Mick
Hi Michael,
You can check which Relations are available for a Clarizen class with the /V2.0/services/metadata/describeEntityRelations service.
Check the reference on https://api.clarizen.com/V2.0/services/metadata/DescribeEntityRelations for further information.
Hope this helps,
Ophir
Yep, OK. Thanks again Ophir