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.

Example 1: XML

The value of this column for each record is whatever the value is of the lob parameter.

XML equivalent

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

Example 2: XML

The values of two columns are concatenating together.

XML equivalent

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

Example 3: JavaScript

This example splits 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>

Attributes

name

The user defined name for each calculated column. This is used in when you want to indicate the name of the sourceColumn.

formula

CQL expression used to define formula. Supported functions:

dataType

The data type of each column could be Text, Date, Number or Bool.

maxLength

The max length of data in the column.

isMandatory

Boolean value that determines if the field is a mandatory column to create a row entry.

validateData

Boolean value determining whether to validate the data before inserting. Valid data means to fit all the constraints of the column (dataType, maxLength, isMandatory, inputFormat). If the data isn't valid and validateData is true, then the entry won't sync into the table. The Execution Errors Table also updates with the appropriate Error Type (Invalid Format Exception, Max Length Violation, Mandatory Rule Violation, Input Format Exception)

description

Description of the column.

trimWhitespace

Boolean value that determines whether to trim white space.

Overview

This page provides information on both Schema Columns (used when configuring Data Sync Sources) and Column Mappings (used when configuring Data sync Destinations).

Schema columns

Schema columns refer to your mapping on your data source. For example, if your source is a CSV with the columns 'Name', 'Age', and 'Company', you would set up three matching schema columns in the Connections UI or data sync XML. These schema columns map to your destination columns for your data sync target, so that the data knows where to go.

The only difference between the setup of schema columns in the Connections UI compared to data sync XML is the addition of the Alias column, which only appears in the Connections UI. The Alias column gives the user an alternative name to the column mapping (usually used for easier readability). The column types are detailed below.

Standard column

Fill in the following attributes for a Standard Column (Image 1):

• Name: The name of your column

• Formula: The formula associated with your calculated column

• Data Type: The return data type of your column, this can be either:

  • Text

  • Date

  • Number

  • Boolean

• Description: Describe your column

• Advanced Settings:

  • You can select if you want this column to be mandatory

  • You can choose whether your data must be validated

• For Text data types, you can choose whether to trim the whitespace.

To add in a Transformation > String Replacement enter the following:

• Pattern for your string replacement

• Replacement

Standard calculated column

Fill in the following attributes for a Standard Calculated Column (Image 2):

• Name: The name of your column

• Formula: The formula associated with your calculated column

• Data Type: The return data type of your column, this can be either:

  • Text

  • Date

  • Number

  • Boolean

• 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.

Conditional calculated column

Fill in the following attributes for a Conditional Calculated Column (Image 3):

• Name: The name of your column

• Data Type: The return data type of your column, this can be either:

  • Text

  • Date

  • Number

  • Boolean

• 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.

• Condition:

  • Name:

  • IF: Click Edit to create the "if" for your Conditional Statement (Image 4)

• Then: Click Edit to create the "then" for your Conditional Statement (Image 5)

• Default: Click Edit to create your default expression (Image 6)

JavaScript Calculated Column

Fill in the following attributes for a JavaScript Calculated Column (Image 7):

• Name: The name of your column

• Data Type: The return data type of your column, this can be either:

  • Text

  • Date

  • Number

  • Boolean

• 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.

• Script: Enter in your JavaScript

Columns in XML

This XML element defines each column and their data type in the data set :

Attributes descriptions

name

The user defined name for each column. This is used in when you want to indicate the name of the sourceColumn.

dataType

The data type of each column could be Text, Date, Number, Boolean, Geometry, or Geography.

To sync into a Cinchy table with a Geometry or Geography column, those respective data types must be used in the data sync, and the input should be in well-known text (WKT) format.

For example, given line 1 of a .csv file:

Name, Location, Age

The ordinal for Age would be 3.

maxLength

The max length of data in the column.

isMandatory

Boolean value that determines if the field is a mandatory column to create a row entry.

A defined SyncKey column of any data type can be checked for NULL values using isMandatory=true. When validation fails, an error message is displayed in the command line. For other columns when validation fails, the Execution Errors Table is updated with Error Type, Mandatory Rule violation for that column and row that failed.

validateData

Boolean value determining whether to validate the data before insertion. Valid data means to fit all the constraints of the column (dataType, maxLength, isMandatory, inputFormat). If the data isn't valid and validateData is true, then the entry won't be synced into the table. The Execution Errors Table is also updated with the appropriate Error Type (Invalid Format Exception, Max Length Violation, Mandatory Rule Violation, Input Format Exception)

trimWhitespace

Boolean value determining whether to trim white space.

description

Description of the column.

inputFormat

Elements:

Column Mappings

Column mappings defines how a single column from the data source maps to a column in a target table. Each <ColumnMapping> has both a source and a target. If the destination is a Cinchy table and the target column is a link, then a third attribute becomes available called linkColumn which you can use to specify the column used to resolve the linked record from the source value. The value of sourceColumn should match name attribute of Source . The value of targetColumn should match that of the target table.

Below is an example of a Column Mapping in the experience followed by the equivalent XML. In the experience, the Source Column attribute is a dropdown of columns configured in the Source Section.

Attributes:

sourceColumn

The name of the column in the data source. The name corresponds to the user defined name from the elements in the schema.

targetColumn

The name of the column in the target table. This would be a table that's already created in Cinchy and defined in the Target.

linkColumn

The name of a column from the linked table. If the target column is a linked column from another table, you may input data based on any of the linked table's columns.