How can I retrieve fields from related records in a REST filter POST request?

I'm using the filter endpoint to retrieve some fields from matching Contacts.  Specifically I'm doing a POST to the {URL}/rest/v11/Contacts/filter endpoint.  In the body I include the fields as follows:

"fields": "id,name,description"

That works great.

However, I also want to get the id and name of any accounts that are related to the retrieved contacts.  Per the help page for the filter GET request it seems like I should be able to.  Specifically, this page:

https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.9/Integration/Web_Services/v10/Endpoi… 

Shows in the example for the files field the following:

Example: name,account_type,description,{"name":"opportunities","fields":["id","name","sales_status"],"order_by":"date_closed:desc"} 

 

Note the bolded italics.  In this case it looks like the query would return details of related opportunities.  Based on this example I tried the following:

 

"fields": "id,name,phone_work,phone_mobile,phone_home,phone_other,description,{'name':'accounts','fields':['id','name']}"

 

But this only returns details of the contact and not of the related account.

 

I notice this similar posting for a couple of years ago (SugarCRM REST API - Filter POST - Related Module Fields ) so this seems like something tha#t Sugar needs to do a better job documenting.

  • First of all, the principle behind the RESTful API is that each token defines the action which should be done with a call.

    So you have the GET token for reading, the POST token for creating, the PUT token for updateing and the DELETE token for deleting. SugarCRM RESTful API since version 10 supports these for tokens.

    Let's see some example:

    First you have to authenticate:

    POST .../rest/v10/oauth2/token

    creates a new OAuth-Token and returns it to be used for the following calls in the header

    GET .../rest/v10/Contacts?filter=[{"$and":[{"last_name":{"$starts":"B"}},{"first_name":{"$starts":"A"}}]}]

    reads all Contacts where the last name starts with a B and the first name starts with an A.

    e.g. you could get some result like:

    {
    "next_offset": -1,
    "records": [
    {
    "id": "e4fa1684-69d2-11e8-99b6-025041000001",
    "name": "Arthur Brickey",
    "date_entered": "2018-06-06T23:42:22+02:00",
    "date_modified": "2018-09-19T14:40:09+02:00",
    ...
    "description": "",
    "deleted": false,
    "salutation": "",
    "first_name": "Arthur",
    "last_name": "Brickey",
    "full_name": "Arthur Brickey",
    "title": "Director Operations",
    ...
    "lead_source": "Public Relations",
    "account_name": "AtoZ Co Ltd",
    "accounts": {
    "name": "AtoZ Co Ltd",
    "id": "a197d520-69d2-11e8-bce6-025041000001",
    "_acl": {
    "fields": [],
    "_hash": "654d337e0e912edaa00dbb0fb3dc3c17"
    }
    },
    "account_id": "a197d520-69d2-11e8-bce6-025041000001",
    ...

    In this result you can see the account linked with the contact. As there is only one account linked to a contact t is listed directly.

    You see that in the contact only the account_id and account_name are directly available. If you want to have more details from the account you must call another GET on the specific account.

    So, if you have an account and want to know all the related contacts you just call

    GET .../rest/v10/Accounts/<id of the account>/link/contacts

    with the above example result e.g.

    GET .../rest/v10/Accounts/a197d520-69d2-11e8-bce6-025041000001/link/contacts

    which returns all data of all related contacts of the account AtoZ Co Ltd.

    If you want to minimize the data payload of the result you can specify the fields or view argument in the GET call.

    All possible calls and possible parameters are described in the help call of your own installation:

    GET ../rest/v10/help

    It is generated on demand and shows all API calls, even custom endpoints.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

  • Thanks.  Lot of info but the key was realizing that of course, if I don't specify any "fields" param in the body of my request then I get all the data including the account details.  Making a query of a contact with an account in Postman shows that the account info for a contact can be easily retrieved by passing the fields "account_name" and "account_id" as shown below.  I also get the "accounts" array back which I don't need but that's fine.  If I just ask for "accounts" instead of "account_name" and "account_id" then I get back just the accounts array.

    "fields": "id,name,phone_work,phone_mobile,phone_home,phone_other,description,account_name,account_id"