SugarCRM REST Origin

Use JSONLint to validate JSON: https://jsonlint.com/.

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.

[{"id":"84b1c05c-1043-05c8-ced8-53a2ed18427b"}]

A more complex filter using $not_null and $not_equals:

[{"$and":[{"import_source_id_c":{"$not_null":""}},{"import_source_id_c":{"$not_equals":""}}]}]

A filter using several ands and ors:

[{"$and":[{"$or":[{"first_name":""},{"first_name":{"$is_null":""}}]},{"$or":[{"last_name":""},{"last_name":{"$is_null":""}}]},{"$or":[{"email1":""},{"email1":{"$is_null":""}}]}]}]

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

Checking to see if a field is blank using $is_null:

[{"status_c":{"$is_null":""}}]

Grab records modified after a certain date. This is useful for creating jobs that only look at records modified since the last run date/time of Connect Creatio.

[{"date_modified":{"$gte":"2018-05-04T15:00:12"}}]

Note that this date MUST be formatted correctly, just like the DateTime field found below: res = FormatDate(GetSetting("AccountDownloadLastDT"), "yyyy-MM-ddTHH:mm:ss")

When filtering on a checkbox, you need to use 1 or 0. This example works on the Teams module.

[{"private":"1"}]

The REST API does have an IN clause:

[{"id":{"$in":["7dcc7462-f8bd-0429-888f-5112aa98a468","2f10f099-550e-2202-4126-511962a3ee86","2ef273c6-056d-1f4a-96ea-51197cedfce8"]}}]

The REST API also has a CONTAINS clause:

[{"fleet_size_c":{"$contains":"JJ"}}]

The REST API also has a NOT CONTAINS clause:

[{"fleet_size_c":{"$contains":"JJ"}}]

[{"email_addresses.email_address":"email@test.com"}]

Using a Custom SugarCRM REST API Endpoint

Version 3.9.x of StarfishETL can talk to a custom SugarCRM REST API EndPoint in the "Origin" tab of a map. (You can update your version of StarfishETL through the "Check for Updates" link under the main menu of the StafishETL Admin program that you see when you click the StarfishETL icon in upper-left of screen... you may also be prompted to update the "Updater" program in StarfishETL).

The Sugar CRM Instance URL on the "Origin" tab would still remain with the "/rest/v10" syntax:

https://your_sugarcrm_domain_name_here/rest/v10

 Be explicit in the "Fields:" area, typing in the names of fields to match those returned by your custom endpoint. For example:

id,name,kbdocument_id

Do separate field names with a comma, but omit any spaces!

In the "Module:" area, type in the location where your custom REST API endpoint has been deployed. For Example:

System/SelectSQL

with "System" being the module folder within the custom folder of SugarCRM and "SelectSQL" being the name of the custom endpoint PHP file (our having deployed it like this pattern):

 
/custom/modules/name_of_module_where_endpoint_is_deployed/clients/base/api/endpointName.php

In the "Filter:" area, type in the the value(s) that your custom endpoint requires. For example:

select id,name,kbdocument_id from kbcontents where name like 'How%' order by name DESC;

For our example, the custom endpoint takes in an entire SQL SELECT query and returns a JSON object containing several items ("next offset" field, an array called "args", and the array of records, or rows, returned by the SQL query, with columns matching the fields defined in the "Fields:" area):

{
  "next_offset": -1,
  "args": {
    "__sugar_url": "v10/System/SelectSQL",
    "filter": "select id,name,kbdocument_id from kbcontents where name like 'How%' order by name  DESC;"
  },
  "records": [
    {
      "id": "6126e52f-b3f0-b375-e442-570754d5ed9c",
      "name": "How to print",
      "kbdocument_id": "614f444d-52a0-75b4-b959-5707545d1400"
    },
    {
      "id": "bd2fb1d9-23a1-4be7-3ee5-570754a89fbe",
      "name": "How to change the language",
      "kbdocument_id": "bd50a571-6d00-7207-fa30-57075416fac7"
    }
  ]
}

In the screen-shot shown above, you can see the two returned results shown in the "Data Preview" table area after having clicked the "refresh/run" (circular arrow) icon.

SugarCRM REST Module Endpoint Sorting

You can sort results from regular Module REST API endpoints by placing the "order_by=' phrase in the "Fields:" box such as "order_by=name:ASC". There should be a space between the last field name and the "order_by". Please note the underscore character between the words "order" and "by", as well as the colon character after the field name and before the "ASC" or "DESC" sort directive at the end.


Descending sequence:


If you want to also use that box to restrict fields you can do this:

 id,name order_by=name:ASC

Retrieve Deleted Records

You can use the "SugarCRM REST" Connector to retrieve deleted record by appending ";true" to the end of the "Sugar CRM Instance URL:" field's value and Filter for deleted records, [{"deleted":"1"}]:

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.

Mapping

Attachments

Insert Note Attachment

In Sugar, Attachments are stored as Notes. To attach a file to a Note, simply run an 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\CRM\upload\@@ORG:id@@. Then select the "File -to- Binary" Conversion Operation. You will also need to fill in the filename in the filename field. You can also use a C# script to work with files. See Download File scripting examples.

Download Note Attachment

To download a Note's Attached file via the REST API, you must pass a GET request to: baseUrl + "/Notes/" + noteId + "/file/filename". The response is a binary file. Luckily, StarfishEtl does all of the work for you. When StarfishETL reads a Note record, StarfishETL checks to see if the Note has a populated filename. If it does, the file is presented in binary format in the 'attachment' column.

Documents

Insert Document Attachment

StarfishETL cannot currently do this. If you need this capability, please email support@starfishetl.com.

Download Document Attachment

To download a Document's attached file via the REST API, you must pass a GET request to: baseUrl + "/Documents/" + documentId + "/file/filename". The response is a binary file. Luckily, StarfishETL does all of the work for you. When StarfishETL reads a Document record, StarfishETL checks to see if the Document has a populated filename. If it does, the file is presented in binary format in the 'attachment' column.

Emails (As in records in the Emails module.)

Email To/From/CC

The "to", "from", and "cc" fields are for the Emails module are rather unique and require you to pass in JSON.

Here are some samples of what is allowed:

{"create": [{"email_address":"adsfdasfds@techadv.com","email_address_id":"4dd0f6e8-6832-11e8-942e-069081b836be"}]}

{"create": [{"email_address_id":"4dd0f6e8-6832-11e8-942e-069081b836be"}]}

{"create": [{"parent_type":"Contacts","parent_id":"b5935fc4-e11c-0299-6ca0-50e7ae7c8be5"}]}

//This is the full result for multiple email addresses found by monitoring Sugar REST Calls.  This could be pared down to remove some of the items as was done for the above examples.
{"create": [{ "_link": "to", "email_address": "zonk@techadv.com", "deleted": false, "email_address_id": "8ee65e0e-1e65-11e9-ac42-02ae981fbade", "invalid_email": false, "opt_out": false }, { "_link": "to", "email_address": "zink@techadv.com", "deleted": false, "email_address_id": "92d9d48c-1e65-11e9-9a9d-02ae981fbade", "invalid_email": false, "opt_out": false }, { "_link": "to", "email_address": "zunk@techadv.com", "deleted": false, "email_address_id": "955421c2-1e65-11e9-aacb-02ae981fbade", "invalid_email": false, "opt_out": false }]}

These were found by monitoring Sugar via Chome Dev Tools and via trial and error.

This script will look up an email address via the SmartLookup function and return the proper JSON. Note that often times an Email can be sent to multiple people. This code does NOT account for that, though it could be modified to do so.

Function ScriptedField
	dim emailaddr
	dim emailid
	dim res
	emailaddr = "@@ORG:ToRecipients_EmailAddress@@"
	emailid = SmartLookup("EmailAddresses","id", emailaddr)
	res = "{""create"": [{""email_address_id"": """&emailid&"""}]}"
	ScriptedField=res
End Function

This script will look up a Contact and return the Contact's ID in the proper JSON format.

Function ScriptedField
	dim id
	dim res
	id = XrefRead("a2s-contacts", "@@ORG:CONTACTID@@")
	res = "{""create"": [{""parent_type"": ""Contacts"", ""parent_id"": """&id&"""}]}"
	ScriptedField=res
End Function

This script will look up a User and return the User's ID in the proper JSON format.

Function ScriptedField
	dim res
	dim userid
	userid = XrefRead("Users", "@@ORG:MANAGEUSERID@@")
	If userid = "" Then
		LogMessage "No Sugar UserID found for Act UserID: @@ORG:MANAGEUSERID@@"
		GoToNextRow
	Else
		res = "{""create"": [{""parent_type"": ""Users"", ""parent_id"": """&userid&"""}]}"
	End If
	ScriptedField=res
End Function

This C# script looks for matching records in Sugar and if it doesn't find a record, inserts the email address.

object ScriptedField()
{
	string res = "";
	
	string email = Starfish.OriginData["FROMEMAILADDRESS"].ToString();
	
	string uid = SafeToString(Starfish.SmartLookup("Users", "id", "[{\"email_addresses.email_address\":\"" + email + "\"}]"));
	if (!String.IsNullOrEmpty(uid))
	{
		res = "{\"create\": [{\"parent_type\": \"Users\", \"parent_id\": \""+uid+"\"}]}";
	}
	else
	{
		string cid = Starfish.GetStageValue(0, "#ID");
		if (String.IsNullOrEmpty(cid)) {
			cid = SafeToString(Starfish.SmartLookup("Contacts", "id", "[{\"email_addresses.email_address\":\"" + email + "\"}]"));
		}
		
		if (!String.IsNullOrEmpty(cid))
		{
			res = "{\"create\": [{\"parent_type\": \"Contacts\", \"parent_id\": \""+cid+"\"}]}";
		}
		else
		{
			string emailid = SafeToString(Starfish.SmartLookup("EmailAddresses","id",email));
			res = "{\"create\": [{\"email_address_id\": \""+emailid+"\"}]}";
		}
	}
	return res;
}

Email Attachments

As of Sugar v8.0, you can NOT Update the attachments field.' Email Attachments must be inserted into the Email Module's "attachments" json type field. The expected format is:

{"create":[{"_link":"attachments","filename_guid":"715d9962-89f3-11e8-8fe2-02fd0a47dd48","name":"xyz.pdf","filename":"xyz.pdf"}]}

If you want to insert multiple attachments to an Email, you must pass them in all at once. This sample code may help:

object ScriptedField()
{
	int i = 0;
	var res = "{\"create\":[";
	foreach (string s in arrTempFile)
	{
		res = res + "{\"_link\":\"attachments\",\"filename_guid\":\""+ s +"\",\"name\":\""+ arrFileName[i] +"\",\"filename\":\""+ arrFileName[i] +"\"},";
		i++;
	}
	res = res.Substring(0, res.Length-1);
	res = res + "]}";
	return res;
}

Field Types

Assigned User

To assign a user to a record, you can normally pass in the user's ID to the assigned_user_id field. EX:

c3c15eb8-86fc-11e8-96ba-02b1b963c90d

This only works for standard users. If you want to assign a record to a Group User, you must pass in the user's ID to the assigned_user_id field AND populate the assigned_user_name field with some JSON:

{"id":"c3c15eb8-86fc-11e8-96ba-02b1b963c90d","value":"GroupUser","full_name":"GroupUser","date_modified":"2018-07-13T19:28:00-05:00","_acl":{"fields":{}},"_erased_fields":[],"_module":"Users"}

Assigning Teams

On the team_name field you want to supply a bit of JSON with examples below. It should be noted with this method whatever you supply will completely replace the team values, as opposed append to them like in previous versions. You can use a hardcoded field value or return the value from a scripted field but the result needs to look like this:

One Team (will automatically become primary):

[{ "id": "17b6bc3e-38a3-db70-6490-50ed7c3a4d87" }]

Multiple Teams:

[{"id": "17b6bc3e-38a3-db70-6490-50ed7c3a4d87", "primary": true}, {"id": "191cd916-e650-f0e2-4776-50d070d6493e"}]

Note that if you are building this JSON in a VBScript, you need to use double double quotes for a single double quote. See this script I used while reading from a Sugar database using the SQL Server connector.

Function ScriptedField
	'[{"id": "17b6bc3e-38a3-db70-6490-50ed7c3a4d87", "primary": true}, {"id": "191cd916-e650-f0e2-4776-50d070d6493e"}]
	dim arr, i, res
	arr = SmartQuery("team_sets_teams","deleted = 0 and team_set_id = '@@ORG:team_set_id@@' and team_id <> '@@ORG:team_id@@'","team_id","ORIGIN")
	res = "[{""id"": ""@@ORG:team_id@@"", ""primary"": true}"
	for i = 0 to UBound(arr) 
		res = res & ", {""id"": """ & arr(i,0) & """}"
	next
	res = res & "]"
	ScriptedField=res
End Function

Checkbox

When mapping to a checkbox, I've seen different versions require different things. In some cases, you can use a hard coded true. In other cases, you can use a hard coded 1. In the last type, I've had to use a Scripted Value to set the value to "true" or "false", making sure to use all lowercase and not use double quotes.

Function ScriptedField
	ScriptedField=true
End Function

In Sugar 7.8+ I had to supply a 1 or 0:

Function ScriptedField
	If "@@ORG:is_admin@@"=True Then
		ScriptedField=1
	Else
		ScriptedField=0
	End If
End Function

Currency

As of early 2017, due to a Sugar bug, https://web.sugarcrm.com/support/issues/d8596ec8-f324-11e6-accd-d4bed9b70c64, when importing data into a Currency field, you need to set the currency_id and/or currency_id_c field to -99.

Date Fields

Function ScriptedField
  ScriptedField=FormatDate("@@ORG:date_entered@@", "yyyy-MM-dd")
End Function

Datetime Fields

Function ScriptedField
  ScriptedField=FormatDate("@@ORG:date_entered@@", "yyyy-MM-ddTHH:mm:ss")
End Function

Dropdowns

This is the function I use to convert source data to Sugar Dropdown DB values.

Function ScriptedField
	dim source 
	source = "@@ORG:Type@@"
	dim res
	If source = "Research" Then
		res = "Research_Site"
	ElseIf source = "Goverment" Then
		res = "Government_Academia"
	Else
		res = source
	End If
	ScriptedField=res
End Function

Multi-select Dropdowns

In newer versions of Connect Creatio, data for Multiselect fields must be supplied as a JSON Array of strings.

["Option A", "Option B"]

In older versions of Connect Creatio, data for Multiselect fields must be supplied as text of how it would look in the database.

^Option A^,^Option B^

Email (As in an Account or Contact's Email Address)

To insert or update an email address, you need to pass JSON into the "email" field. I recommend doing this as a "Hardcoded" function field. The JSON needs to be in this format:

[{"email_address":"test1@gmail.com","primary_address":true}]

You can pass in the email address from your source with the Insert Variable button. Then your JSON will look like this:

[{"email_address":"@@ORG:Email Address@@","primary_address":true}]

If you are passing in more than one email address or to set the email address' attributes, you need more complicated JSON. This JSON sets email address, primary address, invalid email, reply to address and opt out settings of each email address supplied. The json looks like this:

[{"email_address":"burgers@example.com","primary_address":false,"invalid_email":false,"reply_to_address":false,"opt_out":false}]

This is a sample VB Script that will parse out multiple email source fields and set the email address and opt out setting of each address.
Please note that in vbscript, you have to use 2 double quotes to tell VBScript that you actually want 1 double quote in your text value. This is why you see 2 double quotes next to each other in some places in this script.
Note that as of 7.11, Sugar does not seem to accept an empty string into the email field. So the below script needs to be updated to return [{"email_address":"","primary_address":true}].

Function ScriptedField
	dim res
	res = "["
	If "@@ORG:Email@@" <> "" Then
		res = res & "{""email_address"":""@@ORG:Email@@"",""primary_address"":true,""opt_out"":@@ORG:HasOptedOutOfEmail@@},"
	End If
	If "@@ORG:Alternate_Email_1__c@@" <> "" Then
		res = res & "{""email_address"": ""@@ORG:Alternate_Email_1__c@@"",""primary_address"": false,""opt_out"": @@ORG:HasOptedOutOfEmail@@},"
	End If
	If "@@ORG:Alternate_Email_2__c@@" <> "" Then
		res = res & "{""email_address"":""@@ORG:Alternate_Email_2__c@@"",""primary_address"":false,""opt_out"":@@ORG:HasOptedOutOfEmail@@},"
	End If
	If Right(res,1) = "," Then
		res = Left(res,Len(res)-1)
	End If

	res = res & "]"
	If res = "[{}]" Then
		res = "[{""email_address"":"""",""primary_address"":true}]"
	End If
	ScriptedField=res
End Function

Relate Fields

When you create a Relate field in Sugar, not a field created by adding a Relationship, two fields are created for the API: fieldname_c relate type and an id (36) type field which is the name of the related to module followed by _id_c. If you add multiple relate fields for a single module, then you will get multiple numbered id (36) fields.

For example, if you add a Relate field to the User module called "usr_c", you will see 2 fields in StarfishETL:

• user_id_c of type "id (36)"
  
• usr_c of type "relate"
  

You MUST set the id (36) field in the StarfishETL map. Setting the relate type field will do nothing.

Tags

Even though you can see the "tag" field on a module, Tags are not really a field. They are a relationship between a module record and a record in the Tags module. To associate a Tag with another Record, see: Adding a Tag To A Record

Insert Relationships

In the Manage Stages dialog box, you can create a Stage for a table called "Relationship". This Stage should always be an Insert Stage Type. This stage will then contain 5 fields:

1. module - Fill in your one of the module's names here. This is generally the value you see in the URL when viewing a module. Ex: Notes.
   
2. module_id - The ID of the record that exists in the Module you specified in the Module field. If you just inserted or updated this record, you will want to use the Insert Variable button and then a "Stage Value" variable.
   
3. link_name - This is the name of the link that exists between the 2 modules you want to relate. You can sometimes find this in the Relationship section of Studio. Sometimes the names in Studio aren't quite right so you have to look at the vardef code for the module.
   
4. remote_id - The ID of the record that you want related to the Module you previously specified.
   
5. fields - This field can be left blank.
   

Note that the Module and Link names are not always clear. You may need to look at the Vardefs of your instance to find the proper names.

Adding a Tag To A Record

To add a Tag to a record, you must Insert into the "Relationship" table. The relationship name between a Module and the Tags module always seems to be "tag_link". I'm unsure what the relationship name the other direction is, so I always use Tags as my remote_id. To relate a Tag to a module, the 5 fields will contain:

1. module - The module name. This is generally the value you see in the URL when viewing a module. Ex: Notes.
   
2. module_id - The ID of the record that exists in the Module you specified in the Module field. If you just inserted or updated this record, you will want to use the Insert Variable button and then a "Stage Value" variable.
   
3. link_name - This seems to always be "tag_link" when Tags is not the Module specified in #1 of this list, module.
   
4. remote_id - The ID of the Tag you want to associate with your record.
   
5. fields - This can be left blank.
   

Smart Lookups

Function Window

To do a smart lookup inside the Function Window, select your return field and lookup module. Then supply a filter. Any of the filter examples from the Origin section will work:

[{"id":"84b1c05c-1043-05c8-ced8-53a2ed18427b"}]

A more complex filter using $not_null and $not_equals:

[{"$and":[{"import_source_id_c":{"$not_null":""}},{"import_source_id_c":{"$not_equals":""}}]}]

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

Checking to see if a field is blank using $is_null:

[{"status_c":{"$is_null":""}}]

Grab records modified after a certain date. This is useful for creating jobs that only look at records modified since the last run date/time of Connect Creatio.

[{"date_modified":{"$gte":"2015-07-15T05:03:00+00:00"}}]

VBScript

When filtering in a smart lookup in a VBScript function, the filter must use double-double quotes so the script doesn't think you are ending or starting a string:

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

A more complex filter:

res = SmartLookup("Users", "id", "[{""$and"":[{""last_name"":""@@ORG:IENSalesRep_Last@@""},{""first_name"":""@@ORG:IENSalesRep_First@@""}]}]")

Email Address ID Lookup

In v7.11 of Sugar, the emails module was upgraded to the sidecar framework. Within this framework, you often need to find the ID of an Email Address. This is NOT the same as the ID of a Lead, Contact, etc with that Email Address. Every email address has a single unique Email Address ID even if the email address is shared across multiple people records. To retrieve this unique Email Address ID, use a smart lookup.

id = SmartLookup("EmailAddresses","id","support@starfishetl.com")

Retrieve Records Related To Parent Record

This makes use of a special way of calling the SmartLookup function. You use “relationships” as the tablename and then pass 3 comma-separated parameters to the criteria. For example: "Accounts,@@ORG:id@@,contacts". The format is ModuleName,Module ID,RelationshipName. It will return either a blank string if there are no related records or a comma-separated list of ids – in this case Contact IDs.

res= SmartLookup("relationships","id","Accounts,@@ORG:id@@,contacts")

Very often when using this code I find myself wanting to query the ORIGIN, not the default DESTINATION. To do this, use the optional parameters:

res = SmartLookup("relationships","id","TAI_Contracts,@@ORG:tai_contracts_o1_operationstai_contracts_ida@@,tai_contracts_accounts",False,"","ORIGIN")

Once you have a comma-separated list of ids, you would probably want to split the list into an array and repeats calling the stage for each item within the array.

Retrieve value of Destination to determine if we should overwrite it

Function ScriptedField
	dim destVal
	destVal = SmartLookup("Accounts","production_rate_c","[{""id"":""@@ORG:account_id@@""}]")
	'LogMessage destVal
	dim res
	if destVal <> "" Then
		res = ""
        'We might want to skip this update if the destination already has a value.
        'GoToNextRow
	Else
		res = "@@ORG:production_rate_c@@"
	End If
	ScriptedField=res
End Function

Querying Multiple Values with SmartQueryDT

Sometimes you need multiple values from your destination. Rather than querying each value as a separate SmartLookup, you can query for all of the fields at once and store them in a DataTable using SmartQueryDT.

I wanted to query for all of the values in a Before Operation and then use them in various field mappings.

First, I declared my DataTable object in .NET Global

System.Data.DataTable dtAccounts;

In my Before Operation, I made sure to clear out any existing values in the DataTable first, in case of a row with no results. Then, I queried Sugar for the values I needed to store in the DataTable.

dtAccounts = new System.Data.DataTable();
dtAccounts = Starfish.SmartQueryDT("Accounts","[{\"id\":\""+acctID+"\"}]","primary_business_buckets_c,fleet_size_c,num_of_employees_c,types_of_activities_prod_cat_c,types_of_activ_buckets_c,past_attendee_c");

Finally, I noted the index of each field, and used the index to access the appropriate value where I needed it in scripted field mappings. The example below would return the value for the fleet_size_c field.

	if (dtAccounts.Rows.Count>0) 
		{
			existing = dtAccounts.Rows[0][1].ToString();
		}

Smart Query using Custom Endpoint

If you need to query a custom endpoint in your Sugar REST Origin or Destination, you MUST use C# and the SmartQueryDT function. Note that to use the SmartQueryDT function, you must add the following to the .NET Global External Assemblies: "System.dll,System.Xml.dll,System.Data.dll".

I've found that using the SmartQueryDT function with our custom SelectSQL endpoint will only work in before or after row operations. It doesn't seem to work on before or after stage operations. My guess is that the connection is "busy" between stages and is not "busy" between rows.

object ScriptedField()
{
	string res = "";
    //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");
	//Starfish.LogMessage(dtContacts.Columns.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] 
    //Check to make sure there is at least one record in the DataTable so .Rows doesn't error.
	if (dtContacts.Rows.Count>0) 
	{
		//Starfish.LogMessage(dtContacts.Rows[0][0].ToString());
		res = dtContacts.Rows[0][0].ToString();
	}
    //If we are expecting many records, we can loop through them.
    foreach (System.Data.DataRow row in dtContacts.Rows)
    {
        //Create a comma separated list of ContactIDs.
        strContacts = strContacts + row[0].ToString() + ',';
    }
    //Remove the excess comma at the end.
    strContacts = strContacts.Substring(0,strContacts.Length-1);
    //Starfish.LogMessage(strContacts);
	return res;
}

Warnings/Bugs/Errors

• When connecting to the SugarCRM REST API with StarfishETL, it returns the error: "Invalid StatusCode 422".
  • This is generally a result of not having registered the StarfishETL platform with Sugar. See https://support.sugarcrm.com/Documentation/Sugar_Versions/9.0/Pro/Administration_Guide/Developer_Tools/index.html#Configure_API_Platforms. Please add our platform as all lower-case "starfish" and this should allow the connection.
    

• SmartLookups and Origin Filters against the User Module fail. In fact, every time I read the Users module as an Origin using the Sugar REST Connector, I run into issue. I never do this. I use SOAP or a custom SelectSQL end point where I can write my own SQL code against the user table(s). It's possible that the REST API will only return Active users.
  • Workaround: Try the SOAP API.
    

• Cannot authenticate with the REST API if the username has a period, ".", in it.
  • Workaround: Use a username without a period.
    

• Error: Newtonsoft.Json.JsonReaderException: Error reading JArray from JsonReader. Path , line 0, position 0.
  • I got this when I was attempting to write a blank, "", value into the email field. To get around this issue, I checked the box in Manage Stages to ignore black field writes. You could also write in the proper "empty" json for an email field.
    

Sugar Specific Code/Information

SQL Statements to Purge Test Data From Sugar

Don't forget to add statements for any custom modules you may have or OOTB modules you are using but are not listed below.

truncate accounts;
truncate accounts_audit;
truncate accounts_contacts;
truncate accounts_cstm;
truncate accounts_opportunities;
truncate contacts;
truncate contacts_audit;
truncate contacts_cstm;
truncate contacts_users;

truncate activities;
truncate activities_users;
truncate subscriptions;
truncate sugarfeed;

truncate calls;
truncate calls_contacts;
truncate calls_users;
truncate emails;
truncate emails_text;
truncate emails_beans;
truncate email_addr_bean_rel where bean_module <> 'Users';
truncate email_addresses where id in (select email_address_id from email_addr_bean_rel where bean_module <> 'Users');
truncate meetings;
truncate meetings_contacts;
truncate meetings_leads;
truncate meetings_users;
truncate notes;
truncate notes_cstm;
truncate outbound_email;
truncate tasks;

truncate cases;
truncate cases_audit;
truncate bugs;
truncate bugs_cstm;

truncate job_queue;

truncate opportunities;
truncate opportunities_contacts;
truncate opportunities_cstm;
truncate opportunities_audit;
truncate revenue_line_items;
truncate revenue_line_items_audit;
truncate revenue_line_items_cstm;
truncate quotes;
truncate product_templates;
truncate product_templates_audit;
truncate product_templates_cstm;
truncate contracts;
truncate contracts_cstm;
truncate products;

truncate leads;
truncate leads_audit;
truncate leads_cstm;
truncate prospects;
truncate prospects_cstm;
truncate prospect_list_campaigns;
truncate prospect_lists;
truncate prospect_lists_prospects;

truncate documents;
truncate campaigns;
truncate project;
truncate project_cstm;
truncate kbdocuments;
truncate kbdocuments_cstm;