I am trying to get a list of Users within within a group using the REST API to do a CZQL. When I use LIKE on the Container I am getting the error below.
Any suggestions for a better way to do this with CZQL?
URL
https://api2.clarizen.com/v2.0/services/data/query
POST DATA
{
"q": "SELECT Container, Member FROM GroupMembershipLink WHERE Container LIKE '/UserGroup/%' ",
"paging": {
"limit": 100
}
}
*Response *
{'status': '500', 'content-length': '207', ...}
{
"errorCode": "InvalidOperation",
"message": "Invalid operation: Operation 'Like' is incompatible with field 'Container' of 'ObjectIdentifier' data type",
"referenceId": "1aEOgrfBrznnCH9VuPPRRV"
}
Please sign in to leave a comment.
That's correct. You can use the Like operator only with fields of type String. Although the ID field behaves like a string it is actually a reference to another object.
Why are you using Like? if you're trying to get the list of users in a specific group, just compare it to that specific group ID:
"SELECT Container, Member FROM GroupMembershipLink WHERE Container='/UserGroup/GroupID'
How do I get the Group ID. I tried the following without success:
URL
https://api2.clarizen.com/v2.0/services/data/query
POST DATA
{
"q": "SELECT Name FROM Group WHERE Name = 'MY GROUP NAME'",
"paging": {
"limit": 100
}
}
Response
{'status': '500', 'content-length': '141', ...}
{
"errorCode": "General",
"message": "Invalid Query: Invalid input found: 'Name' (1:30)",
"referenceId": "33UvhnRiP5cRGaYAfBHqkG"
}
Hi Joel,
Try using DiscussionGroup or UserGroup instead of Group. This worked better for me.
We'll look into it further.
I hope this helps,
Ophir
Thanks. I want to understand how I can use WHERE in the queries. Right now I have a workaround... I am downloading all of the groups and then looking at only the one I need.
That is fine for UserGroups because there are not that many, but when I am looking at Timesheet or WorkItem, I have thousands and would like to cut back the returned items.
Why is the second query failing. The first one I understand now, but not the second.
Joel
Hi Joel,
If I understand correctly, here's what you need to do:
1. Get the IDs of the groups.
Now, I do not understand why your application only has the name of the group to work with. The name has to come from somewhere, and whether it's dynamically selected or statically defined, you should also have the full ID of the desired group.
Anyway, the second query (as I wrote, we'll check why it fails) can be replaced with 2 separate queries (one for UserGroup and another for DiscussionGroup). Those 2 queries can be submitted with the bulk service to save API calls overhead.
2. Once you have the Group IDs, you can query for the members with Container='<GroupEntityType>/GroupId1' [ OR Container='<GroupEntityType>/GroupId2' ...]
3. Once you have the member IDs you can query Timesheet/WorkItem with the appropriate condition (using In operator or an OR condition) for the field you need, a reference to a User I guess.
Your original question refers to the first step. On the third step here, you should already have the lists of users to filter with, so the number of returned entries should have nothing to do with the way you got the Group IDs.
Hope this helps,
Ophir
My end-goal is to pull all of the timesheet data for a time range for customers in a specific group. I have this working, but it pulls many more records than I need.
Right now I have to pull all groups, all users, programmatically compare users and groups, and then form the query on the Timesheet entity. I am also pulling all Projects and WorkItems to get the names.
If I understand WHERE and how it can be used or not used, I would like to reduce the number of records I am pulling.
Joel
Hi Joel,
Here is a sample for getting that. I assume you group customers by attaching them to a user group, and link customers to projects.
The IDs I used for the 2nd query were retrieved from the 1st and IDs for 3rd query are retrieved from the 2nd. The group id for the 1st query can be retrieved directly from Clarizen by copying the value of External Id field of the group.
select Group.Name, Entity.Name from GroupCustomerLink where Group='/UserGroup/fhc1gp0sniijw4bzi1gzyhjw3482'
returns:
"entities": [
{
"id": "/GroupCustomerLink/to4fydxhduoc86sen4yuc34i2",
"Group": {
"id": "/UserGroup/fhc1gp0sniijw4bzi1gzyhjw3482",
"Name": "Accounting/Auditing"
},
"Entity": {
"id": "/Customer/to4fydxhduoc86sen4yuc34i0",
"Name": "Customer 1"
}
},
{
"id": "/GroupCustomerLink/63ep8t5jhkayj8tmjb3x4y05t2",
"Group": {
"id": "/UserGroup/fhc1gp0sniijw4bzi1gzyhjw3482",
"Name": "Accounting/Auditing"
},
"Entity": {
"id": "/Customer/63ep8t5jhkayj8tmjb3x4y05t0",
"Name": "Customer 2"
}
}
]
select Entity from CustomerLink where Customer in ('/Customer/to4fydxhduoc86sen4yuc34i0', '/Customer/63ep8t5jhkayj8tmjb3x4y05t0')
returns:
{
"entities": [
{
"id": "/CustomerLink/3xomz2pmrxbjjll5al0misrg10",
"Entity": {
"id": "/Project/6077xwvmcgcg9ecks06j7a63v1"
}
}
],
"paging": {
"from": 1,
"limit": 100,
"hasMore": false
}
}
select ReportedBy, RegularHours from Timesheet where WorkItem in (select Id from workitem where project in ('/Project/6077xwvmcgcg9ecks06j7a63v1'))
returns:
{
"entities": [
{
"id": "/Timesheet/4qk9kw8ktw269vlqrt3czbtij0",
"ReportedBy": {
"id": "/User/fhc1gp0sniijw4bzi1gzyhjw3477"
},
"RegularHours": true
},
{
"id": "/Timesheet/5w7w56wbq92lg3bi2syz729bu0",
"ReportedBy": {
"id": "/User/fhc1gp0sniijw4bzi1gzyhjw3477"
},
"RegularHours": true
}
],
"paging": {
"from": 2,
"limit": 100,
"hasMore": false
}
}
Hope this helps,
Ophir