Query Value of Custom Field in API

Hi Bill.

If I understand correctly, your custom field is of type Reference to Object. To filter by the referenced objects data you need to use the In operator. Please refer to this post for more information:

https://success.clarizen.com/entries/38249296-Query-ResourceLinks-for-All-WorkItems-of-type-Task

Post another comment if you need more assistance.

Hope this helps,

Ophir

Thanks for the quick response. I have the query working and can retrieve value in custom data, what I am missing is how to form the right side of the query to search the actual value of a custom field. my query seems to be reading the type instead of the actual value, so I need the structure to drill down to the custom field value of that makes any sense. Thanks.

Hi Bill.

Here's a code snippet for you. By the way, filtering by values of referenced objects doesn't have to do with custom fields particularly.

var entQ = new EntityQuery

{

TypeName = "WorkItem",

Fields = new string[] {"name", "c_taskGroup.c_Module"},

Where = new Compare

{

LeftExpression = new FieldExpression {FieldName="c_TaskGroup"},

Operator = Operator.In,

RightExpression = new QueryExpression

{

Query = new EntityQuery

{

TypeName = "Task",

Where = new Compare

{

LeftExpression = new FieldExpression{FieldName="C_Module"},

Operator= Operator.BeginsWith,

RightExpression = new ConstantExpression {Value="Hi"}

}

}

}

}

};

Ophir, I am either not getting this as a solution or I am not asking my question correctly. 

I have a custom field associated with Tasks which API name is "c_TaskGroup"

In that Custom field I have values of which one is "Module"

Requirement : I want to return all tasks in all projects that have "Module" in "c_TaskGroup"

"c_taskGroup.c_Module" in your code appears to reference a field called "c_Module" inside of "c_TaskGroup". I do not have a custom field called "Module". I hope that makes it clearer.

A bit more clarity: Workitem does not have a field called "c_taskGroup" but Task does have a field called "cTaskGroup"

Much Appreciated...

Hi Bill.

I was thinking of something else, so thanks for clarifying.

Reference fields in the API have a value of type EntityId. 

So "Module" is a specific work item in Clarizen. The best options is to use

RightExpression = new ConstantExpression {Value=new EntityId {TypeName="Task", Value=<the "Module" API (external) ID>} }

If you don't know the Id of that Module, query for it first, or use the example from my previous post, just instead of C_Module use the "Name" field.

Hope this helps

Ophir

OK. So its my understanding that I cannot write one query that retrieves all tasks that have a certain value in a custom field. I would have to query every task to get its ID, check if it has the correct value in the custom field and then add it to a table. That would mean a very large number of calls to the API.

I just want to make sure before I write that code, that it is the only option. And there is no way to write something like this:

condition.LeftExpression = new FieldExpression() { FieldName = "Workitem.Task.c_TaskGroup" };

condition.Operator = Operator.Equal;

condition.RightExpression = new ConstantExpression() { Value = "Module" };

Thanks,

Bill

Hi Bill.

Actually you CAN achieve that with a single API call.

It was my understanding that "Module" is a single Task in Clarizen. As such it has an Id and a name (let's say it's "Module name". If you need to retrieve all work items that have C_TaskGroup  pointing to "Module" you'd use:

Where = new Compare

{

LeftExpression = new FieldExpression {FieldName="c_TaskGroup"},

and one of the below (I just typed it, it may need corrections:}

1. If you know the Id of "Module" (which is a single task as mentioned above)
   Operator = Operator.Equal,
   Value = new ConstantExpression {Value = new EntityId {TypeName="Task", Value="<Id of "Module">}}
2. If you don't know the Id of "Module"
   Operator = Operator.In
   Value = new QueryExpression {Query = new EntityQuery
   {
       TypeName = "Task",
       Where = new Compare { LeftExpression = new FieldExpression {FieldName="Name"},
   Operator = Operator.Equal,
   RightExpression = new ConstantExpression {Value = "<Module name"}

Anyway, your condition is on the c_TaskGroup field and the filter can be the exact Id of "Module", or a sub query that searches for it.

I hope this helps

Ophir

Ophir,

In the following code I am trying to return all tasks that have the value "red" in the field taskGroup(c_taskgroup):

I know this syntax is incorrect but I am still at a loss as to how I return all tasks from Clarizen where the custom field "TaskGroup" has the value of "red". I do not have any ID's of the tasks since if can query for the ID I would have the tasks.

Please give me guidance on syntax to get this information. 

Also, is their more in depth reference and examples on Clarizen API Queries. What I have found seems pretty thin. Thanks.

EntityQuery eq = new EntityQuery

{

TypeName = "Task",

Where = new Compare

{

LeftExpression = new FieldExpression { FieldName = "c_TaskGroup" },

Operator = Operator.In,

RightExpression = new ConstantExpression

{

Value = new QueryExpression

{

Query = new EntityQuery

{

TypeName = "Task",

Where = new Compare

{

LeftExpression = new FieldExpression { FieldName = "c_TaskGroup" },

Operator = Operator.Equal,

RightExpression = new ConstantExpression { Value = "red" }

}

}

}

},

}

};

Hi Bill.

Let's take this step by step.

What exactly is "red"? Is it a name of a task (you mentioned C_TaskGroup is a reference to object of type Task)?

How does you application decide it queries for tasks in group "red"?

Does it choose it from a list of Task groups actually available in your account's data, or is it a value "hard coded" in your application (including a list of group names coming from anywhere but your Clarizen account data)?

The point is the value of C_TaskGroup _field is not "red", unless _C_TaskGroup is a string field (I understand it is not). 

"red" is a just the name of a task in the system. The actual value is of type EntityId which represents a technical ID of a Clarizen data object (any data object accessible through the API). 

My previous reply on this (the second option) should work assuming "red" is a name of a task in Clarizen.

Hope that clarifies a bit

Ophir

Ophir,

Thanks. See responses below.

• Ophir Kenig
  

 

Hi Bill.

Let's take this step by step.

What exactly is "red"? Is it a name of a task (you mentioned C_TaskGroup is a reference to object of type Task)?

'red" is the value in the custom field "task Group"(c_taskgroup) that is associated with the task work items. It could be 'red", "blue", "green", etc. 

-

How does you application decide it queries for tasks in group "red"?

The application is hardcoded to select all tasks with the value of "red" in the TaskGroup custom field 

-

Does it choose it from a list of Task groups actually available in your account's data, or is it a value "hard coded" in your application (including a list of group names coming from anywhere but your Clarizen account data)?

The point is the value of C_TaskGroup _field is not "red", unless _C_TaskGroup is a string field (I understand it is not). 

c_taskGroup is a picklist 

-

"red" is a just the name of a task in the system. The actual value is of type EntityId which represents a technical ID of a Clarizen data object (any data object accessible through the API). 

"red" is one of several values in the picklist field c_TaskGroup 

-

My previous reply on this (the second option) should work assuming "red" is a name of a task in Clarizen.

Hope that clarifies a bit

Ophir

 

So I am still unclear how to achieve the following, which on the surface seems straightforward: Return all tasks with the picklist value "red" in the custom field "c_taskgroup" using clarizens query structure.

Hi Bill,

Now I get it! 

A pick list value is also of type EntityId . The Value  is simply the name of the pickup value. The TypeName resembles the API name of the field, but you'd better figure it out explicitly using DescribeEntitiesMessage specifying "WorkItem". Look for the C_TaskGroup field in the DescribeEntitiesResult  Fields collections and check out the ReferencedEntities to figure out the correct TypeName.

The TypeName of the PickList entity is expected to be C_WorkItemTaskGroup or C_GenericTaskTaskGroup.

When you have the correct TypeName value your query goes like that:

EntityQuery eq = new EntityQuery

{

TypeName = "task",

Fields = new string[] { "name", "C_TaskGroup" },

Where = new Compare

{

LeftExpression = new FieldExpression { FieldName = "C_TaskGroup" },

Operator = Operator.Equal,

RightExpression = new ConstantExpression { Value = new EntityId {TypeName ="c_workitemTaskGroup", Value = "red" } }

}

};

Hope this helps,

Ophir

Eureka!

I appreciate the patience. I have the data downloaded.

Bill C

Facing issue during query to Clarizen objects from salesforce through http call out ----

Hi,

I am referring the Clarizen Rest Api Guide  https://success.clarizen.com/entries/57664667-Clarizen-REST-API to make call out to Clarizen. However I became successful to send login request and received UserId,sessionId,organizationId,serverTime,licenseType as the response with [Status=OK, StatusCode=200] .

But I am facing the issue when I want to make a query to the Timesheet object of Clarizen after getting successfully Login response.

"errorCode": "LoginFailure", 

"message": "Authentication cookie is missing. Did you forget to login?", 

"referenceId": "hmHpu9JvvEKr~SiJGCaAVw"

This is my code which I have used to make query

String strRequest = '{typeName:"Timesheet",fields:["createdBy"]}'; 

httpReq.setHeader('Content-Length', String.valueOf(strRequest.length())); 

httpReq.setHeader('Content-Type', 'text/JSON');

httpReq.setMethod('POST'); 

httpReq.setEndpoint(' https://api2.clarizen.com/v1.0/services/data/entityQuery'); 

httpReq.setHeader('SESSIONID', 'USID='+sessioinId); 

httpReq.setBody(strRequest);

Http http = new Http();

HttpResponse res = http.send(httpReq);

Please help me.

I have tried by modifying the request in so many ways but same exception I am getting.

Thank you in advance

the session should be placed in a cookie:

httpReq.setHeader("Cookie", "USID=" + sessionId);

I have an entity and fieldname as Work Item.Project.MajorCode = "somevalue"

I want to write the query for it where MajorCode is my custom field as C_MajorCode.

I have tried it like

EntityQuery query = new EntityQuery();

{

query.setTypeName("Project");

StringList l1 = new StringList();

String s1[] ={"Name","C_MajorCode"};

l1.setString(s1);

query.setFields(l1);

Condition con = new Condition();

query.setWhere(con);

Compare condition = new Compare();

FieldExpression e2 = new FieldExpression();

e2.setFieldName("C_MajorCode");

condition.setLeftExpression(e2);

condition.setOperator(Operator.Equal);

ConstantExpression e3 = new ConstantExpression();

EntityId id = new EntityId();

id.setValue(loginResult.getLoginResult().getUserId());

id.setTypeName("C_MajorCode");

id.setValue("somevalue");

e3.setValue(id);

condition.setRightExpression(e3);

query.setWhere(condition);

Can you suggest me how would it work as i want to retrieve the value for WorkItem.Project.MajorCode Equal "Somevalue"

Hi Shweta,

Is c_MajorCode a picklist field?

Are you trying to query the Projects, or the work items of the project?

I am trying to query the work items of the project.The query would work like:

Work Item.Project.MajorCode where

WorkItem is an entity , Project is field and c_Major code is the custom field

Hi Ophir Kenig,

Can you please let me know the rest request and the response to query the Milestones where Last Modified On < some date  and ParentProject ( some field value= xyz)?

I am good now to query the milestones between which are modified betweeen certain detes, now the issue is how can add another condition which will check parent project some field values is yes/no. 

If you can provide the sample json request and response for the same, it will be great help for me.

Thanks,

Sreekanth.

Hi Sreekanth,

The Where property of the query is of type Condition. Condition can be of types: Compare, And, Or. For And & O_r_ you can specify an array of Conditions, each of them is a Condition itself, so you can nest them. There's a Compare sample in the documentation. For And & Or check https://api.clarizen.com/V1.0/services/types/And   &  https://api.clarizen.com/V1.0/services/types/Or

If you want to query the parent project, you could use a Compare condition with a FieldExpression for the Parent Project field and the In operator. The RightExpression would be a QueryExpression with a Query Property containing an entity query.

See: 

https://api.clarizen.com/V1.0/services/types/QueryExpression

https://api.clarizen.com/V1.0/services/types/EntityQuery

You can also check other posts here for the SOAP API demonstrating the use of the In operator

Hope this helps,

Ophir