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:

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

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

Mapping

Field Types

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

Checkbox

When mapping to a checkbox, does it require returning True or some other value? 1?

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

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;