With the launch of SmartConnect.com, another scripting language has been introduced for users to create new calculated columns and scripts. We’re going to take a quick look at a couple useful scripts and commands that will assist in building out integrations.

For these examples I’ll describe what each script should help with, then list out what the source field names would be for each example, and finally I’ll show the actual syntax that would be used to build each.

Table of Contents

  1. Sample Concatenation
  2. Sample Character Replace
  3. Sample String Truncate
  4. Sample Date Formatting
  5. Sample Switch Statement
  6. Sample Restriction
  7. Sample Multi-Tenant Destination

Sample Concatenation

Let’s start with a straightforward scenario. We have a first name and a last name in the source data and we need to concatenate it into 1 field for the destination.


The code will take the first name and last name columns and apply a trim statement to each. The trim statement removes any extra whitespace characters before or after the actual data. The single quotes in the middle of the statement are there to ensure a space is added between the first and last name when building the new string.

Sample Character Replace

In this scenario our source column contains characters not allowed by the destination system, so we need to replace all occurrences of invalid characters within the script.


The code first converts all text in phone number field to upper case. Next it replaces all instances of the three strings EXT, EX, X with the character #. The syntax /x/g is a global identifier meaning all occurencies of the text will be replaced. The pipe(|) character represents an OR statement and can be removed if only replacing a single string.

Sample String Truncate

In this scenario our source column length is too long for the target field. We need a script to check if the source string is longer than the max allowed characters and to truncate the string if longer than the max length.


The code requires two inputs. First, ‘length’, is an integer representing the max characters allowed by the destination field. The second, ‘String’, is the source string to be evaluated. The third line takes these two inputs and selects a substring starting at the first character to the end of your input string unless the length exceeds the value of the ‘length’ variable. If the length of your input string is longer than the ‘length’ variable the output will cut the string at the max length.

Sample Date Formatting

In this scenario you have a date field in the data source that needs to be formatted correctly to fit into the destination system. The following example shows how to format the date in 3 different formats based on the target system requirements.


The code first declares the source field ‘documentDate’ as a date object. Then we declare a variable named od to store the output date. Next lines 6, 9, and 12 format the date into different formats. You can remove 2 of these 3 outputs based on the desired formatting.

Sample Switch Statement

In this scenario we need to perform field manipulation based on the value of a different source field. This can be useful when the logic is too complex for a translation table. In the below example we evaluate the document type and modify the amount field based on the type.


The code checks the value of the ‘documentType’ field. If the value is Order or Invoice it will return the source value for the ‘Amount’ field. If the value is Credit or Refund it will return a negative value for Amount. If documentType is not one of the four mentioned values it will default the value to 0.

Sample Restriction

In this scenario we are creating a line restriction in the destination to filter out records that shouldn’t be sent to this destination mapping. Here we are checking the value of the ‘Amount’ field and importing only records with a positive amount.


The code checks the value of the ‘Amount’ field. If the value is greater than zero, the script returns ‘true’ telling SmartConnect to send the data to the target service. If the Amount is less than or equal to zero, the script will return ‘false’ telling SmartConnect to not send the data to the target system.

Sample Multi-Tenant Destination

The final area we may need a script is for a multi-tenant destination. A multi-tenant destination is a powerful feature allowing a single map to dynamically switch connector instances and companies based on a source field value.

Configure a Multi-Tenant Destination in the integration process > Target > Companies > Custom


The code evaluates the ‘billingcountry’ field from the data source and directs the record to the SandboxOdata_CRONUSGBInc company if the source data GB. The else clause at the end defaults all other countries to the SandboxOdata_CRONUSUSAInc company.
*Note: in Javascript ‘==’ is used for comparing values and ‘=’ is used to assign a value to a variable.

Conclusion

Each of those examples and the code included can be taken and adjusted as needed for real world integrations and problems that need to be solved. The Javascript used within the scripting windows in SmartConnect.com will match standard syntax that can be built in any editors. Along that point, using online Javascript editors to test out different statements and code snippets is extremely helpful. There are multiple options out there to try, but one that I personally make use of is: JSFiddle



If you have any further questions, you can email us at support@eonesolutions.com.