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^
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;