CZQL query WHERE condition matching item from multi-pick picklist

Hi Robin,

Try this:

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

Let me know if it works as expected.

Armand

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')

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

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.

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.

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

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

ANSWER:

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