Changes to web services API in SugarCRM 6.4.0

sugarcrmdevelopers —  February 8, 2012 — 18 Comments

With the 6.4 release there is a slight change to ALL web service APIs. We have noticed that on rare occasion, developers have misused our APIs. In order to encourage better utilization and to help improve supporting multiple databases we have improved our query validation. We have also limited the ability to use subqueries in web service calls, which includes subqueries used both in the where clause as well as for the select fields. If you have used subqueries in your web service calls, please test them against a test instance of one of the above mentioned releases before you upgrade to ensure that your calls continue to function as expected.  If you do use a subquery, you will get a no access error.

When we see subqueries being used, it is often to access data inappropriately. For example one of the use cases we have seen is to get a list of meetings a user has access to.

SugarRest.call('get_entry_list',[SugarRest.session, 'Meetings', "meetings.id in (SELECT id from meetings_users WHERE user_id = '" + SugarRest.user_id + "')", '', 0, [], {}, 100, 0, false])

However, when wanting to do something like this. There is a specific web service call for traversing these relationships. get_relationships.

SugarRest.call('get_relationships', [SugarRest.session, 'Users', SugarRest.user_id, 'meetings', '', ['name'], 0, ''])

And the best part is you can pass in additional filters after the ‘meetings’ parameter to get the exact result set you want.

If you have any additional subqueries that you want to know the proper way to access the data, please post a comment below.

18 responses to Changes to web services API in SugarCRM 6.4.0

  1. 
    Jonathan Cutting February 10, 2012 at 7:04 am

    Before implementing this, it would have been very useful if SugarCRM had done the following:

    Provide a max_results in get_relationships.
    Provide an order_by for search_by_module, or at least return results in date order DESC.

    We spent a LONG TIME trying to use a combination of get_relationships and search_by_module in the Thunderbird extension, and we were unable to do so for these two reasons.

    Thanks SugarCRM, you have broken the Opacus Thunderbird extensions for SugarCRM in one upgrade, and did not see fit to give us any warning beforehand. The Opacus Thunderbird extension was project of the month on SugarForge only a few months ago.

    We will now have to rewrite the search functions for 6.4 and will only be able to offer our users a sub-standard search provided by these two badly hamstrung functions.

    Strange that you’ve left the legacy soap.php in the root alone.

    Please, please devote some of your attention in 6.4.1 to fixing the bugs I have mentioned.

    • 

       I apologize for your frustration. I am having our engineering team follow up on this immediately to try and find a resolution to this issue. I hope to have an answer soon.

      • 
        Jonathan Cutting February 11, 2012 at 1:48 am

         Thank you John. We can work around the limitations in the search, by and large, but I struggle to find a way around the problem mentioned above. How do we retrieve all contacts that have been marked for Sync to Outlook with the REST api if we can’t run a subquery on the contacts_users table? All the old sync functions are not available in v2 and up.

  2. 

     Slight?  Great, you just killed a few hundred man hours of internal integration of Sugar and our business apps.

    Set_entry_list fine, that writes data.  GET_entry_list reads it. 

    Instead of breaking the existing implementation WRITE SOME CURRENT AND GOD FORBID ACCURATE DOCUMENTATION. get_relationships and search_by_module would be better named as SugarCRM’s Random data generator in most cases.

    Instead of breaking the current implementation, speed it up.   Adding some logical WHERE   and JOINs turns 30 second or more querries into 5 second ones.  

    90% of the documentation and examples for SOAP is for ancient versions.  There are 3 different SOAPs API’s, each with almost no documentation.   And if you have a Paid Version, how about documenting what should work against each.   

    • 

       Thanks for your feedback. Couldn’t agree more about the documentation being subpar, and it’s something we are working on improving. Would love for you to follow up with me directly at jmertic at sugarcrm
      dot com on what you’d like to see us do there.

      As for this issue, can you let us know the queries you are using which are problematic now in 6.4? We can work with you to help resolve the problems or provide workarounds.

      Thanks!

  3. 

    We use subqueries in our application for many purposes. This is a major breaking change that makes 6.4 completely incompatible with our app.

    We do use a subquery to retrieve the meetings a user has access to, similar to your example, but we retrieve all the fields from the meeting, since we need them anyway. Doing it with get_entry_list and a sub-query avoids us having to make 2 calls, one to get the list of ids, and another to get the objects by ID.

    We also use subqueries to find users by email address, since email addresses are stored in a separate table. What would be the “correct” way to do this?

    We also struggle with the problem of identifying “Sync To Outlook” contacts as pointed out by “Jonathan”.

    Is there a way to re-enable sub-query support so that our customers who have upgraded can avoid downgrading?

    • 

       Which tables are you doing subqueries against? We have a whitelist of allowed tables that may help solve the problem, plus we fixed a few bugs with the tool for 6.4.1 which should also help.

  4. 

    Hello everyone.
    If any of you hear, yesterday I released my repository on GitHub, the SOAP clientfor the new API (v4) of SugarCRM CE 6.4. The client has been created withApache CXF (RPC / Literal) instead of Axis 1.4.

    The repository may be accessed athttps://github.com/amusarra/SugarCRMCE64v4SOAPLibrary

    The article I wrote (still in Italian only) may be accessed athttp://musarra.wordpress.com/2012/03/01/sugarcrm-services-build-a-apache-cxf-client/

    Bye,
    Antonio.

  5. 
    Blake Robertson March 4, 2012 at 1:59 am

    The project Liz which is an asterisk plugin which looks up a phone number and changes the caller id to be the actual contact name from sugar no longer works.  

    $str = RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_home,’ ‘,”),'(0′,”),’+’,”),’-‘,”),'(‘,”),’)’,”),’+’,”),7) LIKE ‘2152497’ OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_work,’ ‘,”),'(0′,”),’+’,”),’-‘,”),'(‘,”),’)’,”),’+’,”),7) LIKE ‘2152497’ OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_mobile,’ ‘,”),'(0′,”),’+’,”),’-‘,”),'(‘,”),’)’,”),’+’,”),7) LIKE ‘2152497’ OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_other,’ ‘,”),'(0′,”),’+’,”),’-‘,”),'(‘,”),’)’,”),’+’,”),7) LIKE ‘2152497’ OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_fax,’ ‘,”),'(0′,”),’+’,”),’-‘,”),'(‘,”),’)’,”),’+’,”),7) LIKE ‘2152497’

    $result = $service->get_entry_list($sid,”Accounts”,$str,””,0,{a=>”name”},1,0)->result;

    If you are using this plugin, I found that v0.90 still works.  According to the release notes the only change was to support mssql databases.  It appears they replaced a SQL REGEX statements with the Right Replace stuff which is supposed to match only 7 numbers of the phone number.

  6. 
    Wayne Westmoreland June 15, 2012 at 10:28 am

    OK, here is my now broken get_entry_list call

    get_entry_list(sessionId, “Contacts”, “contacts.id In (Select contacts_bugs.contact_id From contacts_bugs Where contacts_bugs.deleted = 0 and contacts_bugs.bug_id = ‘a0ca2711-982d-b6a5-1fb2-4fdb354630c1’)”, “”, 0, [“account_id”], 250, 0)

    what is the new way to retrieve the Account Ids for the Contacts associated with a given Bug?

    FYI, I am using V1 of the SOAP API (I hope the answer doesn’t require V2).

  7. 

    We also just ran into some issues with that… After switching our API calls from get_entry_list to get_relationships, we’re missing a “sort_by” parameter. We’re trying to load an account’s n most recent cases. What’s your suggestion on getting that implemented?

    Thanks

  8. 
    Wayne Westmoreland July 2, 2012 at 4:16 pm

    Without using a subquery I can find no way to find bug records which have had contacts added or removed.  I used to be able to use this expression in my where clause: “bugs.id in (select contacts_bugs.bug_id from contacts_bugs where contacts_bugs.date_modified > $some_date)

    Using the date_modified field of the Bug record itself isn’t sufficient because it isn’t updated when a Contact is removed from the Bug and it also isn’t updated when the Bug is added to a Contact (as opposed to the Contact being added to a Bug).

    Is there any way now to reference the date_modified field of the contacts_bugs table?

  9. 

    Hi,
    How can i find one contact filtered by custom fields AND where the contact’s account is not in a filtered account list ? In one soap request because of performance….

    eg: current get_entry_list query on Contacts module
    field1_c=’some value’ and field2_c=’some other value’ and contacts.id not in (SELECT accounts_contacts.contact_id FROM accounts_contacts,accounts_cstm WHERE accounts_contacts.account_id=accounts_cstm.id_c AND accounts_cstm.field3_c=’value’ AND accounts_contacts.deleted=0)

    Thx

  10. 

    Nice Tutorial……………………………!

Trackbacks and Pingbacks:

  1. Access Denied when trying to retrieve meetings a user is participating in - November 11, 2013

    […] […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s