Rest API Unable to fetch list of Project's resources email and resources name

Hi everyone.
Can you, please, post the final working CZQL query?

Hi S.

Please try the following CZQL:

select name, (select name, email from resources) from project

Hope this helps,

Ophir

Hi Karthik.

"Resources" is truly not a field on Project. If you need to fetch the project's resources, query "RegularResourceLink" filtering by the WorkItem field. You can reach the resources information with the Resource field.

Use the Clarizen page 'Settings -> configure' to figure out available fields for each entity and their API name. You can also get programmatically with the _DescribeEntitiesMessage. _Also check the manual page:

http://usermanual.clarizen.com/15-4-3-1-data-model

Hope this helps,

Ophir

Thanks for you reply Ophir.

I did the same already. But I got Internal error!

{
"fields": [
"WorkItem.Project.ExternalID",
"Resource.Name",
"Resource.Email"
],
"TypeName": "RegularResourceLink",
"paging": {
"pageNumber": 1,
"pageSize": 10
},
"where": {
"_type": "Compare",
"LeftExpression": {
"FieldName": "WorkItem"
},
"Operator": "Equal",
"RightExpression": {
"value": "b9265252-9f80-4278-a391-4423bbb213af"
}
}
}
Array
(
[errorCode] => Internal
[message] => An internal error has occurred, the error was logged and will be examined.
[referenceId] => rLgXKt3_N0C6MeGN3QENRQ
)

Hi Karthik,

Try "rightExpression": {"value": "Project/b9265252-9f80-4278-a391-4423bbb213af"}

An EntityId value must always contain the type name.

Hope this helps,

Ophir

Hi Ophir,

When I tried with "Project/ed1eeae000df47299c46385db348fdd5" => "Internal Error".

Then I tried with  "/Project/ed1eeae000df47299c46385db348fdd5" prepended slash. Now I am getting valid response but result is empty.

Array
(
[entities] => Array
(
)

[paging] => Array
(
[pageNumber] => 2
[pageSize] => 1000
[hasMore] =>
)

)

 I checked the website for Project ID and its avaiable and its active project. 

Please advise me if I am wrong and let me know if you need more info.

Hi Karthik.

You should prepend slash (my mistake for not mentioning). However, you should specify pageNumber value of 0 on the query. Page numbers are zero based.

Hope this helps,

Ophir

Thank a lot Ophir. It Works!

Hi Ophir

I have to fetch Resource.DisplayName,WorkItem.State and one custom field on the Work Item and I have followed the same structure as told by you.

I am able to fetch the custom field and Resource.DisplayName but not able to fetch WorkItem.State.

Here is the code that I am using:-

{

** typeName: "RegularResourceLink",**

** fields: [**

** "WorkItem.C_Hours",**

** "C_WIStartDate",**

** "Resource.DisplayName"**

** ],**

** paging: {**

** pageNumber: 0**

** },**

** where: {**

** "_type": "And",**

** "conditions": [**

** {**

** "leftExpression": {**

** "fieldName": "WorkItem.State"**

** },**

** "operator": "Equal",**

** "rightExpression": {**

** "value": "Completed"**

** }**

** },**

** {**

** "leftExpression": {**

** "fieldName": "C_WIStartDate"**

** },**

** "operator": "GreaterThan",**

** "rightExpression": {**

** "value": "2015-05-10 00:00:00"**

** }**

** },**

** {**

** "leftExpression": {**

** "fieldName": "C_WIStartDate"**

** },**

** "operator": "LessThan",**

** "rightExpression": {**

** "value": "2015-05-18 00:00:00"**

** }**

** }**

** ]**

** }**

}

Please advise.

Although you can retrieve nested fields as part of the query result (i.e. WorkItem.State) you can not use them in Where expressions.

What you can do, is use an IN query which goes something like this:

"leftExpression": {

"fieldName": "WorkItem"

},

"operator": "In",

"rightExpression":{

   "query": {

   //Here you should put a query expression which returns Completed Work Items.

   //You can do it either via a QueryExprssion object or via CZQL 

   }

}

Thank you Eyal for your response.

Can we use CZQL inside this? If yes, can we do it this way?

{typeName: "RegularResourceLink",fields: ["WorkItem.C_Hours","WorkItem.ChildrenCount","C_WIStartDate","Resource.DisplayName"],paging: { pageNumber:0},where: { "_type": "And", "conditions": [ { "leftExpression": { "fieldName": "WorkItem" }, "operator": "In", "rightExpression": { "query": ("select id from WorkItem where ChildrenCount = 0") } }, { "leftExpression": { "fieldName": "C_WIStartDate" }, "operator": "GreaterThan", "rightExpression": { "value": "2015-05-10 00:00:00" } }, { "leftExpression": { "fieldName": "C_WIStartDate" }, "operator": "LessThan", "rightExpression": { "value": "2015-05-18 00:00:00" } } ] }}

Regards,

Anumeet Kaur

Eyal,

I tried to follow this approach-

https://success.clarizen.com/entries/103909216-REST-API-Entity-Query-Where-Condition-with-Specific-Entity-Field-Value

And query is becoming like this-

{typeName: "RegularResourceLink",

fields: ["WorkItem.C_Hours","WorkItem.ChildrenCount","C_WIStartDate","Resource.DisplayName"],

paging: { pageNumber:0},

where: { "_type": "And", "conditions": [

{ "leftExpression": { "fieldName": "WorkItem" }, "operator": "In", "rightExpression": { "query": {"TypeName": "Task","Where": {"LeftExpression": {"FieldName": "ChildrenCount"},"Operator": "Equal","RightExpression": {"Value": "0"}},}} },

{ "leftExpression": { "fieldName": "C_WIStartDate" }, "operator": "GreaterThan", "rightExpression": { "value": "2015-05-10 00:00:00" } },

{ "leftExpression": { "fieldName": "C_WIStartDate" }, "operator": "LessThan", "rightExpression": { "value": "2015-05-18 00:00:00" } } ] }}

But it is giving Internal server error.

Hi Anumeet,

Can you provide the error reference number?

Thanks.

Here is the numer-

1ypRtRRdhoTiHmUjuhMgZc

Thanks

Hi Anumeet,

You need to specify "_type":"compare" for each of the Compare conditions.

Hope this helps,

Ophir

Ophir thanks for your reply, Is my query forming out correctly?

After replacing and with compare, I am getting "ePG6ryxCLAZFopEjuD7ri" Reference ID. 

This is the query now-

{typeName: "RegularResourceLink",fields: ["WorkItem.C_Hours","WorkItem.ChildrenCount","C_WIStartDate","Resource.DisplayName"],paging: { pageNumber:0},where: { "_type": "compare", "conditions": [ { "leftExpression": { "fieldName": "WorkItem" }, "operator": "In", "rightExpression": {"query": { "TypeName": "Task","Where": { "leftExpression": {"FieldName": "ChildrenCount"},"Operator": "Equal", "rightExpression": {"Value": "0"}},}} }, { "leftExpression": { "fieldName": "C_WIStartDate" }, "operator": "GreaterThan", "rightExpression": { "value": "2015-05-10 00:00:00" } }, { "leftExpression": { "fieldName": "C_WIStartDate" }, "operator": "LessThan", "rightExpression": { "value": "2015-05-18 00:00:00" } } ] }}

Hi Anumeet,

I meant you should ADD "_type":"compare" for each of the Compare _conditions (the ones with _LeftExpression, Operator & RightExpression). Leave "_type":"And" as it is for the And condition.

Hope this helps,

Ophir

Actually, we detect Compare conditions automatically. I think the change should be in the Query expression:

{ "leftExpression":  

   { "fieldName": "WorkItem" },

  "operator": "In",

  "rightExpression": {

     {

       "query": {

           "_type": "EntityQyery",

            "TypeName": "Task",

   ...

...

}

Thanks Eyal, adding "_type": "EntityQyery" worked like magic.

Thank you so much for your help :-)

See if this works for you.

'{typeName: "RegularResourceLink",'+
'fields: ["WorkItem.name","WorkItem.C_Hours","WorkItem.StartDate", "WorkItem.ChildrenCount","Resource.DisplayName"],paging: { pageNumber:'+intCount+'},'+
'where: '+
'{ "_type": "and", '+
'"conditions": [ '+
'{ "leftExpression": { "fieldName": "WorkItem" }, "operator": "In", "rightExpression": {"query": { "_type": "EntityQuery", "TypeName": "Task","Where": { "leftExpression": {"FieldName": "ChildrenCount"},"Operator": "Equal", "rightExpression": {"Value": "0"}},}} }, '+
'{ "leftExpression": { "fieldName": "WorkItem" }, "operator": "In", "rightExpression": {"query": { "_type": "EntityQuery", "TypeName": "Task","Where": { "leftExpression": {"FieldName": "StartDate"},"Operator": "GreaterThanOrEqual", "rightExpression": {"Value": "'+startDate.toStartofWeek().adddays(1)+'"}},}} }, ' +
'{ "leftExpression": { "fieldName": "WorkItem" }, "operator": "In", "rightExpression": {"query": { "_type": "EntityQuery", "TypeName": "Task","Where": { "leftExpression": {"FieldName": "StartDate"},"Operator": "LessThan", "rightExpression": {"Value": "'+endDate+'"}},}} }] }'+
'}';

Anumeet Kaur, thanks for fast response.
Could you provide the CZQL version for this query? (It should look like this). This one you provided is entityQuery.

(P.S. Moderators, maybe I open a new request for CZQL version of this post? Because this one gets larger)