Best way to search?

I'm getting confused as to how to best search for records and could use some advice.  I'm using the /rest/v11/ end point.  

I have a need to find matching Accounts, Contacts and Lead that have a specific phone number in one of their phone fields.  A related need is wanting to be able to search regardless of how a number happens to be formatted in Sugar.  So, searching on 5085551212 would find "5085551212", (508) 555-1212", etc.  Finally, if possible I'd like to control which fields of the matching records are returned.

I see there is a search, globalsearch and <module>/filter and possibly others?

What is the best Sugar REST end point to use?  While I'd prefer to make one query, I can live with querying each of the three modules separately.  Similarly I can also live with  explicitly listing the phone fields I want to search to look at.  While I'd like to control the specific fields for each module type that are returned I can also live with getting all of the data for each record.

So, what is the advice and best practice?  At this point I'm playing in Postman/Curl so examples that show several requests would be really appreciated.

  • Hello Grant,

    I have not done this, but for what it's worth, I would build a custom endpoint to query each of the entities that have phone numbers and return the results per module or just with common fields like name and number to display them in a unified way, you then have the flexibility of retrieving all or some of the data from the respective modules.

    http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_8.3/Integration/Web_Services/REST_API/Ex… 

    To search for the various formatting is a little trickier as you'll need to know how people format numbers. The most common formats use brackets, dashes, spaces, and + sign for international numbers. You could use something like this, where $phone_number is stripped of all formatting.

    SELECT
    id as id
    , CONCAT(first_name, ' ', last_name) as name
    ,phone_work
    ,phone_home
    ,phone_other
    ,phone_mobile
    FROM {$module_table}
    WHERE
    deleted = 0
    AND
    (
    REPLACE(REPLACE (REPLACE (REPLACE( REPLACE(phone_work , '(','') ,')','') ,'-','') ,' ','') ,'+','') = '{$phone_number}'
    OR
    REPLACE(REPLACE (REPLACE (REPLACE( REPLACE(phone_home , '(','') ,')','') ,'-','') ,' ','') ,'+','') = '{$phone_number}'
    OR
    REPLACE(REPLACE (REPLACE (REPLACE( REPLACE(phone_other , '(','') ,')','') ,'-','') ,' ','') ,'+','') = '{$phone_number}'
    OR
    REPLACE(REPLACE (REPLACE (REPLACE( REPLACE(phone_mobile , '(','') ,')','') ,'-','') ,' ','') ,'+','') = '{$phone_number}'
    )
    ORDER BY last_name, first_name

     

    Hope this helps,

    FrancescaS

  • Thanks for replying.  You show SQL for doing a lookup but I don't see where that is part of the REST interface.  At this point, filtering Account and Contact results and searching a given number using various fixed formats works fairly well.  If I could use wildcards in calls to Contacts/filter and Accounts/filter I'd be in good shape.

    For example, this shows the body for an Accounts/filter search:

    {
    "filter":[
    {
    "$or":[
    { "phone_office":"5085551212" },
    { "phone_office":"(508)555-1212" },
    { "phone_office":"(508) 555-1212" },
    { "phone_office":"508-555-1212" },

    { "phone_alternate":"5085551212" },
    { "phone_alternate":"(508)555-1212" },
    { "phone_alternate":"(508) 555-1212" },
    { "phone_alternate":"508-555-1212" }
    ]
    }
    ],
    "fields": "id,name,phone_office,phone_alternate,description",
    "order_by": "name:ASC",
    "max_num": 10,
    "offset": 0
    }

  • The Filter API you are using is an existing Endpoint, I was suggesting writing your own endpoint and using that SQL inside the custom end point.

    I have not tried using wildcards with out of the box APIs, nor have I looked into it. I am lazy, when things get complicated I build my own API.

    You may want to look at the code for the filter API to see if they account for wildcards and how:

    clients/base/api/FilterApi.php

    I took a quick look, and it doesn't seem like wildcards are an option, but I may have missed something. 

     

    You could maybe try using $contains and check for each of the portions of the number? As in contains 508 and contains 555 and contains 1212. If it contains each of the three sets of numbers then it's probably a good hit. Though it could return hits for 508-555-1212 as well as 555-508-1212

     

    Sorry I could not be more help.

    Francesca