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