Post

2 followers Follow
0
Avatar

Retrieving customer name with timesheet data using API

I'm trying to retrieve timesheet data (approved hours) along with some information about each entry. I'm getting nearly everything I want, but I can't figure out how to get the customer name(s) in the timesheet.

Here's what I got so far:

I send a POST request to https://api.clarizen.com/v2.0/services/data/entityQuery

The POST data is:

{
"typeName": "Timesheet",
"fields": [
"ReportedBy.Email",
"ReportedDate",
"Duration",
"Project.Name",
"WorkItem.Billable",
"WorkItem.Name"],
"orders": [
{
"fieldName": "ReportedDate",
"order": "Ascending"
}],
"where": {
"and": [
{
"leftExpression": {"fieldName": "State"},
"operator": "Equal",
"rightExpression": {"value": "Approved"}
},
{
"leftExpression": {"fieldName": "ReportedDate"},
"operator": "GreaterThanOrEqual",
"rightExpression": {"value": "2015-12-01"}
}
]
}
}

This works fine, but I'd like to get the customer names for each entry in the timesheet back as well. I could use multiple requests, but I'm hoping that's not necessary.

Here's the curl command if you want to test (replace the session id with your own):

curl -d '{ "typeName": "Timesheet", "fields": [ "ReportedBy.Email", "ReportedDate", "Duration", "Project.Name", "WorkItem.Billable", "WorkItem.Name"], "orders": [ { "fieldName": "ReportedDate", "order": "Ascending" }], "where": { "and": [ { "leftExpression": {"fieldName": "State"}, "operator": "Equal", "rightExpression": {"value": "Approved"} }, { "leftExpression": {"fieldName": "ReportedDate"}, "operator": "GreaterThanOrEqual", "rightExpression": {"value": "2015-12-01"} } ] } }' -H "Authorization: Session FOOBAR-FOOBAR-FOOBAR" https://api.clarizen.com/v2.0/services/data/entityQuery

Audun Rundberg Answered

Official comment

Avatar

It's not possible because customers are linked to the project and not directly to the timesheet. One way to achieve that is to create a custom field on the Project level which contains a reference to the customer and then you will be able to query using Project.C_Customer.Name (where C_Customer is the new custom field). You can populate that field using workflow rules (note that having this field assumes that you have a single customer per project)

Eyal Post
Comment actions Permalink

Please sign in to leave a comment.

4 comments
Date Votes

0
Avatar

 

 

 

Thanks. I've looked at creating a report for this (not the API), and using the reports editor I can get what I want.

To be clear on what I'm trying to achieve here, it is a list with the follo:

  • Name/email of the person who has reported hours
  • Customer name
  • Project name
  • Work Item name
  • Reported Date
  • Duration (# of reported hours)

When I select these options in the report builder:

Report on: Timesheet

Include related data: Project

I can access <Timesheet>.<Project>.Customers in the report builder. See this screenshot of the report configuration:

 

So far so good, but I'd like to try the same in the API so we don't have to download the report manually.

Is it not possible to access the equivalent of Timesheet.Project.Customers in one single query in the API without using custom fields? I've looked at relations for timeSheetQuery (since it looks like the report builder is using relations to achieve what I want), but it seems that Timesheet has no relations in the API. I tried describeEntityRelations for Timesheet, but it's empty.

Audun Rundberg 0 votes
Comment actions Permalink
0
Avatar

It is available in the reports but not in the API. You won't be able to accomplish that in 1 query but there is a way to do it in 2 queries:

Assuming your original query retrieves all timesheets reported after Jan 1st (using CZQL):

"select Duration, <any other fields>  from Timesheet where CreatedOn>2016-01-01"

to get the customers of project for those same timesheets you would do something like this:

"select Name, (Select name from Customers) from Project where ID in (select Project from Timesheet where CreatedOn>2016-01-01) "

Note the inner subquery in the seconds query. It should have the same filter as the first query you use to get timesheets.

Eyal Post 0 votes
Comment actions Permalink