Skip to main content

Calculated Columns

Calculated Columns are a powerful feature of Data Sync that allow you to add new columns to your data source. These allow you to easily transform values via the in-built functions to create new row values, and can be used in the Schema Map like any other column and mapped to your target.

Add Calculated Column​

To add a new calculated column press the Add Calculated Column button in the data source toolbar or press the shortcut keys ALT+C.

Add Calculated Column

This will open the Calculated Column configuration window, where you can specify a name, return data type, and write an expression for your calculated column.

Blank Calculated Column

The name must be unique within your project, you cannot use an existing name from your Data Source Schema. We generally use the prefix fx_ to each calculated column to ensure it is unique.

The expression is a C# value that is calculated for each row in your data source. However we have made this more "Excel like" with the introduction of many functions for typical scenarios. Check out the next pages for the functions documentation for explanations and examples of each function.

An example of a calculated column can be seen below where we use the FORMAT function to create an email address, replacing the row ID value for each row.

Create Email Address

This will produce a result for each ID returned such as: testuser1@simego.com, testuser2@simego.com, testuser3@simego.com etc.

This is the same as writing the code found below in Dynamic Columns, however with Calculated Columns this is much quicker and easier.

public string Email 
{ 
    get
    {
        return string.Format("testuser{0}@simego.com", ID);
    }
}

Once you've added your expression, click the Build button to compile your calculated column and verify that the syntax is correct.

Build Results

Edit Calculated Column​

To edit a calculated column you can either double click onto the column to open the editor, or you can select the column and then click the Add Calculated Column button.

Edit Calculated Column

You can then make the changes to your function as needed and click OK to apply the update.

Delete Calculated Column​

To delete a Calculated Column, select the column and click the Delete Calculated Column button in the data source toolbar.

Delete Calculated Column

Advanced String Manipulation​

Calculated columns can be used for more advanced string manipulation, such as formatting telephone numbers. For example, the following expression will format a telephone number to include leading zeros and display as (xxx) xxx-xxxx:


IF(AND(LEN(targettext)==11,ISNUMBER(targettext),LEFT(targettext,1)=="0"),
FORMAT("({0}) {1}-{2}",
MID(targettext,2,3),
MID(targettext,5,3),
RIGHT(targettext,4))
,targettext)

Advanced String Manipulation

You can test that your syntax is correct and the function builds by selecting the Build button.