SugarCRM REST - Revision history

Jkuehlthau: /* Smart Query using Custom Endpoint */

2025-09-23T19:07:40Z

Smart Query using Custom Endpoint

← Older revision		Revision as of 19:07, 23 September 2025
Line 728:		Line 728:
	{		{
	string res = "";		string res = "";
	//This code will search for all Sugar ~~Contacts~~ that are associated with a specific email address.		//This code will search for all Sugar CONTACTS that are associated with a specific email address.
	string sql = "select bean_id from email_addresses ea inner join email_addr_bean_rel eabr on ea.id = eabr.email_address_id and eabr.deleted <> 1 and eabr.bean_module = '~~Leads~~' where ea.deleted <> 1 and ea.email_address = '"+ ConvertObj2String(Starfish.OriginData["EMAILADDRESS"]) +"';";		string sql = "select bean_id from email_addresses ea inner join email_addr_bean_rel eabr on ea.id = eabr.email_address_id and eabr.deleted <> 1 and eabr.bean_module = 'Contacts' where ea.deleted <> 1 and ea.email_address = '"+ ConvertObj2String(Starfish.OriginData["EMAILADDRESS"]) +"';";
	Starfish.LogMessage(sql);		Starfish.LogMessage(sql);
	System.Data.DataTable dtContacts = Starfish.SmartQueryDT("System/SelectSQL",sql,"bean_id");		System.Data.DataTable dtContacts = Starfish.SmartQueryDT("System/SelectSQL",sql,"bean_id");

Jkuehlthau: /* Smart Query using Custom Endpoint */

2025-09-23T19:06:58Z

Smart Query using Custom Endpoint

← Older revision		Revision as of 19:06, 23 September 2025
Line 729:		Line 729:
	string res = "";		string res = "";
	//This code will search for all Sugar Contacts that are associated with a specific email address.		//This code will search for all Sugar Contacts that are associated with a specific email address.
	~~System.Data.DataTable dtContacts~~ = ~~Starfish.SmartQueryDT("System/SelectSQL",~~"select bean_id from email_addresses ea inner join email_addr_bean_rel eabr on ea.id = eabr.email_address_id and eabr.deleted <> 1 and eabr.bean_module = '~~Contacts~~' where ea.deleted <> 1 and ea.email_address = '"+Starfish.OriginData["~~EMAIL1~~"]+"';","bean_id");		string sql = "select bean_id from email_addresses ea inner join email_addr_bean_rel eabr on ea.id = eabr.email_address_id and eabr.deleted <> 1 and eabr.bean_module = 'Leads' where ea.deleted <> 1 and ea.email_address = '"+ ConvertObj2String(Starfish.OriginData["EMAILADDRESS"]) +"';";
	//Starfish.LogMessage(dtContacts.~~Columns~~.Count);		Starfish.LogMessage(sql);
			System.Data.DataTable dtContacts = Starfish.SmartQueryDT("System/SelectSQL",sql,"bean_id");
			Starfish.LogMessage(ConvertObj2String(dtContacts.Rows.Count));
	//If we're assuming that there is only 1 record returned or if we only care about the first returned record, we can grab that value with DataTable.Rows[0][0]		//If we're assuming that there is only 1 record returned or if we only care about the first returned record, we can grab that value with DataTable.Rows[0][0]
	//Check to make sure there is at least one record in the DataTable so .Rows doesn't error.		//Check to make sure there is at least one record in the DataTable so .Rows doesn't error.
Line 737:		Line 739:
	//Starfish.LogMessage(dtContacts.Rows[0][0].ToString());		//Starfish.LogMessage(dtContacts.Rows[0][0].ToString());
	res = dtContacts.Rows[0][0].ToString();		res = dtContacts.Rows[0][0].ToString();
			Starfish.LogMessage(res);
	}		}

	//If we are expecting many records, we can loop through them.		//If we are expecting many records, we can loop through them.
			string strContacts = "";
	foreach (System.Data.DataRow row in dtContacts.Rows)		foreach (System.Data.DataRow row in dtContacts.Rows)
	{		{
			Starfish.LogMessage(row[0].ToString());
	//Create a comma separated list of ContactIDs.		//Create a comma separated list of ContactIDs.
	strContacts = strContacts + row[0].ToString() + ',';		strContacts = strContacts + row[0].ToString() + ',';
	}		}
			Starfish.LogMessage(strContacts);
	//Remove the excess comma at the end.		//Remove the excess comma at the end.
	strContacts = strContacts.Substring(0,strContacts.Length-1);		strContacts = strContacts.Substring(0,strContacts.Length-1);
	//Starfish.LogMessage(strContacts);		Starfish.LogMessage(strContacts);
	return res;		return res;
	}		}

Jkuehlthau: /* Insert Document Attachment */

2024-12-17T22:22:41Z

Insert Document Attachment

← Older revision		Revision as of 22:22, 17 December 2024
Line 371:		Line 371:
	You will need two stages for this.		You will need two stages for this.

	First, Insert into the "TempDocument" Table and map the attachment in Binary format to the attachment field.		First, Insert into the "TempDocument" Table and map the attachment in Binary format to the attachment field. See the Note Attachments section for information about data format conversions.

	Second, Insert or Upsert into the Documents table and pass the ID returned by Stage 0, "@@ORG:id@@", to the filename_guid field.		Second, Insert or Upsert into the Documents table and pass the ID returned by Stage 0, "@@ORG:id@@", to the filename_guid field.

Jkuehlthau: /* Insert Document Attachment */

2024-12-17T22:21:30Z

Insert Document Attachment

← Older revision		Revision as of 22:21, 17 December 2024
Line 369:		Line 369:
	==Documents==		==Documents==
	===Insert Document Attachment===		===Insert Document Attachment===
	~~Starfish cannot currently do~~ this. ~~If you need this capability~~, ~~please email support~~@~~starfishetl.com~~.		You will need two stages for this.

			First, Insert into the "TempDocument" Table and map the attachment in Binary format to the attachment field.

			Second, Insert or Upsert into the Documents table and pass the ID returned by Stage 0, "@@ORG:id@@", to the filename_guid field.

	===Download Document Attachment===		===Download Document Attachment===

Jkuehlthau: /* Using the Upsert PATCH (BULK) endpoint */

2024-09-28T11:05:05Z

Using the Upsert PATCH (BULK) endpoint

← Older revision		Revision as of 11:05, 28 September 2024
Line 143:		Line 143:
	##If multiple match fields are chosen, the job will not work correctly.		##If multiple match fields are chosen, the job will not work correctly.

	~~'''~~Known BULK Issues~~'''~~		==Known BULK Issues==
	*Note that when working with the Contacts module, this will NOT work with the email address field.		*Note that when working with the Contacts module, this will NOT work with the email address field.
	*Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.		*Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.

Jkuehlthau: /* Using the Upsert PATCH (BULK) endpoint */

2024-09-28T11:04:45Z

Using the Upsert PATCH (BULK) endpoint

← Older revision		Revision as of 11:04, 28 September 2024
Line 143:		Line 143:
	##If multiple match fields are chosen, the job will not work correctly.		##If multiple match fields are chosen, the job will not work correctly.

	''Known BULK Issues''		'''Known BULK Issues'''
	*Note that when working with the Contacts module, this will NOT work with the email address field.		*Note that when working with the Contacts module, this will NOT work with the email address field.
	*Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.		*Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.

Jkuehlthau: /* Using the Upsert PATCH (BULK) endpoint */

2024-09-28T11:04:38Z

Using the Upsert PATCH (BULK) endpoint

← Older revision		Revision as of 11:04, 28 September 2024
Line 143:		Line 143:
	##If multiple match fields are chosen, the job will not work correctly.		##If multiple match fields are chosen, the job will not work correctly.

	'Known BULK Issues'		''Known BULK Issues''
	*Note that when working with the Contacts module, this will NOT work with the email address field.		*Note that when working with the Contacts module, this will NOT work with the email address field.
	*Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.		*Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.

Jkuehlthau: /* Using the Upsert PATCH (BULK) endpoint */

2024-09-28T11:04:24Z

Using the Upsert PATCH (BULK) endpoint

← Older revision		Revision as of 11:04, 28 September 2024
Line 143:		Line 143:
	##If multiple match fields are chosen, the job will not work correctly.		##If multiple match fields are chosen, the job will not work correctly.

	Note that when working with the Contacts module, this will NOT work with the email address field.		'Known BULK Issues'
	Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.		*Note that when working with the Contacts module, this will NOT work with the email address field.
			*Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.

	=Mapping=		=Mapping=

Jkuehlthau: /* Using the Upsert PATCH endpoint */

2024-09-28T11:03:56Z

Using the Upsert PATCH endpoint

← Older revision		Revision as of 11:03, 28 September 2024
Line 133:		Line 133:
	You can also filter for recently deleted records by filtering on the date_modified. See example in http://wiki.starfishetl.com/index.php/SugarCRM_REST#Sample_Origin_Filters.		You can also filter for recently deleted records by filtering on the date_modified. See example in http://wiki.starfishetl.com/index.php/SugarCRM_REST#Sample_Origin_Filters.

	=Using the Upsert PATCH endpoint=		=Using the Upsert PATCH (BULK) endpoint=
	Using the newer upsert PATCH endpoint, https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_12.3/Integration/Web_Services/REST_API/Endpoints/Integratemodulesync_key_field_namesync_key_field_value_PATCH/, in StarfishETL is fairly simple:		Using the newer upsert PATCH endpoint, https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_12.3/Integration/Web_Services/REST_API/Endpoints/Integratemodulesync_key_field_namesync_key_field_value_PATCH/, in StarfishETL is fairly simple:
	#On your Destination, choose Batch mode.		#On your Destination, choose Batch mode.
Line 143:		Line 143:
	##If multiple match fields are chosen, the job will not work correctly.		##If multiple match fields are chosen, the job will not work correctly.

			Note that when working with the Contacts module, this will NOT work with the email address field.
	Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.		Note that this does not work with some modules such as Emails or the custom "Relationships" table that Starfish uses to create relationships between two records.

Jkuehlthau: /* Dropdowns */

2024-06-14T20:58:36Z

Dropdowns

← Older revision		Revision as of 20:58, 14 June 2024
Line 506:		Line 506:
	===Dropdowns===		===Dropdowns===
	This is the function I use to convert source data to Sugar Dropdown DB values.		This is the function I use to convert source data to Sugar Dropdown DB values.
	<syntaxhighlight lang="c">		<syntaxhighlight lang="visualbasic">
	Function ScriptedField		Function ScriptedField
	dim source		dim source