Post

2 followers Follow
0
Avatar

CZQL count Link

Dear Clarizen R&D team,

  

According to Clarizen API documentation it is possible to execute aggregate CZQL query:

https://api.clarizen.com/V2.0/services/data/Query

e.g.

SELECT COUNT(ExternalID) AS RowCount FROM Task

 

When I tried to execute the same on a Link type of entity the API did not return the expected result (it returned RowCount = 1):

e.g.

SELECT COUNT(ExternalID) AS RowCount FROM RegularResourceLink

 

Could you please help me with the right CZQL expression to get the count of a Link type of entity?

  

Thanks and Best Regards,

Istvan

Istvan Szabo Answered

Official comment

Avatar

Hi Istvan,

Currently this is not supported in our API.

What you can do it to get tasks with their resources as a relation and then count the number of resources using your code.

The query should look like this:

"SELECT Name,(select Name FROM Resources) FROM Task"

And the response will look something like this:

{
   "entities": [
   {
      "id": "/Task/981f223f-fe76-4dea-8852-cdab69a7258c",
      "Name": "Task with no resources",
      "Resources": null
   },
   {
      "id": "/Task/5w7zux8fch4mfyvxdl9eyan3c0",
      "Name": "Task with 1 resources",
      "Resources": {
         "entities": [
          {
            "id": "/User/2qwgydsncnkqz7734kilbkxv52101",
            "Name": "Aida"
          }
                 ],
          "paging": {
               "from": 1,
               "limit": 30,
               "hasMore": false
          }
      }
     },
   ],
   "paging": {
      "from": 2,
      "limit": 100,
      "hasMore": false
  }
}

 

I hope this helps,

Elad

Elad Franklin
Comment actions Permalink

Please sign in to leave a comment.

6 comments
Date Votes

0
Avatar

Hi Elad,

Thanks for your reply!
I'm afraid the suggested solution will not work to achieve the desired result.

Let me give you more context what I would like to do:
We have a custom Currency type of field under RegularResourceLink entity.
I'd like to have the full dataset of RegularResourceLink so I can join it with Resource and WorkItem in our custom ETL process.

I know in the Excel add-in there we can see the "Number of items" for Link type of entities - see screenshot below.
I thought Excel add-in is also using the API to get the results?

Thanks,
Istvan

Istvan Szabo 0 votes
Comment actions Permalink
0
Avatar

Hi Istvan,

It looks like the Excel add-in just give you the total number of RegularResourceLink in your account. From what I understand this is not what you want.

I think I still not fully understand your exact needed result. If you want the full dataset how will the number of links help you?

 

Elad Franklin 0 votes
Comment actions Permalink
0
Avatar

Hi Elad,

This is exactly what I need: the total number of items under RegularResourceLink:

SELECT COUNT(ExternalID) AS RowCount FROM RegularResourceLink

When I'm downloading the data from API I'd like to show the progress when fetching the data page by page:

e.g.

1000 downloaded out of x

2000 downloaded out of x

where x = total number of items

Hope this clarifies the question.

Thanks,

Istvan

Istvan Szabo 0 votes
Comment actions Permalink