Layer2 Cloud Connector - Dynamic Columns and Transformations

Version 7 of the Layer2 Cloud Connector comes with a Dynamics Columns feature. This feature allows users to define new data columns in addition to the ones that are returned by a data entity. The values of these columns are calculated at run-time via C# code, and can be based on the content of other columns. This allows for customized calculations, conversions, translations, and formatting of the data to be synchronized between 100+ systems and apps, e.g. Microsoft SharePoint, Office 365, Dynamics, SQL, ERP/CRM and more.

Layer2 Cloud Connector Dynamic Columns Feature - How does it work

​To add a Dynamic Column to a data entity you have to open the Advanced Settings section of the certain connected data entity. You will find +/- buttons to add new or remove existing Dynamic Columns.
Fig.: Add or remove Dynamic Columns to / from a connected data entity.
To edit a specific Dynamic Column you can double-click it in the list. The Dynamic Column Editor pop-up will be shown in the Connection Manager to specify the column name, select existing columns, and add your own C# code for calculations, conversions, translations, transformations, or formatting.
Fig.: The Layer2 Cloud Connector Dynamic Column Editor allows to add your own C# code.

Layer2 Cloud Connector Dynamic Columns - What you can do

Using Dynamic Columns you can:
  • Implement your own business logic directly in the Layer2 Cloud Connector connection: You don't need to add workflows or stored procedures in the connected systems to trigger after data changes.​
  • Use C# to implement your own simple, but also complex operations.
  • Access all columns of your data entity in the code.
  • Make use of simple expressions: As long as the code is an expression that can be resolved into a value, no return statement is necessary. 
  • The data type of the column is automatically detected by the returned values. All returns need to have the same data type or the verification will report an error.
  • Define Get and Set functions to read and write transformed values.
  • You can not only set the value of the current column, but also values of other columns in a dynamic column.
  • Verify and cleanup your source data before writing to a target system that possibly does not except specific values like NULLs, blanks, or special characters. That could highly increase the quality of your data sync.
  • Calculate values directly in the field, e.g. volume pricing based on the amount of items, etc.
  • Conditional field selection: For example, the source system contains products with a regular price, a discounted price, and a flag that defines if the product should use the discount. The target system should only receive a final price and no information about discounts.
  • Current date and time operations, e.g. to set these values in an item of a target system.
  • Better manage SharePoint like lookup fields: In some cases you might need to have the ID part, in others the value part of the lookup field which is returned as ID;#value otherwise in SharePoint.
  • Multi-Value Lookup Field Translations: In the case of a migration (one-way sync) from one SharePoint to another, a source list with a lookup column that allows multiple values has to be sent to the new list in the new SharePoint. There is a similar lookup set up regarding the labels, but the IDs do not match those of the source SharePoint, leading to issues. With a simple dynamic column that only provides and sets the lookup labels without knowing the Id, the problem can be solved. The dynamic column has to be mapped to the Multi-Lookup column of the other data entity.
  • Translations based on dictionaries: You can make use of a CSV-file to assign values or IDs to each other and use it in dynamic columns for translations, e.g. in case of SharePoint User or Group IDs of different systems.
  • Set SharePoint Managed Metadata automatically: Managed metadata can be assigned dynamically to items and documents based on dynamic columns, e.g. depending on the file path, file name and other document or item properties.

Layer2 Cloud Connector Dynamic Columns - Examples

Please take a look into the Layer2 Cloud Connector User's Guide to find examples for the above.

Dynamic Columns - Known Issues and Workarounds

  • ​The Dynamic Columns feature is only available in the .NET 4+ version of the Layer2 Cloud Connector. It is not available in .NET 3.5. Please install a -NET 4+ version of the Cloud Connector on a current Windows Operating System to close this gap
  • The definition of dynamic columns is based on the programming language C# in version 6. Either a valid C# expression or a valid method-body returning a value on all possible code-paths are acceptable. 
  • The code used includes common parts of the .NET Class-Library, but currently does not allow loading additional assemblies or namespaces. It is, however, possible to load additional assemblies through reflection.
  • In any case the code can access values of fields of the current record (or item, document) only. There is no option to access other records of the data set. 
  • The Dynamic Column code is executed for each field access. There is no option yet, to manage some kind of initializations, store some values to better access it later on.
  • Improvements are planned for future releases based on partner and customer feedback.
  • Please contact [email protected] for any questions or requirements.
Share this site on Xing
Share this site on Google+
Share this site on LinkedIn

This tool fills an open gap with SharePoint integrating external data sources. I've tried - Oracle, MS SQL, and others. And it's really fast! A must-have for any SharePoint implementation, whether standalone or Office 365. You can't live without.

Frank Daske

Frank Daske

Business Development Manager
+49 (0) 40 28 41 12 - 30
[email protected]

Please note that the 011 exit code must be dialed first for all international calls made from the USA, Canada and Australia.

Related News

Layer2 now connects 100+ commonly used IT systems with Twitter via its Layer2 Cloud Connector

The Layer2 Cloud Connector is now able to connect Twitter to integrate data to a wide range of IT backend systems including Office 365, SharePoint, SQL, ERP/CRM.

New Microsoft Teams Connectors: Layer2 adds 100+ typically used backend systems

​The Layer2 Cloud Connector is now able to connect to Microsoft Teams to quickly integrate more than 100 IT backend systems including local SQL, ERP/CRM.
Related Downloads
PDF Image

Cloud Connector Instruction

Download this step-by-step instruction to connect and sync your corporate or cloud data sources bi-directional to any native SharePoint 2010/2013/2016 list or library, including Microsoft Office 365.
PDF Image

Feasibility Study: Connecting local SQL Server to SharePoint Online

The study compares different approaches for SharePoint Online data integration regarding performance, features, security and investment.
PDF Image

Flyer: Layer2 Partner Program

Partner with us as a software distributer, reseller or SharePoint, Office 365 or Azure service provider. We are offering a free Partner Program with up to 30% Discount, free NFR licenses etc. Please download for more.
PDF Image

Layer2 Case Study: Cloud Connector helps ChemWerth to integrate the Microsoft Office 365 Cloud

"The Cloud Connector’s interface is easy to understand and use. Anyone familiar with utilities in the Microsoft stack will be productive immediately. Our recommendation: The Layer2 Cloud Connector is a no brainer.”
NoDoc Image

Layer2 Cloud Connector PAD File

This XML-based PAD file offers a Layer2 Cloud Connector product description for resellers to download and publish.
PDF Image

Office 365: How to sync with MS Dynamics CRM

You can sync any CRM data with native lists in SharePoint on-prem or online to make it available offline with Outlook.
PDF Image

Office 365: How to sync with SAP?

You can connect SharePoint and Office 365 with SAP via NetWeaver and OData.
PDF Image

Office 365: How to sync your local file share?

When you are moving to the cloud, there are a lot of reasons to keep your local file system. But you don't have to miss the benefits of the cloud.
PDF Image

Product Flyer: Cloud Connector for Microsoft SharePoint & Office 365

Flyer with features, benefits & pricing info for download as PDF.
PDF Image

Product Flyer: SharePoint Online Local File Server Sync

You can centrally migrate, synchronize, or backup your local file server department shares and home drives very flexible with SharePoint Online to close known gaps and overcome limitations of the Microsoft OneDrive for Business client.
PDF Image

Solutions Flyer: Data Integration with SharePoint, Office 365 and 100+

FileMaker can be integrated with 100+ other sources codeless using the Layer2 Cloud Connector.
Related Links

Layer2 Cloud Connector Community Group @ LinkedIn

​Are you already @ LinkedIn? Please join the Cloud Connector Community Group to share your experience.

Microsoft Partner Story Book for Office 365 about Cloud Connector

​Know more about a Layer2 Cloud Connector customer reference at the Microsoft Office web site.

Purchase Layer2 Cloud Connector via ComponentSource

​ComponentSource has offices in the USA, UK and Japan supporting over 125,000+ Customers, in over 180 Countries. They are also an approved Federal Government supplier through the GSA Schedule.



Sign Up for Layer2 Product Newsletter

We keep you up-to-date about new Layer2 product releases, added features and fixed issues.