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

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, use a script to set the value to "true" or "false", making sure to use all lowercase. (Needs verification.)

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 SOAP

Contents