Use JSONLint to validate JSON: https://jsonlint.com/.
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"}]
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.
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
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.
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.
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.
StarfishETL cannot currently do this. If you need this capability, please email support@starfishetl.com.
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.
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;
}
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;
}
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"}
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
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
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.
Function ScriptedField
ScriptedField=FormatDate("@@ORG:date_entered@@", "yyyy-MM-dd")
End Function
Function ScriptedField
ScriptedField=FormatDate("@@ORG:date_entered@@", "yyyy-MM-ddTHH:mm:ss")
End Function
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
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^
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
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:
You MUST set the id (36) field in the StarfishETL map. Setting the relate type field will do nothing.
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
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:
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.
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:
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"}}]
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@@""}]}]")
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")
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.
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
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();
}
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;
}
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;
Parameter | Description |
SugarCRM Instance URL |