SharePoint 2013 – Update an External Data field using PowerShell

Update an External Data field using PowerShell

The Situation

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();
Advertisements

7 thoughts on “SharePoint 2013 – Update an External Data field using PowerShell

  1. Brilliant Work, I spent days searching for this.
    I thought SP automatically grabs all the additional fields once you connect one field.

    Like

  2. Do you know how a external datavalue that we brought in can sync with external list.
    When external list is updated, the value remains same in custom list.

    Like

    1. Are you looking for a manual way of doing this? A programmatic way or a automated way.

      Manual: there is an option in the interface to refresh the field.
      Programmatic: In a PowerShell script you could update the external fields (They are just like any other field with the format “ExternalFieldName: Fieldname”. So something like $item[“ExternalFieldName: Fieldname”] would do.

      Automatic: run the above PowerShell as a scheduled task.

      I haven’t found a setting anywhere runs the refresh automatically.

      Like

  3. Thanks Pieter, The manual way “Refresh External Data” icon, is what I like, It has an ID associated with it.
    Which goes to BusinessDataSynchronizer.aspx page.

    It would be nicer If there is powershell just to trigger that refresh, rather than bringing all the value.

    Thanks for explanation,

    Like

  4. Is there a way to delete external list items through PowerShell? I’ve done extensive research and can’t seem to find a way to do so. I was able to move items from a native list to an external and update items as well. When I try to delete I am not able to.

    Like

Please leave a comment or feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.