CODEWITHSUNDEEP
sqljsonsugarcrm
Azoulay Jason
Azoulay Jason

Reputation: 2999

How to get a record by anything else than it's id - SugarCRM

The question might be unclear to you but my problem is very simple

The SugarCRM documentation suggests that to get a record, I should send a GET request to /<module>/:recordId.

What I want to achieve is to get a record by its email.

In previous version of SugarCRM, I could send an SQL query to their API in the payload request. But here since it is a GET request there is no body.

I need help for this

Upvotes: 2

Views: 998

Answers (1)

Jay
Jay

Reputation: 3950

To get records by matching non-id fields you can use the record filter API /<module>/filter (see documentation or /rest/v10/help of your Sugar) to specify which field(s) to search for what values.

If you only care for one matching record at most, specify "max_num": 1 in the options.

As response you will receive a json object with a records array containing the matching record(s).

Filtering by email address

Email addresses were previously stored in fields email1, email2, etc. Those legacy fields do still exist in Sugar >=7 (for now) and could be used with a request payload like this:

{
    max_num: 1,
    filter: [
        {"email2": "[email protected]"},
    ],
    fields: ["id"],
}

This will search for the email address in only the secondary email-address.

However, in modern Sugar, email addresses are stored in a relationship-backed link field called email_addresses that connects to EmailAddress records which can be searched like these:

{
    max_num: 1,
    filter: [
        {"email_addresses.email_address": {"$equals": "[email protected]"}},
    ],
    fields: ["id"],
}

This will return an record with any email address matching. Regardless of whether it's the primary one or another one.

Random related notes

  • The filter endpoint apparently supports both the GET and the POST method, so you can choose whether to transfer the filter definition and options via the request's query string or in the request body.
  • GET requests to the /<module> endpoint will use the same underlying filter API. However you cannot filter using POST on /<module>, because that combination is reserved for creating new records.
  • If you are unsure about what your search criterias should look like in Sugar filter syntax, you might be able to use Sugar to create an example filter in the modules list view and check in the browser's debugger's network tab what Sugar's resulting request payload looked like.
  • If you need a wildcard search, you can accomplish that by using the wildcard character %, e.g. for %@whatever.test, seems to require using a different operator than $equals though, e.g. $starts or $contains.
  • If you only care about the number of matches, not the actual records, send your filter to the /<module>/count (GET) endpoint or to /<module>/filter/count (GET or POST).
  • During development testing I recommend using a max_num higher than what you expect to be returned, to make sure you see it immediatly if you get any false positives in your results due to an incorrect filter definition. That or pay close attention to next_offset in the response json: It will have the value -1 if there are no more results than those already returned in the response.
  • There is a short notation for the $equals operator, e.g. {"email_addresses.email_address": {"$equals": "[email protected]"}}, could also be written as {"email_addresses.email_address": "[email protected]"},.

Upvotes: 3

Related Questions