Post

4 followers Follow
0
Avatar

Query Value of Custom Field in API

I have a custom field and am trying to write a query on the value of that custom field. Can I get some guidence on the structure of:

condition.RightExpression = "Value of Custom Field"

 

I get references to the custom field entity but not the value itself. What is the proper way to drill down to the actual value in a right expression?

 

I have queried on different criteria and have gotten results in this field, so it is finding the data, I just can't use it to search on.

 

condition.LeftExpression = new FieldExpression() { FieldName = "C_TaskGroup" }; //custom field

condition.Operator = Operator.Contains;

condition.RightExpression = new ConstantExpression { Value = "Module" }; //looking for the value module in the field

 

 

Thanks,

 

Bill

Bill Caughron Answered

Please sign in to leave a comment.

19 comments

0
Avatar

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.

Bill Caughron 0 votes
Comment actions Permalink
0
Avatar

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 Kenig 0 votes
Comment actions Permalink
0
Avatar

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

Bill Caughron 0 votes
Comment actions Permalink
0
Avatar

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

Ophir Kenig 0 votes
Comment actions Permalink
0
Avatar

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

 

Bill Caughron 0 votes
Comment actions Permalink
0
Avatar

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 Kenig 0 votes
Comment actions Permalink
0
Avatar

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" }

}

}

}

},

}

};

Bill Caughron 0 votes
Comment actions Permalink
0
Avatar

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 Kenig 0 votes
Comment actions Permalink
0
Avatar

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.

Bill Caughron 0 votes
Comment actions Permalink
0
Avatar

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

Ophir Kenig 0 votes
Comment actions Permalink
0
Avatar

Eureka!

I appreciate the patience. I have the data downloaded.

Bill C

Bill Caughron 0 votes
Comment actions Permalink
0
Avatar

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

 

John Gray 0 votes
Comment actions Permalink
0
Avatar

the session should be placed in a cookie:

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

Eyal Post 0 votes
Comment actions Permalink
0
Avatar

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"

 

 

 

Shweta 0 votes
Comment actions Permalink
0
Avatar

Hi Shweta,

Is c_MajorCode a picklist field?

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

Ophir Kenig 0 votes
Comment actions Permalink
0
Avatar

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

Shweta 0 votes
Comment actions Permalink
0
Avatar

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.

Sreekanth A 0 votes
Comment actions Permalink
0
Avatar

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

 

Ophir Kenig 0 votes
Comment actions Permalink