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
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
Fig.: SQL On Update trigger to set
the Color field with the SharePoint lookup label instead of the whole lookup
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.