SugarCRM REST Origin

Use JSONLint to validate JSON: https://jsonlint.com/.

Sample Origin Filters

See the GET /<module> filterList in the SugarCRM REST Help: https://SERVER/rest/v10/help/

Note the [{...}] surrounding the filter. This is required.

[{"id":"84b1c05c-1043-05c8-ced8-53a2ed18427b"}]

A more complex filter using $not_null and $not_equals:

[{"$and":[{"import_source_id_c":{"$not_null":""}},{"import_source_id_c":{"$not_equals":""}}]}]

A filter using several ands and ors:

[{"$and":[{"$or":[{"first_name":""},{"first_name":{"$is_null":""}}]},{"$or":[{"last_name":""},{"last_name":{"$is_null":""}}]},{"$or":[{"email1":""},{"email1":{"$is_null":""}}]}]}]

Note that the $not_null operation still requires a blank string.

Checking to see if a field is blank using $is_null:

[{"status_c":{"$is_null":""}}]

Grab records modified after a certain date. This is useful for creating jobs that only look at records modified since the last run date/time of Creatio Connect.

[{"date_modified":{"$gte":"2018-05-04T15:00:12"}}]

Note that this date MUST be formatted correctly, just like the DateTime field found below: res = FormatDate(GetSetting("AccountDownloadLastDT"), "yyyy-MM-ddTHH:mm:ss")

When filtering on a checkbox, you need to use 1 or 0. This example works on the Teams module.

[{"private":"1"}]

The REST API does have an IN clause:

[{"id":{"$in":["7dcc7462-f8bd-0429-888f-5112aa98a468","2f10f099-550e-2202-4126-511962a3ee86","2ef273c6-056d-1f4a-96ea-51197cedfce8"]}}]

The REST API also has a CONTAINS clause:

[{"fleet_size_c":{"$contains":"JJ"}}]

Filtering on the email 'field' only queries the primary email address. To filter for an email address in all of a contact's email addresses, use email_addresses.email_address as the field name.

[{"email_addresses.email_address":"email@test.com"}]

Using a Custom SugarCRM REST API Endpoint

Version 3.9.x of Creatio Connect can talk to a custom SugarCRM REST API EndPoint in the "Origin" tab of a map. (You can update your version of Creatio Connect through the "Check for Updates" link under the main menu of the Creatio Connect Admin program that you see when you click the Creatio Connect icon in upper-left of screen... you may also be prompted to update the "Updater" program in Creatio Connect).

The Sugar CRM Instance URL on the "Origin" tab would still remain with the "/rest/v10" syntax:

https://your_sugarcrm_domain_name_here/rest/v10

 Be explicit in the "Fields:" area, typing in the names of fields to match those returned by your custom endpoint. For example:

id,name,kbdocument_id

Do separate field names with a comma, but omit any spaces!

In the "Module:" area, type in the location where your custom REST API endpoint has been deployed. For Example:

System/SelectSQL

with "System" being the module folder within the custom folder of SugarCRM and "SelectSQL" being the name of the custom endpoint PHP file (our having deployed it like this pattern):

 
/custom/modules/name_of_module_where_endpoint_is_deployed/clients/base/api/endpointName.php

In the "Filter:" area, type in the the value(s) that your custom endpoint requires. For example:

select id,name,kbdocument_id from kbcontents where name like 'How%' order by name DESC;

For our example, the custom endpoint takes in an entire SQL SELECT query and returns a JSON object containing several items ("next offset" field, an array called "args", and the array of records, or rows, returned by the SQL query, with columns matching the fields defined in the "Fields:" area):

{
  "next_offset": -1,
  "args": {
    "__sugar_url": "v10/System/SelectSQL",
    "filter": "select id,name,kbdocument_id from kbcontents where name like 'How%' order by name  DESC;"
  },
  "records": [
    {
      "id": "6126e52f-b3f0-b375-e442-570754d5ed9c",
      "name": "How to print",
      "kbdocument_id": "614f444d-52a0-75b4-b959-5707545d1400"
    },
    {
      "id": "bd2fb1d9-23a1-4be7-3ee5-570754a89fbe",
      "name": "How to change the language",
      "kbdocument_id": "bd50a571-6d00-7207-fa30-57075416fac7"
    }
  ]
}

In the screen-shot shown above, you can see the two returned results shown in the "Data Preview" table area after having clicked the "refresh/run" (circular arrow) icon.

SugarCRM REST Module Endpoint Sorting

You can sort results from regular Module REST API endpoints by placing the "order_by=' phrase in the "Fields:" box such as "order_by=name:ASC". There should be a space between the last field name and the "order_by". Please note the underscore character between the words "order" and "by", as well as the colon character after the field name and before the "ASC" or "DESC" sort directive at the end.


Descending sequence:


If you want to also use that box to restrict fields you can do this:

 id,name order_by=name:ASC

Retrieve Deleted Records

You can use the "SugarCRM REST" Connector to retrieve deleted record by appending ";true" to the end of the "Sugar CRM Instance URL:" field's value and Filter for deleted records, [{"deleted":"1"}]:

You can also filter for recently deleted records by filtering on the date_modified. See example in http://wiki.starfishetl.com/index.php/SugarCRM_REST#Sample_Origin_Filters.

Mapping

Attachments

Insert Note Attachment

In Sugar, Attachments are stored as Notes. To attach a file to a Note, simply run an Insert, Update or Update/Insert Stage against the Notes module. There will be a field call "attachment". Map it as a function and select the Format Conversion Tab in the Function Field window. Fill in your Conversion Source where the file can be found. EX: C:\Temp\CRM\upload\@@ORG:id@@. Then select the "File -to- Binary" Conversion Operation. You will also need to fill in the filename in the filename field. You can also use a C# script to work with files. See Download File scripting examples.

Download Note Attachment

To download a Note's Attached file via the REST API, you must pass a GET request to: baseUrl + "/Notes/" + noteId + "/file/filename". The response is a binary file. Luckily, Creatio Connect does all of the work for you. When Creatio Connect reads a Note record, Creatio Connect checks to see if the Note has a populated filename. If it does, the file is presented in binary format in the 'attachment' column.

Documents

Insert Document Attachment

Creatio Connect cannot currently do this. If you need this capability, please email support@creatioconnect.com.

Download Document Attachment

To download a Document's attached file via the REST API, you must pass a GET request to: baseUrl + "/Documents/" + documentId + "/file/filename". The response is a binary file. Luckily, Creatio Connect does all of the work for you. When Creatio Connect reads a Document record, Creatio Connect checks to see if the Document has a populated filename. If it does, the file is presented in binary format in the 'attachment' column.

Emails (As in records in the Emails module.)

Email To/From/CC

The "to", "from", and "cc" fields are for the Emails module are rather unique and require you to pass in JSON.

Here are some samples of what is allowed:

{"create": [{"email_address":"adsfdasfds@techadv.com","email_address_id":"4dd0f6e8-6832-11e8-942e-069081b836be"}]}

{"create": [{"email_address_id":"4dd0f6e8-6832-11e8-942e-069081b836be"}]}

{"create": [{"parent_type":"Contacts","parent_id":"b5935fc4-e11c-0299-6ca0-50e7ae7c8be5"}]}

//This is the full result for multiple email addresses found by monitoring Sugar REST Calls.  This could be pared down to remove some of the items as was done for the above examples.
{"create": [{ "_link": "to", "email_address": "zonk@techadv.com", "deleted": false, "email_address_id": "8ee65e0e-1e65-11e9-ac42-02ae981fbade", "invalid_email": false, "opt_out": false }, { "_link": "to", "email_address": "zink@techadv.com", "deleted": false, "email_address_id": "92d9d48c-1e65-11e9-9a9d-02ae981fbade", "invalid_email": false, "opt_out": false }, { "_link": "to", "email_address": "zunk@techadv.com", "deleted": false, "email_address_id": "955421c2-1e65-11e9-aacb-02ae981fbade", "invalid_email": false, "opt_out": false }]}

These were found by monitoring Sugar via Chome Dev Tools and via trial and error.

This script will look up an email address via the SmartLookup function and return the proper JSON. Note that often times an Email can be sent to multiple people. This code does NOT account for that, though it could be modified to do so.

Function ScriptedField
 dim emailaddr
 dim emailid
 dim res
 emailaddr = "@@ORG:ToRecipients_EmailAddress@@"
 emailid = SmartLookup("EmailAddresses","id", emailaddr)
 res = "{""create"": [{""email_address_id"": """&emailid&"""}]}"
 ScriptedField=res
End Function

This script will look up a Contact and return the Contact's ID in the proper JSON format.

Function ScriptedField
 dim id
 dim res
 id = XrefRead("a2s-contacts", "@@ORG:CONTACTID@@")
 res = "{""create"": [{""parent_type"": ""Contacts"", ""parent_id"": """&id&"""}]}"
 ScriptedField=res
End Function

This script will look up a User and return the User's ID in the proper JSON format.

Function ScriptedField
 dim res
 dim userid
 userid = XrefRead("Users", "@@ORG:MANAGEUSERID@@")
 If userid = "" Then
  LogMessage "No Sugar UserID found for Act UserID: @@ORG:MANAGEUSERID@@"
  GoToNextRow
 Else
  res = "{""create"": [{""parent_type"": ""Users"", ""parent_id"": """&userid&"""}]}"
 End If
 ScriptedField=res
End Function

This C# script looks for matching records in Sugar and if it doesn't find a record, inserts the email address.

object ScriptedField()
{
 string res = "";
 
 string email = Starfish.OriginData["FROMEMAILADDRESS"].ToString();
 
 string uid = SafeToString(Starfish.SmartLookup("Users", "id", "[{\"email_addresses.email_address\":\"" + email + "\"}]"));
 if (!String.IsNullOrEmpty(uid))
 {
  res = "{\"create\": [{\"parent_type\": \"Users\", \"parent_id\": \""+uid+"\"}]}";
 }
 else
 {
  string cid = Starfish.GetStageValue(0, "#ID");
  if (String.IsNullOrEmpty(cid)) {
   cid = SafeToString(Starfish.SmartLookup("Contacts", "id", "[{\"email_addresses.email_address\":\"" + email + "\"}]"));
  }
  
  if (!String.IsNullOrEmpty(cid))
  {
   res = "{\"create\": [{\"parent_type\": \"Contacts\", \"parent_id\": \""+cid+"\"}]}";
  }
  else
  {
   string emailid = SafeToString(Starfish.SmartLookup("EmailAddresses","id",email));
   res = "{\"create\": [{\"email_address_id\": \""+emailid+"\"}]}";
  }
 }
 return res;
}

Email Attachments

As of Sugar v8.0, you can NOT Update the attachments field.' Email Attachments must be inserted into the Email Module's "attachments" json type field. The expected format is:

{"create":[{"_link":"attachments","filename_guid":"715d9962-89f3-11e8-8fe2-02fd0a47dd48","name":"xyz.pdf","filename":"xyz.pdf"}]}

If you want to insert multiple attachments to an Email, you must pass them in all at once. This sample code may help:

object ScriptedField()
{
 int i = 0;
 var res = "{\"create\":[";
 foreach (string s in arrTempFile)
 {
  res = res + "{\"_link\":\"attachments\",\"filename_guid\":\""+ s +"\",\"name\":\""+ arrFileName[i] +"\",\"filename\":\""+ arrFileName[i] +"\"},";
  i++;
 }
 res = res.Substring(0, res.Length-1);
 res = res + "]}";
 return res;
}

Field Types

Assigned User

To assign a user to a record, you can normally pass in the user's ID to the assigned_user_id field. EX:

c3c15eb8-86fc-11e8-96ba-02b1b963c90d

This only works for standard users. If you want to assign a record to a Group User, you must pass in the user's ID to the assigned_user_id field AND populate the assigned_user_name field with some JSON:

{"id":"c3c15eb8-86fc-11e8-96ba-02b1b963c90d","value":"GroupUser","full_name":"GroupUser","date_modified":"2018-07-13T19:28:00-05:00","_acl":{"fields":{}},"_erased_fields":[],"_module":"Users"}

Assigning Teams

On the team_name field you want to supply a bit of JSON with examples below. It should be noted with this method whatever you supply will completely replace the team values, as opposed append to them like in previous versions. You can use a hardcoded field value or return the value from a scripted field but the result needs to look like this:

One Team (will automatically become primary):

[{ "id": "17b6bc3e-38a3-db70-6490-50ed7c3a4d87" }]

Multiple Teams: