PortiBlog

Setting an BDC external list field with PowerShell and retrieving the bcdid

4 september 2014

In a migration scenario I needed to have the value of an external bdc field (from SQL server) set from a text field.
Since the migration software failed at this point I started using PowerShell to do this.
It’s not very difficult to set a field in a normal list but when working with external lists you first need to get the Service Context by using ‘Get-SPServiceContext’.


$ctx = Get-SPServiceContext http://domainx.nl
$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx

While working on the script I think I ran into the same problem the migration software did.
Even when the Service Context is set you can set the external field simply by setting $listitem["Contract Party B"]

If the field is set this way then you can see the update in the list, but when editing the item this value is not retrieved and shows up empty.
This is because each external field has a second field _ID
The field needs to contain the bdcid, which is the encoded value used in the profile page of your external content type.

So if you look at the URL of your external content type http://domainx.nl:80/Businessprofiles/_bdc/http___domainx.nl/product.aspx?ProductNr={0} you need to set this field to the product number field.
To encode this field you need to use, I used a console application because I could not find a way to call EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { “ ” }) in PowerShell.

The console application:


using System;
using System.Web;
using Microsoft.SharePoint;
using Microsoft.Office.Server.ApplicationRegistry.Administration;
using Microsoft.SharePoint.BusinessData.Infrastructure;

namespace Microsoft.SDK.SharePoint.Samples.Bdc.ExternalList
{
    class Program
    {
        static void Main(string[] args)
        {
            var input = args[0];
            var encoded = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { input });

            Console.WriteLine(encoded);
        }
    }
}

 

If the input is 809159460 then the returned encoded value is __bk4200830003009300130053009300430063000300
The PowerShell script that calls the console application:


Add-PsSnapin Microsoft.SharePoint.PowerShell

#variables
$site = "http://domainx"
$exList = "ExternalDocuments"

#set service context
$ctx = Get-SPServiceContext http://domainx.nl
$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx
$web = get-spweb $site
$elist = $web.Lists[$exList]

#iterating thru external list items
foreach ($elistitem in $elist.Items)
    {
              #calling console application to generate the encoded profile id.
              $code = & D:\Temp\GetBcdEntityId.exe $row.productnr
              $elistitem["ExProductField"] = $elistitem["MigratedTextField"]
              $elistitem["ExProductField_ID"] = $code
              $elistitem.Update()

              write-host "Updated ExProductField with: " $elistitem["MigratedTextField "] "and profile ID: " $code
    }

 

Submit a comment