SharePoint data integration: How to add specific business logic using Layer2 connectors

You can connect and sync almost any external data source codeless with SharePoint using the Layer2 Business Data List Connector or Layer2 Cloud Connector. But how to add specific business logic on both sides, SharePoint and / or the external system?

​While external data integration and synchronization of SharePoint lists can be done codeless in just minutes via Layer2 BDLC, it takes a little bit more time to add your specific business logic. Generally you can go two directions, in some cases both:

• Add SharePoint list workflows (declarative or coded with VSTS)
• Add events in the external system for insert, update or delete items, e.g. using SQL triggers

Add business logic using SharePoint list workflows

You can do almost everything by adding SharePoint workflows to a connected list. You can copy column content (or a part of column content) to other unmapped columns, or to other lists, if required. That could especially be helpful to manage lookups, user or group picker, modified or created columns and other more or less complex SharePoint fields. You can also keep existing foreign key table relationships in SQL for SharePoint. You will find a typical scenario here. 

Add business logic to SharePoint integration using SQL triggers

​Often SQL databases have the option to define triggers on insert, update or delete of records, e.g. for SQL Server, Oracle or MySQL. The following example shows how to post-process an already synchronized SharePoint lookup field to save the label only - and not the synced SharePoint content directly.

As you know, you can use lookups without any restrictions with Layer2 connectors. Let's demo this with a color lookup of a product list.

Fig.: A color lookup list with a label (title) and an ID (item ID) in SharePoint.

The lookup is used in a product list as follows.

Fig.: Product list that uses the lookup in a lookup column SPColor.

You can connect and sync the list with external SQL using the Layer2 Business Data List Connector for SharePoint or the Layer2 Cloud Connector for Office 365 including the lookup field without any issues. Let's assume the following table structure in SQL (taken from the Northwind database).

Fig.: Product table in SQL connected with SharePoint

Please note the fields SPColor and Color. While SPColor is mapped with SharePoint (using the Layer2 connectors), the Color field is not. To fill the Color field automatically, on insert and on update triggers are defined as follows.

Fig.: SQL On Update trigger to set the Color field with the SharePoint lookup label instead of the whole lookup string.

Finally we have got the results as expected shown below in the data query.

Fig.: Please note the typical SharePoint lookup token in the field SPColor while the label is stored automatically in the field Color.

How to add my specific business logic? Where to go next?

​No idea how to add your specific business logic to SharePoint sync? Just let us know! Also note our support offer on a per-hour base linked on the product pages.

READY TO GO NEXT STEPS?