From Starfish ETL
Jump to navigation Jump to search

SugarCRM SOAP Origin

Note that the tab in Starfish is labelled simply "SugarCRM".

Sample Origin Filters



In Sugar, Attachments are stored as Notes. To attach a file to a Note, simply run and Insert, Update or Update/Insert Stage against the Notes module. There will be a field call Attachment. Map it as a function and select the Format Conversion Tab in the Function Field window. Fill in your Conversion Source where the file can be found. EX: C:\Temp\SugarCRM\SugarCRM\cache\upload\@@ORG:id@@. Then select the File -to- Base64 Conversion Operation.

Field Types

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
End Function


Smart Lookup

When filtering in a Smart Lookup, the make sure to put single quotes around the query value:

ScriptedVariable = SmartLookup("Accounts", "id", "accountid_c='@@ORG:ACCOUNTID@@'")


Use this function to get the records related to another record. For example, the Contacts related to a phone call. In the example below, I loop through the returned records, split them into an array, convert them to foreign IDs and finally put them into a comma separated string.

'Get related contacts from Sugar
strContactIDs = SugarGetRelationships("Calls", "@@ORG:id@@", "Contacts")
LogMessage "ContactIDs: " & strContactIDs
'Put list into Array
arrContactIDs = split(strContactIDs, ",")
numContactUpperBound = UBound(arrContactIDs)
'Convert SugarIDs to foreignIDs
For i = 0 To numContactUpperBound
	arrContactIDs(i) = XrefRead("contacts_sugar_to_other", arrContactIDs(i))
'Create a comma seperated string of Depos IDs
strContactIDs = Join(arrContactIDs, ",")

Sugar Specific


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;