Note that the tab in StarfishETL is labelled simply "SugarCRM".
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.
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
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))
Next
'Create a comma seperated string of Depos IDs
strContactIDs = Join(arrContactIDs, ",")
SmartLookups and Origin Filters against the User Module fail.
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;