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?