Post

2 followers Follow
0
Avatar

Retrieving Deleted Timesheet Entries from the API

I'm trying to build a Timesheet sync to our CRM system but having some trouble retrieving deleted items and keeping things in sync.

For background, I use the following CQZL query to retrieve queries for a list of open projects in our CRM:

$params = [
'q' => "SELECT ExternalId,ReportedDate,LastUpdatedOn,Project.ExternalId,Project.InternalId,Duration,ReportedBy.ExternalId, ReportedBy.Name, Comment, C_API_Timesheet_Type.Name, C_WBPointsCredits, C_WorkbooksSolutionCentreCaseRef
FROM Timesheet
WHERE Project IN (
SELECT ExternalId
FROM Project
WHERE Project = '{$project_id}'
) AND LastUpdatedOn > '{$last_run_datetime}'
LIMIT {$no_of_records}
OFFSET {$offset}",
];

$this->clarizen = $this->curl_request('/data/query', $params, 'post', $session);

This returns something like...

{"entities":[{"id":"/Timesheet/55tyrmmkuq8gmcm51xd1uhrea17","ExternalId":"55tyrmmkuq8gmcm51xd1uhrea17","ReportedDate":"2019-04-05T00:00:00.0000000","LastUpdatedOn":"2019-04-05T13:24:56.1846150","Project":{"id":"/Project/3lzizutl3xzj6zpob2d237fq1197","ExternalId":"3lzizutl3xzj6zpob2d237fq1197","InternalId":"CASE-56256"},"Duration":{"unit":"Hours","value":1.0},"ReportedBy":{"id":"/User/818f4bc2-908e-42ea-a3e7-cea11c9b302e","ExternalId":"818f4bc2-908e-42ea-a3e7-cea11c9b302e","Name":"Jamie Lowe"},"Comment":"new test","C_API_Timesheet_Type":{"id":"/C_TimesheetAPI_Timesheet_Type/Internal Project","Name":"Internal Project"},"C_WBPointsCredits":2.0,"C_WorkbooksSolutionCentreCaseRef":""}],"paging":{"from":1,"limit":100,"hasMore":false}}

From the result of this query, I store the ExternalID in our CRM. Once these are stored, of course users can delete the timesheets from Clarizen so we want to reflect this in the CRM so that we do not over bill our clients. I run the following query using the entityQuery endpoint:

$params = [
'typeName' => 'Timesheet',
'deleted' => 'true',
'where' => [
'leftExpression' => ['fieldName' => 'Project'],
'operator' => 'In',
'rightExpression' => [
'values' => [
$project_id,
]
],
],
'fields' => 'ExternalId,Duration,Comment,Project'
];
$this->clarizen = $this->curl_request('/data/entityQuery', $params, 'post', $session);

This retrieves me the correct set of results for deleted Timesheet Entries (based on what I have deleted from the UI), but the ExternalIDs are different from when the Timesheet wasn't deleted... Therefore, I cannot then find them in my CRM to delete! The result is something like this:

{"entities":[{"id":"/Timesheet/85C92FA75D703921E0535A2010AC6E35","ExternalId":"85C92FA75D703921E0535A2010AC6E35","Duration":{"unit":"Hours","value":0.0},"Comment":"Test 1","Project":{"id":"/Project/3lzizutl3xzj6zpob2d237fq1197"}},{"id":"/Timesheet/85C8FD0C948C62CDE0535A2010AC650C","ExternalId":"85C8FD0C948C62CDE0535A2010AC650C","Duration":{"unit":"Hours","value":1.0},"Comment":null,"Project":{"id":"/Project/3lzizutl3xzj6zpob2d237fq1197"}},{"id":"/Timesheet/85C92FA75D713921E0535A2010AC6E35","ExternalId":"85C92FA75D713921E0535A2010AC6E35","Duration":{"unit":"Hours","value":1.0},"Comment":"Test 2","Project":{"id":"/Project/3lzizutl3xzj6zpob2d237fq1197"}},{"id":"/Timesheet/85C96BCC372662A9E0535A2010ACEE4A","ExternalId":"85C96BCC372662A9E0535A2010ACEE4A","Duration":{"unit":"Hours","value":1.0},"Comment":"new test","Project":{"id":"/Project/3lzizutl3xzj6zpob2d237fq1197"}}],"paging":{"from":4,"limit":100,"hasMore":false}}

I've also tried this with the timesheetQuery endpoint too but get the same problem. Is this a bug with the API? If so, how do I workaround it please? There doesn't appear to be anyway to now tie the deleted entries back to their original Timesheet ID from what I can see as the available fields on the deleted entry are the same as when the timesheet is not deleted?

 

Jamie Lowe Answered

Official comment

Avatar

Hi Jamie,

Indeed, when an item is deleted in Clarizen, its ExternalId changes. This allows to reuse the same id for new items.

For deleted items you can query the "RecycleBinItems" with filter by "DeletedEntityType" (endpoint: /data/entityQuery):

{
"typeName": "RecycleBinItems",
"fields": ["DeletedEntityType","DeletedExternalId"],
"where": {
"leftExpression": { "fieldName": "DeletedEntityType" },
"operator": "Equal",
"rightExpression": {"value" : "Timesheet"}
}
}

Response:

{
"entities": [
{
"id": "/RecycleBinItems/401-332-26frl8sgjrkv5rpzdk50k89a67",
"DeletedEntityType": "Timesheet",
"DeletedExternalId": "26frl8sgjrkv5rpzdk50k89a67"
}
],
"paging": {
"from": 1,
"limit": 1000,
"hasMore": false
}
}

This will provide you actual ExternalId for deleted items.

Regards.

Alex Broitman

Please sign in to leave a comment.

2 comments