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
- Filter the list content for this user or group - for example, for ownership
- 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:
The ";#" part is used as separator. The ID is the ID value of the user
information list, local to the current site collection.
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
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.