I’ve been working with a client recently on a project where we are using a BDC connection.  Things have been going fine when we were using the external item picker in an InfoPath form but we ran into a problem.  We are using an external column in the same library.  Due to issues I will explain, I need to set this external column with PowerShell.

I’ve seen a lot of online folks have trouble with this, so I thought that I would share how to properly set this column with PowerShell as there is a little trick to it. 

I’ll give you some context, but if you just want the script, scroll down to it. 

A Little Backstory

We are implementing an Invoice and Check Request process for the the Finance department.  The invoices come in as PDFs in a document library as one content type, and the check requests are InfoPath forms as a different content type in the same library.  They share most columns. 

In the form there is an external item picker, and there is a corresponding external column in the library.  Ideally, we could promote the value from the external picker from the form to the external column in the library.  Well, you cant!  It’s just not possible.  Hence our problem.  I needed a way to get this list column updated with the value from the form so there is ONE column with the information. 

My Solution

To accomplish this, we need to do two things:

1. Promote the value from the external item picker to a text column in the library.
2. Use PowerShell to copy the value from this text column to the external list column

The purpose of this blog is to share #2, not #1. 

What Didn’t Work

Your first instinct will be to craft a workflow where you just copy the value from the text field to the external field.  SharePoint Designer will let you do this, however when you run your workflow, nothing will happen. Rather, your external column will STILL be blank. 

Ok, next you try the same thing with PowerShell – same results.  You might even see where the value does show on the view properties, but when you click edit, it’s blank. 

What Does Work – The Script

The reason it didn’t work before is because there is a hidden column that goes with the visible one that needs to be set too. 

The column name is the name of the BDC entity _ID. For example, check your BDC external content type (visible in SharePoint Designer):

The hidden column we need to set will be called GreatPlains_ID.  You can also get this information from PowerShell via either the SchemaXML or the RelatedField property, or with SharePoint Manager. 

First we get the item, then get the external field as as  BCS field that is named “Vendor Name”, then we can get the related field:

#Connect to the list and item
$web = get-spweb "http://sharepoint/finance"
$list = $web.lists["Invoices"]
$item = $list.getitembyid(50)

#Get the BCS column as a BCS field for the item
$vendorname = $item.fields | ? {$_.Title -eq "Vendor Name"} 
#Get the related ID field name
$vendorBCSIDfield = $vendorname.relatedfield

This wouldn’t work if you just get the field like $item[“Vendor Name”].  It is just a string field, and would not have this information.  You have to get it like the above method.  In the SchemaXML, it listed as RelatedFieldWssStaticName, and the Entity is the external content type:

The problem is what do we use to set the hidden ID field?  This value is an encoded value.  To set the hidden field GreatPlains_ID, we need to get the unique identifier field from the external content type and the text value.  We can publish these values from the InfoPath form but for our script purposes, I’ll just specify them in the script.  We need to take that text value and encode it.  Let’s look at what this looks like if we set it via the interface:

The value is like:

__bk8100140025007400030003001300

Side note – we can decode this value using the DecodeEntityInstanceID method:

$BCSID = $item[$vendorname.relatedfield].tostring()
$value = [Microsoft.SharePoint.BusinessData.Infrastructure.EntityInstanceIdEncoder]::DecodeEntityInstanceId($BCSID)

Now that is all out of the way, we can set our external field after encoding the hidden field value using the EncodeEntityInstanceID method:

#Test Values representing the text values from the InfoPath form
$vendorIDtxt = "C5001"
$vendornametxt = "C5 INSIGHT, INC."
#Encode the ID txt value 
$newBCSID =  [Microsoft.SharePoint.BusinessData.Infrastructure.EntityInstanceIdEncoder]::EncodeEntityInstanceId($vendorIDtxt)

#Update the external column
$item[$vendorname.title] = $vendornametxt
$item[$vendorBCSIDfield] = $newBCSID
$item.update()

Done!  I will add that in my testing, I’ve found that as long as the hidden ID field is set to SOMETHING (even just like 12345abc), the next time you edit the properties and save in the browser, it will update the hidden field with the right value.  To take this further in a more real world situation, you would either need to expand the script then set it to run as a scheduled task or use a SharePoint workflow utilizing a custom workflow action that could execute a PowerShell script (HarePoint, codeplex, etc.). 

Please contact us if you have any questions on this blog or anything else!