How my external data source fields are mapped to my SharePoint list columns?

​The Layer2 Business Data List Connector for SharePoint (BDLC) is used to connect native SharePoint lists (no Web Parts) directly to external data sources.

The BDLC automatically creates the list columns required to host your external data source in a SharePoint list. The field names exposed by your select statement are used as column names automatically. But what about the field types and field names?

FIELD TYPES

• All text fields are mapped as Single Line of Text with max 155 character. Please change to Multiple Lines of Text if necessary.
• Any numbers, values etc. are mapped to numbers in SharePoint list columns. Please change formatting directly in SharePoint, if required.
• Any booleans are mapped to SharePoint true/false column types.
• Any Date/Time fields are mapped to SharePoint data/time column type. Please change formatting directly in SharePoint, if required.
• Any other field types are mapped to SharePoint text column types. Please change formatting directly in SharePoint, if required.
• Binary fields (BLOBS etc.) are not supported as this time. Text fields with a default string are created for this type of fields. 

FIELD NAMES

SharePoint list column names are automatically created based on data source field names.as delivered by the select statement. The SharePoint column display name is the same as the datasource field name.

Please use the select statement wit aliases directly to give certain columns names, that are different to the source fields, if required, e.g.

SELECT <field name> AS "<new field name>" ...

if your data source supports this type of statement. You can modify the display name of any SharePoint BDLC list column, if required after initial creation manually.

Please note, that special characters in names are processed specifically in SharePoint. There is a 32 character limit for SharePoint internal column names.

You can add additional columns later on after the structure is created (e.g. if the select statement or database structure is changed) using the field name conventions given above.

How to get the existing internal column names from SharePoint? You can do this by using the "Check Mapping" button.

TITLE COLUMN

You should use a single line text field for the Title column in the SharePoint list. If a field name Title is available in the result set, it will be mapped automatically. If the Title field is a primary key, take care about correct internal column name mapping. Don't use the column display name for that.
If no Title field is present, the primary key is used to map the Title column.

ID AND OTHER SYSTEM COLUMNS

Is is not possible to map the item id and other SharePoint system columns by design. Created, CreatedBy, Modified and ModifiedBy are supported since version 7.0.0.

COLUMN PROPERTIES

You can modify column properties after creation of the list structure. Please set single or multi-line text field properties, as required manually. SharePoint allows single line text fields up to 255 characters only.

When using multi-line text fields, do not use the "Enhanced Rich Text" Property, otherwise it results in continuous updates.

VIEWS

Please create SharePoint list views without any restrictions to

• format
• sort
• filter
• group

your data as required.

CALCULATED COLUMNS

You can use calculated columns without any restrictions.

CHECK MAPPING

Please use the "Check Mapping" button to check the field / column mapping. Every "?" on this screen can indicate a potential mapping problem. Make use of the "Custom Mapping" option in the same screen to map fields individually.

READY TO GO NEXT STEPS?