Set SharePoint BDC field value programmatically in Visual Studio workflow
The Business Data Catalog (BDC) in SharePoint is a superb way of hooking up virtually any disparate system to SharePoint 2007. By defining the connection in to the system, you can expose any part of the system to SharePoint, and harness some of SharePoint’s real power - for example, its powerful search capabilities. Once you’ve created your connection (your Line Of Business [LOB] system), you can attach BDC columns to any list (e.g., document library) and you can, for example, have a document with a reference to your other system, so that you can permanently associate extra data to your document, without having to reproduce the information in SharePoint and create unnecessary duplication.
Using the BDC is beyond the scope of this post, but I’d like to cover an issue that I recently encountered when trying to work with the BDC and a document library. I had a fairly simple Visual Studio 2008 workflow that ran when a new item was created in the document library.
Here’s the scenario:
There’s abacklog of paper documents of varying types, that are to be scanned in bulk and then imported in to SharePoint. Each document can be linked to a particular record* in the BDC. The scanning process identifies the type of document as well the reference used to link the document to the record in the BDC. It constructs a filename for each document on the basis of this info. When the item is created in the document library, a Visual Studio workflow starts and is able to deconstruct the filename and retrieve the content type and reference, and set these properties automatically.
I’m just going to cover the two bits of code to update the content type and the BDC value programmatically in your workflow. It’s in Visual Basic - refactoring to C# is trivial if that floats your boat.
The (simplified) code required to set the content type (this assumes you’re in a workflow, where you’re dealing with an item that is passed in):
[code lang=”vb”]
/* the string name of your content type */
Dim contType As String = “My Content Type”
Dim newCT As SPContentType = Nothing
Try
/* This tries to get the actual content type from the list of Site content types to check it exists. When we actually set the content type, we actually just use the string name of it */
newCT = workflowProperties.Web.ContentTypes.Item(contType)
If Not IsNothing(newCT) Then
workflowProperties.Item(“Content Type”) = contType
workflowProperties.Item.Update()
End If
Catch ex As Exception
SetStatus(“Exception trying to update content type: ” & ex.Message, StatusType.Error)
Exit Sub
End Try
End If
[/code]
Hopefully it’s fairly easy to follow. Setting the value of the BDC field, though, is more complicated. Unfortunately, you can’t just do a simple
[code lang=”vb”]
workflowProperties.Item(“Column Name”) = newValue
[/code]
I did a lot of digging, and on the basis of the information in this post, this post and this post, and there’s actually quite a few ways to skin this particular cat. I ended up with the following:
[code lang=”vb”]
Dim mREFIELDNAME as string = “YOUR_COLUMN”
Dim ordRef as string = “THE_NEW_VALUE”
Try
Dim theField As SPField = workflowProperties.Item.Fields.GetField(mREFIELDNAME)
Dim theXMLDoc = New xmlDocument
theXMLDoc.LoadXml(theField.SchemaXml)
Dim entityName As String = theXMLDoc.FirstChild.Attributes(“RelatedFieldWssStaticName”).Value
workflowProperties.Item(entityName) = EntityInstanceIdEncoder.EncodeEntityInstanceId(New Object() {ordRef})
workflowProperties.Item(mREFIELDNAME) = ordRef
Catch ex As Exception
SetStatus(“Exception trying to set BDC ” & mREFIELDNAME & “: ” & ex.Message, StatusType.Error)
Exit Sub
End Try
[/code]
In the above, “SetStatus” is just a simple error handler that sets the status of the workflow so we can check the result (and any error messages) when it finishes. Notice that I don’t explicitly verify that the field we’re updating is an actual Business Data field as some of the other posts say is possible. If you want to do that, you’ll need a reference to microsoft.sharepoint.portal 12.0.0.0 (by default in C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions12ISAPI.) You can then import the Microsoft.SharePoint.WebControls namespace, which gives you the BusinessData objects. I haven’t verified this, but it seems that the name of the column has to be the same as the entity in the application definition file. Also note that if in a view on your document library, you’re displaying other information from the BDC on the value you’ve just set, that data isn’t attached to the list item until you click the little refresh button. Be warned, though, that this refreshes all list items and this will produce a hit on your BDC system. It is, of course, possible to refresh BDC data programmatically.
- I use “record” to generically define something in the LOB system. It could be anything, e.g., a product, or a purchase order, or an invoice…