SugarCRM

Managed by | Updated 6 years ago.

Introduction

Recommended approach is to use two collections to facilitate a minimal level of discoverability:

• sugar-crm-accounts
• sugar-crm-contacts

Pre-Requisites

CRM administrators are unlikely to allow direct database access to Sugar - thankfully, there's an API available.  You'll need an Oauth key generated by a Sugar Administrator and

1. Oauth2 Key (generated by Sugar Administrator via Sugar AdminUI)
2. User account (created by Sugar Administrator via Sugar AdminUI)

API Usage

Sample response

{
  "next_offset": 20,
  "records": [
    {
      "my_favorite": false,
      "following": false,
      "id": "f40d8076-db13-0dde-b4b8-56cfd3cbce95",
      "name": "The Crown Estate",
      "date_entered": "2016-02-26T04:23:00+00:00",
      "date_modified": "2016-04-25T14:54:50+01:00",
      "modified_user_id": "3a7dcb3d-22fa-2ed1-382b-55c01d928bd1",
      "modified_by_name": "Caroline Maillols",
      "created_by": "3a7dcb3d-22fa-2ed1-382b-55c01d928bd1",
      "created_by_name": "Caroline Maillols",
      "doc_owner": "",
      "user_favorites": "",
      "description": "",
      "deleted": false,
      "assigned_user_id": "83166a1c-9ddf-38f2-8136-56ce8840cfc4",
      "assigned_user_name": "Ben Tilley",
      "team_count": "",
      "team_name": [
        {
          "id": "1",
          "name": "Global",
          "name_2": "",
          "primary": true
        }
      ],
      "email": [],
      "email1": "",
      "email2": "",
      "invalid_email": "",
      "email_opt_out": "",
      "email_addresses_non_primary": "",
      "facebook": "",
      "twitter": "",
      "googleplus": "",
      "account_type": "",
      "industry": "",
      "annual_revenue": "",
      "phone_fax": "",
      "billing_address_street": "16 New Burlington Place",
      "billing_address_street_2": "",
      "billing_address_street_3": "",
      "billing_address_street_4": "",
      "billing_address_city": "London",
      "billing_address_state": "NA",
      "billing_address_postalcode": "W1S 2HX",
      "billing_address_country": "",
      "rating": "",
      "phone_office": "",
      "phone_alternate": "",
      "website": "www.crownestate.co.uk",
      "ownership": "",
      "employees": "",
      "ticker_symbol": "",
      "shipping_address_street": "16 New Burlington Place",
      "shipping_address_street_2": "",
      "shipping_address_street_3": "",
      "shipping_address_street_4": "",
      "shipping_address_city": "London",
      "shipping_address_state": "NA",
      "shipping_address_postalcode": "W1S 2HX",
      "shipping_address_country": "",
      "parent_id": "",
      "sic_code": "",
      "duns_num": "",
      "parent_name": "",
      "campaign_id": "",
      "campaign_name": "",
      "hosting_c": "",
      "cms_used_c": "",
      "capability_website_search_c": false,
      "record_type_c": "Direct Prospect",
      "account_status_c": "Active",
      "hosting_since_c": "",
      "capability_accessibility_c": false,
      "region_c": "UK",
      "intranet_account_link_c": "https://funnelback-intranet.squiz.net/?client_id=3377",
      "cms_since_c": "",
      "import_source_c": "Intranet 2016 // Was Prospect",
      "capability_enterprise_search_c": false,
      "egnyte_link_c": "",
      "intranet_account_id_c": "3377",
      "sync_to_intranet_c": "",
      "accountdirector_c": "None",
      "search_used_c": "",
      "search_since_c": "",
      "mrkto2_industry_c": "Education University",
      "_acl": {
        "fields": {}
      },
      "_module": "Accounts"
    },
   ...
  ]
}

Configuration

PADRE XML Mapping Version: 2
document,/documents/records/record
docurl,/documents/records/record/id
t,1,,//name
id,0,,//id
website,0,,//website
region,0,,//region_c
country,1,,//billing_address_country
postcode,0,,//billing_address_postalcode
street,0,,//billing_address_street
state,1,,//billing_address_state
phone,0,,//phone_office
egnyte,0,,//egnyte_link_c
status,1,,//account_status_c
type,1,,//record_type_c
intranet,0,,//intranet_link_c
twitter,0,,//twitter
d,0,,//date_modified
a,0,,//assigned_user_name
industry,1,,//industry

indexing.collapse_fields=[$],[a],[t]
query_processor_options=-collapsing=on -rmc_sensitive=true -stem=2 -SM=meta -SF=[region,name,id,description,twitter,website,country,state,a,d]
 
# Custom configuration values for use by custom_gather.groovy
sugar_fields=id,name,assigned_user_name,date_modified,website,industry,description,twitter,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,record_type_c,account_status_c,region_c,markto2_industry_c,intranet_account_link_c,egnyte_link_c
sugar_server_api_path=https://crm.funnelback.com/rest/v10/
sugar_module=Accounts

Common facets worth providing:

1. assigned_user_name
2. industry
3. billing_address_country > billing_address_state
4. account_type

Custom fields may also be used by a client - in the example files above, they've been suffixed with '_c'.

Caveats

1. SugarAPI only outputs JSON.  You'll need to use a JSON2XML conversion as a custom_gather.groovy.
2. SugarCRM URLs use hash characters, so will not be able to be indexed correctly by PADRE.  Creating non-hashed versions of URLs at index time, then outputting hashed versions at template time via hook scripts seems to be a reasonable workaround.
3. Calling more than 100 records at a time (with all available fields) seems to trigger 500 status code messages on the API.  Be polite, be cautious.