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":""}}]

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:

module - Fill in your one of the module's names here.
module_id - The ID of the record that exists in the Module you specified in the Module field.
link_name - This is the name of the link that exists between the 2 modules you want to relate.
remote_id - The ID of the record that you want related to the Module you previously specified.
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@@""}]}]")

Sugar Specific

Warnings

SmartLookups and Origin Filters against the User Module fail.

SQL Statements to Purge Data From Sugar

delete from accounts;
delete from accounts_audit;
delete from accounts_contacts;
delete from accounts_cstm;
delete from accounts_opportunities;

delete from activities;
delete from activities_users;

delete from bugs;
delete from bugs_cstm;

delete from calls;
delete from calls_contacts;
delete from calls_users;

delete from cases;
delete from cases_audit;

delete from contacts;
delete from contacts_audit;
delete from contacts_cstm;
delete from contacts_users;

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

delete from job_queue;

delete from meetings;
delete from meetings_contacts;
delete from meetings_leads;
delete from meetings_users;

delete from notes;

delete from opportunities;
delete from opportunities_contacts;
delete from opportunities_cstm;
delete from opportunities_audit;

delete from outbound_email;

delete from products;

delete from tasks;

SugarCRM REST

Contents