Trouble returning deleted records with API

Hello,

Using Postman, I'm attempting to return a list of contacts which includes records that have been deleted from Sugar. To do so, I'm sending a GET request to the Contacts end point and setting the deleted flag to '1'. However, the API responds with this error: 

{
"error": "unknown_error",
"error_message": "An exception occurred while executing 'SELECT contacts.last_name, contacts.assigned_user_id, contacts.acl_team_set_id, contacts.team_set_id, LTRIM(RTRIM(CONCAT(IFNULL(jt0_team_link.name,''),' ',IFNULL(jt0_team_link.name_2,'')))) team_name, jt0_team_link.created_by team_name_owner, contacts.created_by, jt1_email_addresses_primary.email_address email1, contacts.salutation, contacts.first_name, contacts.title, jt2_accounts.id account_id, jt2_accounts.assigned_user_id account_id_owner, jt2_accounts.name account_name, jt2_accounts.assigned_user_id account_name_owner, jt1_email_addresses_primary.email_address email, contacts.phone_work, jt3_assigned_user_link.first_name rel_assigned_user_name_first_name, jt3_assigned_user_link.last_name rel_assigned_user_name_last_name, jt3_assigned_user_link.created_by assigned_user_name_owner, contacts.date_modified, contacts.date_entered, contacts.team_id, contacts.id, contacts.deleted FROM contacts LEFT JOIN teams jt0_team_link ON (contacts.team_id = jt0_team_link.id) AND (jt0_team_link.deleted = ?) LEFT JOIN email_addr_bean_rel contacts_email_addresses_primary ON (contacts.id = contacts_email_addresses_primary.bean_id) AND (contacts_email_addresses_primary.deleted = ?) AND (contacts_email_addresses_primary.bean_module = ?) AND (contacts_email_addresses_primary.primary_address = ?) LEFT JOIN email_addresses jt1_email_addresses_primary ON (jt1_email_addresses_primary.id = contacts_email_addresses_primary.email_address_id) AND (jt1_email_addresses_primary.deleted = ?) LEFT JOIN accounts_contacts accounts_contacts ON (contacts.id = accounts_contacts.contact_id) AND (accounts_contacts.deleted = ?) AND (accounts_contacts.primary_account = ?) LEFT JOIN accounts jt2_accounts ON (jt2_accounts.id = accounts_contacts.account_id) AND (jt2_accounts.deleted = ?) LEFT JOIN users jt3_assigned_user_link ON (contacts.assigned_user_id = jt3_assigned_user_link.id) AND (jt3_assigned_user_link.deleted = ?) WHERE () ORDER BY contacts.id ASC LIMIT 1001 OFFSET 0' with params [0, 0, \"Contacts\", 1, 0, 0, 1, 0, 0]:\n\nYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY contacts.id ASC LIMIT 1001 OFFSET 0' at line 1"
}

Am I doing something incorrectly in my request, or is it not possible to return deleted records? 

And my request: 

{{url}}/rest/v11/Contacts?fields=last_name,assigned_user_id,team_name,created_by,email1&max_num=1000&offset=0&order_by=id&deleted=1

 

Am I doing something incorrectly in my request, or is it not possible to return deleted records? 

  • Hi Vaughn Okerlund,

    I dont think we can get the deleted records from out of the box APIs, you can try writing custom API and returning the deleted records in it.

    Thanks

    Venkatesh

  • Hi Venkatesh,

    Thanks for the reply. I notice that in the documentation for the GET /module endpoint it lists the following optional field which seems to suggest that it should be possible:

    "deleted      Boolean     Boolean to show deleted records in the result set."

    Is showing the deleted records different than returning them? 

  • It should be returning deleted records. What version of Sugar are you using Vaughn?

  • I've been trying with the V11 API and Sugar 8, do any permissions or anything need to be set on the Sugar instance to allow the field to be used? 

    Here's an example of the request I've been using:

    {{url}}/rest/v11/Contacts?fields=last_name,assigned_user_id,team_name,created_by,email1&max_num=1000&offset=0&order_by=id&deleted=1

  • Hi Vaughn Okerlund

    when you delete a record, the information in this record should not be visible to anyone any more.


    So you can retrieve deleted records from the Sugar Database, but the only data you get back is the id of these records.
    If you send a rest/v11/Contacts?filter=[{"first_name":{"$starts":"So"}}]&deleted=true you will get all ids of all records including the deleted records whose first_name start with "So".

    For the not deleted records you get all information you are allowed to see.

    For the deleted records you get only something like:


    {
    "id": "84f75e48-5ab1-11e8-8cb9-0024d72fcb98",
    "deleted": true,
    "_module": "Contacts"
    }


    If you send a rest/v11/Contacts?filter=[{"deleted":true}]&deleted=true you will get a list of all records which are deleted. 

    PS:

    In your REST request only the filter is missing.

    If you change it to {{url}}rest/v11/Contacts?filter=[{"deleted":true}]&fields=last_name,assigned_user_id,team_name,created_by,email1&max_num=1000&offset=0&order_by=id&deleted=1 you will get the list of deleted records (but no fields except the ids).

    You see it in the SQL statement where the WHERE clause id empty, which produces the SQL error.

  • Thanks for the response, Harald, that was very helpful and fixed my issues!