SugarCRM REST

SugarCRM REST Origin

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:

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

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 Starfish.

[{"date_modified":{"$gte":"2015-07-15T05:03:00+00:00"}}]

Mapping

Field Types

Checkbox

When mapping to a checkbox, use a script to set the value to "true" or "false", making sure to use all lowercase.

Date Fields

Function ScriptedField
  ScriptedField=FormatDate("@@ORG:renewal_date_c@@", "yyyy-MM-dd")
End Function

Datetime Fields

Function ScriptedField
  ScriptedField=FormatDate("@@ORG:date_entered@@", "yyyy-MM-ddTHH:mm:ss")
End Function

Multi-select Dropdowns

Data in Multiselect Dropdowns is stored as a comma separated string with carats around each value: ^Atari^,^Automobiles^,^Baseball^,^Fine_Art^,^Running_Walking^

Sample Function:

Function ScriptedField
  dim cat 
  cat = "@@ORG:Categories@@"
  dim res
  If InStr(cat,"Board Invitee") Then
    res = res & "^Board_Invitee^,"
  End If
  If InStr(cat,"Priority Contact") Then
    res = res & "^Priority_Contact^,"
  End If
  If InStr(cat,"Dividend Contact") Then
    res = res & "^Dividend_Contact^,"
  End If
  If Right(res,1) = "," Then
    res = Left(res,Len(res)-1)
  End If
  ScriptedField=res
End Function

Relate Fields

When you create a Relate field in Sugar, not a field created by adding a Relationship, two fields are created for the API: fieldname_c relate type and an id (36) type field which is the name of the related to module followed by _id_c. If you add multiple relate fields for a single module, then you will get multiple numbered id (36) fields.

For example, if you add a Relate field to the User module called "usr_c", you will see 2 fields in Starfish:

• user_id_c of type "id (36)"
• usr_c of type "relate"

You MUST set the id (36) field in the Starfish map. Setting the relate type field will do nothing.

Insert Relationships

In the Manage Stages dialog box, you can create a Stage for a table called "Relationship". This Stage should always be an Insert Stage Type. This stage will then contain 5 fields:

1. module - Fill in your one of the module's names here.
2. module_id - The ID of the record that exists in the Module you specified in the Module field.
3. link_name - This is the name of the link that exists between the 2 modules you want to relate.
4. remote_id - The ID of the record that you want related to the Module you previously specified.
5. fields - This field can be left blank.

Note that the Module and Link names are not always clear. You may need to look at the Vardefs of your instance to find the proper names.

VBScript

Smart Lookup

When filtering in a smart lookup, the filter must use double-double quotes:

strSugarID = SmartLookup("Accounts","id","[{""import_source_id"":""84b1c05c-1043-05c8-ced8-53a2ed18427b""}]")

A more complex filter:

res = SmartLookup("Users", "id", "[{""$and"":[{""last_name"":""@@ORG:IENSalesRep_Last@@""},{""first_name"":""@@ORG:IENSalesRep_First@@""}]}]")

Warnings/Bugs

I'm unsure if these are bugs in the Sugar REST API or the Starfish Connector.

• SmartLookups and Origin Filters against the User Module fail.
• Setting the Team value via the team_id field. The Team is set to the importing Sugar User's Default Team set in the User's Sugar Profile no matter what is passed into the team_id field.
  • Setting the team works in the SOAP Connector. If you must set the team, use the SOAP connector.
• Cannot authenticate with the REST API if the username has a period, ".", in it.
  • Workaround: Use a username without a period.

Sugar Specific Code/Information

SQL Statements to Purge Test Data From Sugar

Don't forget to add statements for any custom modules you may have or OOTB modules you are using but are not listed below.

truncate accounts;
truncate accounts_audit;
truncate accounts_contacts;
truncate accounts_cstm;
truncate accounts_opportunities;

truncate activities;
truncate activities_users;

truncate bugs;
truncate bugs_cstm;

truncate calls;
truncate calls_contacts;
truncate calls_users;

truncate cases;
truncate cases_audit;

truncate contacts;
truncate contacts_audit;
truncate contacts_cstm;
truncate contacts_users;

truncate emails;
truncate emails_text;
truncate emails_beans;
truncate email_addr_bean_rel where bean_module <> 'Users';
truncate email_addresses where id in (select email_address_id from email_addr_bean_rel where bean_module <> 'Users');

truncate job_queue;

truncate meetings;
truncate meetings_contacts;
truncate meetings_leads;
truncate meetings_users;

truncate notes;

truncate opportunities;
truncate opportunities_contacts;
truncate opportunities_cstm;
truncate opportunities_audit;

truncate outbound_email;

truncate products;

truncate tasks;