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.