Update an External Data field in SharePoint using PowerShell

Update an External Data field using PowerShell

The situation is as follows. I have created an external list (using BDC/BCS in SharePoint 2013).

My external list is called “External Users” and I have a normal custom list (Called “Custom List”) with an External Data (Called “External User”) field taking it’s data form the External List.

I now want fill in the External User field using PowerShell.

The Solution

After loading the SharePoint Snapin and reading the list items the tricky bits will start.

function Load_SPAddin()
{
$ver = $host | select version
if ($ver.Version.Major -gt 1) {$host.Runspace.ThreadOptions = “ReuseThread”}
if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue) -eq $null)
{
Add-PSSnapin “Microsoft.SharePoint.PowerShell”
}
}

$siteUrl = …
Load_SPAddin
$ctx = Get-SPServiceContext $siteUrl
$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx

$web = Get-SPWeb $siteUrl
$userList = $web.Lists[“External Users”]
$customList = $web.Lists[“Custom List”]

$eitems = $userList.GetItems()
foreach ($eitem in $eitems)
{
…
}

So now I’ve got $eitem containing the external item and we’re going to add a new item to the custom list and then we’ll fill the “External User” field.

In the External item there is a field User ID containing  a login of the user. This is what we want to appear in the Exteernal data field. Ok, this is easy:

$item = $customList.Items.Add();

$item[“Title”] = $eitem[“User ID”];
$item[“JDE User”] = $eitem[“User ID”];

But when I now look in the list item, the External user field is set but the external item hasn’t been connected. And also the other external data fields aren’t being displayed.

For each field in the external item there is a field I the custom list. So Where my field is called External User. There will be a field “External User: User ID”, “External User: Description” etc.

So first of all I’m getting all the fields in the External item.

$fieldnames = $eitem.Fields|Select Title

So first filling each  of the external fields displayed in the custom list:

foreach ($fieldname in $fieldnames)
{
$fn = $fieldname.Title;
if ($fn -ne “BDC Identity”)
{
$item[“External User: $fn”] = $eitem[$fn];
}
}

Still the data is all there now but the external item isn’t connected properly. When I edit the custom list item I’m still not seeing the external item. What did I miss?

There is one more field to update “ExternalUser_ID”. ExternalUser is here the name of the External content type. The value of this is something like __######## (two underscores followed by some numbers). So how can I make up the numbers? No need to the External item contains a field “BDC Identity” which matches this number. These numbers actually contain the External content type ID (a number starting with 001) and the item number of the external number.

$item[“ExternalUser_ID”] = $eitem[“BDC Identity”];

Oh, and finally don’t forget to update the item

$item.Update();