Calculated column examples

A calculated column uses values from other fields in the record for its evaluation. Refer to this page for examples on using calculated columns within a data sync.

Examples

Examples 1 and 2 show calculated columns within the Connections UI and their relevant XML.

Example 3 demonstrates the use of JavaScript in Calculated Columns.

Parameters

The examples use the following attributes.

• Name: The name of your column. This is used in <ColumnMapping> when you want to indicate the name of the sourceColumn.
• Formula: The formula associated with your calculated column. Refer here for a list of supported calculated column functions.
• Data Type: The return data type of your column, this can be either:
  • Text
  • Date
  • Number
  • Boolean

caution

If a Destination column is being used as a sync key, its source column must be set to type=Text, regardless of its actual type.

• Description: Describe your calculated column
• Advanced Settings:
  • You can select if you want this column to be mandatory.
  • You can choose whether your data must be validated.

info

• If both Mandatory and Validated are checked on a column, then rows where the column is empty are rejected
• If just Mandatory is checked on a column, then all rows are synced with the execution log status of failed, and the source error of "Mandatory Rule Violation"
• If just Validated is checked on a column, then all rows are synced.

Supported Functions

The following functions are supported within calculated columns in Connections:

Function	Details
CONCAT(colA, colB, 'literal value1', 'literal value2')	Concatenates multiple columns, parameters or literal values together. Supports two or more parameters.
row_number()	This is the numeric row number of files (Excel, delimited, fixed width). Currently not supported in conjunction with other formulas/parameters.
isnull(colA,'alt value')	If the first column is null, use the second value (can be a literal or another column).
hash('SHA256',colA)	Hashes the column using the algorithm specified. It is recommended to salt any values prior to hashing them.

Example 1: @value

In this example, the value of the Date column for each record is taken from the @mydate parameter.

<Parameters>
<Schema>
  <CalculatedColumn name="date" formula="@mydate" dataType="Text" maxLength="100" 
      isMandatory="false" description="My calculated column" trimWhitespace="true"/>
 </Schema>

Example 2: CONCAT

In this example, the values of two columns (firstname and lastname) are concatenated together into the name column.

<Parameters>
<Schema>
  <CalculatedColumn name="name" formula="CONCAT(firstname, lastname)" 
      dataType="Text" maxLength="100" isMandatory="false" 
      description="" trimWhitespace="true"/>
 </Schema>

caution

The CONCAT function supports more than 2 parameters, and you must enclose any literal values in single quotes ( 'abc')

Example 3: JavaScript

This example uses JavaScript to split a [Name] column with the format "Lastname, Firstname" into two columns: [First Name] and [Last Name].

       <Schema>
            <Column name="Name" dataType="Text"/>
            <CalculatedColumn name="First Name" ordinal="3" dataType="Text">
                <Script>
            function firstName(Name) {
                return Name.substr(Name.indexOf(', ')+2);
            }
                        
            function calc(currentRecord) {
                if (currentRecord['Name'])
                    return firstName(currentRecord['Name']);
                    return '';
            }            
            </Script>
            </CalculatedColumn>
            <CalculatedColumn name="Last Name" dataType="Text">
                <Script>
                    function lastName(Name) {
                        return Name.substr(0,Name.indexOf(', '));
                    }
    
    
                    function calc(currentRecord) {
                        if (currentRecord['Name'])
                        return lastName(currentRecord['Name']);                            
                        return '';
                    }   
                    </Script>
            </CalculatedColumn>
        </Schema>