How to pull external data in a SharePoint User/Group list column with BDLC?

The SharePoint Business Data List Connector is used to directly connect a native SharePoint list to almost any external LOB data, e.g. from databases, CRM or ERP systems.

Columns of type "Person or Group" allow you to show existing user or group in a SharePoint list and are used to:

• Render a user name with presence information, ender a user name with presence information,
• Filter the list content for this user or group - for example, for ownership - or
• Use the people picker to select users or groups. se the people picker to select users or groups

Generally, you can push data into columns of this type using the SharePoint Business Data List Connector (BDLC).

Fig. The column User0 is of type text, the person column User1 holds the correct SharePoint users.

To properly update the SharePoint list with this type of data, you must create the "Person or Group" type column manually after creating the SharePoint list structure automatically by the BDLC. The BDLC cannot automatically create columns of this type, and it will create a text field to hold the data instead. You wouldn't be able to convert the 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.

To automatically map the column you created to the external data in your SQL select statement, please use the same column name for both fields in both locations. For example, if you have a field "MyUserColumn" in your SQL result set, create a SharePoint column "MyUserColumn" to map to it. Or use the custom mapping feature of BDLC and map the new columns individually.

To display a field named "MyUserColumn" in your SQL result set without actually changing the SQL column, you can use the "Select XYZ as MyUserColumn from MyTableOrView" SQL select clause to assign it an alias to make the SharePoint column name.

The last step is making sure the data in the Source is formatted correctly so that SharePoint can interpret it. The column values of this field are saved as lookup fields in SharePoint with the notation of:

• ID;#Value

The ";#" part is used as separator. The ID is the ID value of the user information list, local to the current site collection.

For example:

• 11;#Administrator
• 123;#mydomain\myuser

You can build the token dynamically in your SQL as expression, e.g. ... ID + ";#" + userName ...
However, if you have a lot of users, building this out can be tedious!

There's an easy way around this though, because SharePoint can do this lookup for you. Simply set -1 as ID to retrieve the current IDs automatically!

For example:

• -1;#mydomain\myuser
• -1;#myuser
• -1;#i.0#.w|mydomain\myuser

In this way you can easily fill a Person / Group field with external user information using BDLC.
This information can be used in may ways, such as to filter data displayed to the user.

**Known Issues - Repeating Updates**

The format used by SharePoint may be different depending on if your system uses claims authentication or not. The way the accounts are stored by SharePoint can even be mixed in the same site. If you use the wrong format, the BDLC may compare that to the slightly different value stored by SharePoint and assume it is a change, leading to repeating updates. If you get this behavior, try a different format above to see if that clears the issue. If you aren't sure which format you need to use, you can set up a test SharePoint list with a Person/Group field, and use the BDLC write-back feature to push the user values to an empty table, which will show the true format SharePoint is using to store that account. You can also switch to using the Id;#Value method as the ID will make sure it always matches the right user.

You can download the BDLC Shareware version for FREE here.

READY TO GO NEXT STEPS?