Post

2 followers Follow
0
Avatar

CZQL query WHERE condition matching item from multi-pick picklist

Hi devs,

Background:

I'm using the api to pull info from Clarizen and then populate an external dashboard.

Problem:

In the query I only want to pull the info from milestones that contain the value "Cheese" in the C_Dashboard field (a multi-pick picklist). This works as long as it is the only item selected in that field but ceases to work as soon as another value is added (eg. "Cheese" and "Wine") 

SELECT Name, Duedate

FROM Milestone WHERE (State IN ("active") AND C_Dashboard LIKE "Cheese")

 

I tried both the equal operator or LIKE operator and both show the same behaviour. I am essentially looking for a contains operator.

 

Thanks

Robin

Robin Head Answered

Please sign in to leave a comment.

8 comments

0
Avatar

Hi Robin,

Try this:

FROM Milestone WHERE (State IN ("active") AND C_Dashboard LIKE "%Cheese%")

Let me know if it works as expected.

 

Armand

 

Armand BOLMONT 0 votes
Comment actions Permalink
0
Avatar

Thanks Armand,

I tried it but it the query seems to fail as I get nothing returned (without the % it returns all milestones with only "Cheese" as C_Dashboard item)

 

BTW probably should have said I use Python:

def czql_query(data=None): #Generic query function
headers = {'Authorization': 'Session ' + session_id}
url = actual_url + czql_url
response = requests.post(url, data=json.dumps(data), headers=headers)
json_response = json.loads(response.text)
return json_response

def retrieve_active_milestones():
data = {'q': 'SELECT Name, Duedate FROM Milestone WHERE (State IN ("active") AND C_Dashboard LIKE "%Cheese%")'}
results = czql_query(data)
return results.get('entities')

 

Robin Head 0 votes
Comment actions Permalink
0
Avatar

You could try the "IN" syntax (as you do for State).

 

In order to try troubleshooting this case, I would identify a milestone with "Cheese" plus another value, identify its ExternalID, and request this specific Milestone with a request like

data = {'q': 'SELECT Name, Duedate, C_Dashboard FROM Milestone WHERE (ExternalID IN ("[theexternalID]"))'}

You'll know how a multiple value field is returned through a request, maybe that will help.

 

Armand

Armand BOLMONT 0 votes
Comment actions Permalink
0
Avatar

I tried the IN aswell (unsuccessfully). I assume it is an exclusive relation eg. it has to be in "Cheese" and nothing else.

 

I did try asking it to return it to me and got this:

 

For items with only Cheese:

'C_Dashboard': '/C_WorkItemDashboard/Cheese'

For items with both Cheese and Wine:

'C_Dashboard': '/C_WorkItemDashboard/Cheese;Wine'

 

I have tried "Cheese;Wine" to see whether I can filter for items containing both and that works fine. However, neither "Cheese;*", "%Cheese;%" nor "Cheese;%" works to select items with "Cheese" as one or more of their C_Dashboard values - it returns nothing.

 

 

Robin Head 0 votes
Comment actions Permalink
1
Avatar

Hi again,

I tried myself, unsuccessfully as well. There is a check against the value '%Cheese%', which does not exist, hence the absence of results.

I have another suggestion that would work: create a new Custom field, named Dashboard_Txt, with a formula "=ToString($C_Dashboard)", then query from this custom field with LIKE '%Cheese%'.

The drawback is that you'd have to create a formula custom field just for that.

The last option would be to query all milestones, then filter the data yourself in your code.

Armand BOLMONT 1 vote
Comment actions Permalink
0
Avatar

Thanks Armand!

Yes, I can think of a few workarounds (like the additional custom text field, or filtering in my code) but thought there must be something that I'm missing ...

Robin Head 0 votes
Comment actions Permalink
0
Avatar

Hi,

I don't understand why this has been marked as answered as we clearly have not been able to work out whether this is something that is possible and we just don't know the syntax or whether this is not possible and we have to use workarounds?

 

Thanks

Robin

Robin Head 0 votes
Comment actions Permalink
0
Avatar

ANSWER:

I've been in contact with Clarizen and this functionality is currently not supported.

Robin Head 0 votes
Comment actions Permalink