SugarCRM SOAP - Revision history

Jkuehlthau: /* SugarGetRelationship */

2015-10-13T18:50:31Z

SugarGetRelationship

← Older revision		Revision as of 18:50, 13 October 2015
Line 46:		Line 46:
	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.		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.
	<source lang="vb">		<source lang="vb">
	~~'Retrieve Related Contacts~~
	~~intNumToBeDeleted = 0~~
	~~intNumDeleted = 0~~
	'Get related contacts from Sugar		'Get related contacts from Sugar
	strContactIDs = SugarGetRelationships("Calls", "@@ORG:id@@", "Contacts")		strContactIDs = SugarGetRelationships("Calls", "@@ORG:id@@", "Contacts")

Jkuehlthau: /* VBScript */

2015-10-13T18:49:56Z

VBScript

@@ Line 42: / Line 42: @@
 ScriptedVariable = SmartLookup("Accounts", "id", "accountid_c='@@ORG:ACCOUNTID@@'")
 </syntaxhighlight>
 =Sugar Specific=

Jkuehlthau: /* Attachments */

2015-08-07T13:51:59Z

Attachments

← Older revision		Revision as of 13:51, 7 August 2015
Line 6:		Line 6:
	=Mapping=		=Mapping=
	==Attachments==		==Attachments==
	In Sugar, Attachments are stored as Notes. To attach a file to a Note, simply run and Insert, Update or Insert~~/Update~~ 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.		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==		==Field Types==

Jkuehlthau: /* Mapping */

2015-08-07T13:48:35Z

Mapping

← Older revision		Revision as of 13:48, 7 August 2015
Line 5:		Line 5:

	=Mapping=		=Mapping=
			==Attachments==
			In Sugar, Attachments are stored as Notes. To attach a file to a Note, simply run and Insert, Update or Insert/Update 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==		==Field Types==
	===Multi-select Dropdowns===		===Multi-select Dropdowns===

Jkuehlthau: /* Smart Lookup */

2015-04-08T19:11:14Z

Smart Lookup

← Older revision		Revision as of 19:11, 8 April 2015
Line 34:		Line 34:
	==VBScript==		==VBScript==
	===Smart Lookup===		===Smart Lookup===
	When filtering in a ~~smart lookup~~, the ~~filter must use double-double~~ quotes:		When filtering in a Smart Lookup, the make sure to put single quotes around the query value:

	<syntaxhighlight lang="vb">		<syntaxhighlight lang="vb">
	~~strSugarID~~ = SmartLookup("Accounts","id","~~[{""import_source_id"":""84b1c05c-1043-05c8-ced8-53a2ed18427b""}]")~~		ScriptedVariable = SmartLookup("Accounts", "id", "accountid_c='@@ORG:ACCOUNTID@@'")
	~~</syntaxhighlight>~~

	~~A more complex filter:~~
	~~<syntaxhighlight lang~~=~~"vb">~~
	~~res = SmartLookup("Users", "id", "[{""$and"":[{""last_name"":""~~@@ORG:~~IENSalesRep_Last~~@@~~""},{""first_name"":""@@ORG:IENSalesRep_First@@""}]}]~~")
	</syntaxhighlight>		</syntaxhighlight>

Jkuehlthau: /* Field Types */

2015-04-08T19:10:19Z

Field Types

@@ Line 6: / Line 6: @@
 =Mapping=
 ==Field Types==
 ===Multi-select Dropdowns===
 Data in Multiselect Dropdowns is stored as a comma separated string with carats around each value:
@@ Line 45: / Line 31: @@
 End Function
 </syntaxhighlight>
 ==VBScript==

Jkuehlthau: /* SugarCRM REST Origin */

2015-04-08T19:09:42Z

SugarCRM REST Origin

@@ Line 1: / Line 1: @@
-=SugarCRM REST Origin=
+=SugarCRM SOAP Origin=
 ==Sample Origin Filters==
 =Mapping=

Jkuehlthau: Created page with "=SugarCRM REST Origin= ==Sample Origin Filters== See the GET / filterList in the SugarCRM REST Help: https://SERVER/rest/v10/help/ Note the [{...}] surrounding the f..."

2015-04-08T19:05:32Z

Created page with "=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 f..."

New page

=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.

<syntaxhighlight lang="c">
[{"id":"84b1c05c-1043-05c8-ced8-53a2ed18427b"}]
</syntaxhighlight>

A more complex filter using $not_null and $not_equals:

<syntaxhighlight lang="c">
[{"$or":[{"import_source_id_c":{"$not_null":""}},{"import_source_id_c":{"$not_equals":""}}]}]
</syntaxhighlight>

Note that the $not_null operation still requires a blank string.

Checking to see if a field is blank using $is_null:
<syntaxhighlight lang="c">
[{"status_c":{"$is_null":""}}]
</syntaxhighlight>

=Mapping=
==Field Types==
===Date Fields===
<syntaxhighlight lang="vb">
Function ScriptedField
ScriptedField=FormatDate("@@ORG:renewal_date_c@@", "yyyy-MM-dd")
End Function
</syntaxhighlight>

===Datetime Fields===
<syntaxhighlight lang="vb">
Function ScriptedField
ScriptedField=FormatDate("@@ORG:date_entered@@", "yyyy-MM-ddTHH:mm:ss")
End Function
</syntaxhighlight>

===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:
<syntaxhighlight lang="vb">
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
</syntaxhighlight>

===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:

<syntaxhighlight lang="vb">
strSugarID = SmartLookup("Accounts","id","[{""import_source_id"":""84b1c05c-1043-05c8-ced8-53a2ed18427b""}]")
</syntaxhighlight>

A more complex filter:
<syntaxhighlight lang="vb">
res = SmartLookup("Users", "id", "[{""$and"":[{""last_name"":""@@ORG:IENSalesRep_Last@@""},{""first_name"":""@@ORG:IENSalesRep_First@@""}]}]")
</syntaxhighlight>

=Sugar Specific=
==Warnings==
SmartLookups and Origin Filters against the User Module fail.

==SQL Statements to Purge Data From Sugar==
<syntaxhighlight lang="mysql">
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;
</syntaxhighlight>