Columns of type "Person or Group" are often used in SharePoint lists to reference to an existing user or group, e.g.
- to render a user name with presence information,
- to filter the list content for this user or group - for example for ownership - or
- to use the people picker to select users or groups.
Generally you can pull data into columns of this type using the SharePoint Business Data List Connector (BDLC).
Fig.: Sample of a SharePoint BDLC list with person / group column type (named bdlcPerson)
You cannot automatically create columns of this type using the BDLC. The BDLC will create multiline text fields automatically in this case. You can't convert this automatically created field into a person / group field. It's a known limitation of SharePoint, that you cannot modify any existing column type after creation to the "Person or Group" type field.
So you have to create the "Person or Group" type column manually after creating the SharePoint list structure automatically by the BDLC. To map this column to external data in your SQL select statement please use the naming conventions given with BDLC, e.g. if you have a field "MyUser" in your SQL result set, create a column "MyUser" to map it automatically. Please note, that "MyUser" is the internal (!) column name in this sample, given on creation You can rename this column after creation to any column title you want. You can't modify the internal column name in SharePoint in any way (just delete and re-create).
To display an field named "User" in your SQL result set, you can use the "Select XYZ as User from MyTableOrView" SQL select clause.
The most interesting question is about the content of your "User" field in the SQL result set to display the user name in the person / group column.
The column values of this field are saved as lookup fields in SharePoint with the notation of:
The ";#" part is used as separator. The ID is the ID value of the user information list, local to the current site collection.
Samples:
- 11;#Administrator
- 123;#mydomain\myuser
You can build the token dynamically in your SQL as expression, e.g. ... ID + ";#" + userName ...
But how to know about the certain IDs in your external data base?
It's easy, because SharePoint can do this for you. Simply set -1 as ID to retrieve the current IDs automatically!
Samples:
- -1;#mydomain\myuser
- -1;#myuser
In this way you can easily fill a person / group fields with external user information using BDLC.
This information can be used in may ways, e.g. to filter data displayed to the user.
You can download the BDLC Shareware version for FREE here:
http://www.layer2.de/en/products/Pages/SharePoint-Business-Data-List-Connector.aspx