How to push database field content to SharePoint metadata columns (and write back)?
The Cloud Connector for Microsoft Office 365 and
SharePoint connects almost any on-premise data source, e.g. ODBC,
OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), SQL databases
like SQL Server, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes,
SharePoint, Exchange, Active Directory, Navision, SAP and many more directly to
native SharePoint lists in the Microsoft Cloud - or any other Microsoft
SharePoint installation - in just minutes without any programming. For any
questions please use the FAQ Web. Please contact [email protected] directly for more.
If you want to connect a native SharePoint list with a database source you
possibly want to have your metadata information, e.g. creation and last modified
date and time, author and editor names in SharePoint as well. This article
explains this step-by-step.
Assuming you have a database table like that.
Fig.: Database table with metadata
to map to SharePoint.
You can map the database fields to SharePoint internal columns in the Cloud
Connector as follows.
Fig.: Field - Column mapping sample
Please note the SharePoint side. You have to use these field names to write
metadata to SharePoint. See the SharePoint list below for results:
Fig.: Sample list with metadata from
database included as SharePoint metadata column.
This looks pretty simple, but need some pre-considerations to work.
- Modified / Created needs to be of
- Created By and Modified By must contain a valid user token in the target
system. For example for Office 365 this can be
- If you have the value myUser only in the database field user (e.g. from
current user AD account) you can easily build the token in SQL, e.g.
*, 'i:0#.f|membership|' + user + '@mydomain.onmicrosoft.com' as SPuser from
But be careful with bi-directional connections. It is better to keep
the original field content in this case and build the token or user using
triggers on database side.
- This also will work with person / group fields on SharePoint side.