Layer2 Cloud Connector Dynamic Columns Feature - How does it work

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.

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.  
  

READY TO GO NEXT STEPS?