Schema Columns

This page details the various types of Columns available within a Source Data Sync

Table of Contents

1. Columns in the Connections Experience

In the Connections experience, the attributes are mostly the same as in the XML, with the addition of the Alias column which is only in the experience. This attribute allows the user to give an alternative name to the column mapping (usually used for easier readability). The various types of columns are detailed below.
Note that some source types have unique parameters not otherwise specified in other sources. You can find information on those, where applicable, in the source's main page.

1.1 Standard Column

Fill in the following parameters 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
    • Bool
    • Geometry
    • Geography
If a source column (of any type) is being synced into a Cinchy Target Table link column, the source column needs to be of dataType="Text", no matter what type it actually is.
  • 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
    • If your data type was chosen as "text", you can choose whether to trim the whitespace or not
You can choose to add in a Transformation > String Replacement by inputting the following:
  • Pattern for your string replacement
  • Replacement
Note that you can have more than one String Replacement
Image 1: Standard Column

1.2 Standard Calculated Column

Fill in the following parameters 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
    • Bool
    • Geometry
    • Geography
If a source column (of any type) is being synced into a Cinchy Target Table link column, the source column needs to be of dataType="Text", no matter what type it actually is.
  • 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
Image 2: Standard Calculated Columns

1.3 Conditional Calculated Column

Fill in the following parameters (Image 3):
Image 3: Conditional Calculated Column
  • Name: The name of your column
  • Data Type: The return data type of your column, this can be either:
    • Text
    • Date
    • Number
    • Bool
    • Geometry
    • Geography
If a source column (of any type) is being synced into a Cinchy Target Table link column, the source column needs to be of dataType="Text", no matter what type it actually is.
  • 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)
Image 4: Creating your Conditional statement
  • Then: Click Edit to create the "then" for your Conditional Statement (Image 5)
Image 5: Creating your Conditional Statement
  • Default: Click Edit to create your default expression (Image 6)
Image 6: Creating your Default Expression

1.4 JavaScript Calculated Column

Fill in the following parameters (Image 7):
  • Name: The name of your column
  • Data Type: The return data type of your column, this can be either:
    • Text
    • Date
    • Number
    • Bool
    • Geometry
    • Geography
If a source column (of any type) is being synced into a Cinchy Target Table link column, the source column needs to be of dataType="Text", no matter what type it actually is.
  • 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
Image 7: JavaScript Calculated Column

2. Columns in XML

This XML element defines each column in the data set as well as their data type:
<Column
name="string"
dataType="Text"| "Date"| "Number"| "Bool"| "Geometry"| "Geography"
ordinal="int" -- Depends on the data source
maxLength="int" --OPTIONAL
isMandatory=["true", "false"] --OPTIONAL
validateData=["true", "false"] --OPTIONAL
trimWhitespace=["true", "false"] --OPTIONAL
description="string" --OPTIONAL
inputFormat="string" --OPTIONAL
>
...
</Column>

Attributes:

name
The user defined name for each column. This is used in <ColumnMapping> when you want to indicate the name of the sourceColumn.
dataType
The data type of each column could be Text, Date, Number, Bool, Geometry, or Geography.
If a source column (of any type) is being synced into a Cinchy Target Table link column, the source column needs to be of dataType="Text", no matter what type it actually is.
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.
The dataType affects how the source and target data is parsed, and also determines how the fields are compared for equality. If your sync keeps updating a field that has not changed, double check your data types.
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 determining 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, error message is displayed in the command line. For other columns when validation fails, Execution Errors Table is updated with Error Type, Mandatory Rule violation for that column and row that failed.
validateData
Boolean value determining whether or not to validate the data before inserting. Valid data means to fit all the constraints of the column (dataType, maxLength, isMandatory, inputFormat). If the data is not valid and validateData is true, then the entry will not be synced into the table. Also, Execution Errors Table is updated with appropriate Error Type (Invalid Format Exception, Max Length Violation, Mandatory Rule Violation, Input Format Exception)
trimWhitespace
Boolean value determining whether or not to trim white space.
description
Description of the column.
inputFormat
Date fields support the inputFormat which adheres to the c# .net DateTime.ParseExact format. See here for reference.
inputFormat attribute is useful when source file need some format changes in the input data

Elements: