Trouble returning deleted records with API


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(,''),' ',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, account_id, jt2_accounts.assigned_user_id account_id_owner, 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.deleted FROM contacts LEFT JOIN teams jt0_team_link ON (contacts.team_id = AND (jt0_team_link.deleted = ?) LEFT JOIN email_addr_bean_rel contacts_email_addresses_primary ON ( = 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 ( = contacts_email_addresses_primary.email_address_id) AND (jt1_email_addresses_primary.deleted = ?) LEFT JOIN accounts_contacts accounts_contacts ON ( = accounts_contacts.contact_id) AND (accounts_contacts.deleted = ?) AND (accounts_contacts.primary_account = ?) LEFT JOIN accounts jt2_accounts ON ( = accounts_contacts.account_id) AND (jt2_accounts.deleted = ?) LEFT JOIN users jt3_assigned_user_link ON (contacts.assigned_user_id = AND (jt3_assigned_user_link.deleted = ?) WHERE () ORDER BY 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 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: 



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

Parents Reply Children
  • 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? 

  • 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. 


    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.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    SugarCRM Deutschland GmbH