Wednesday, December 28, 2011

SharePoint 2010 BCS Field - Setting the field/column

I found where SharePoint keeps the bodies!

...well at least where it keeps the tombstones.

[In SharePoint and other programming worlds, the term "tombstone" or "headstone" often refers to a link, ID, or other mechanism or value that references some other value, much like a pointer in C/C++ terms.]

For BCS columns, I couldn't find a good article to automatically do the look-up and set the related fields of an External Content Type (ECT) field/column for SharePoint 2010 (pure 2010 mind you).  The code I COULD find was for SharePoint 2007, ported to 2010, but the objects and DLL's referenced in all of the examples were rendered OBSOLETE by Microsoft, so I couldn't compile against them.  This was a total bummer and a set-back, because those methods used to actually work (according to the comments people posted on those articles and blogs).

What I WAS able to find, was that, for MS SQL tables at least, there was a "__b" string that was stored in each item of a purely external list that referenced the external row.  You could see it as you hovered over the item in the list, the URL would contain a string at the end ..."&ID=__b"... blah blah blah.  That blah portion could be a complex number, or a dressed-up GUID, whatever the primary key identifier field was in SQL, but it ALWAYS started "__b" (again, this is for MS SQL, your mileage may vary).  I tested this value by creating a new item in a different list that had that ECT column, and when I did the look up against the value, it pulled back the record!!! EUREKA! 

So if I set the value for that field in code (ala "theItem["Field"] = stuff.ToString();") then it should work, right?  WRONG!  You'll see that the value might be set, or it might not be (random), and the value won't actually look itself up.  :(  Also if you run a workflow against the field and attempt to get anything like "[field: OtherTableRelatedField]" it will error out, telling you that the value isn't in the expected format, which will halt a workflow at "Error Occurred".  The look-up works in the UI in the Edit or New form with no problems, but not from code, be it client object model, server object model, or PowerShell.

HOWEVER!  Afterwards, you can do a refresh using the little icon next to the ECT column title in a view through the GUI to force it to refresh against the external content, and it will parse the entire list and update any rows that are out of date, or that contain a tombstone ("__b") value.  AND THEN IT WILL DO THE LOOK-UP!  Afterwards, your workflows and other code will operate just fine; the related fields will have the appropriate data in them.

I have found NO WAY to automatically or programmatically kick off that refresh so that the field will be set correctly at the time that I set it.  :(  So... this is basically a hack, and an 80% solution. 

Also, I should point out, that tombstone value that points to the DB row (that "__b" value) is stored in a RELATED field to the ECT column, in a Multiple Lines of Text (Notes) type field!  This is not easy to find when debugging, so it took me the better part of a day!  I also wasn't aware of that "__b" value even existed until a coworker of mine pointed it out to me (who isn't even a programmer, but works with workflows all the time, Thanks Jason!).

Here's how you get that value in code:

SPFieldMultiLineText notes = theItem.Fields.GetField(
theItem.Fields["ECTField"].RelatedField) 
as SPFieldMultiLineText; // notice the nesting?
 
string tombstone = notes.GetFieldValueAsText(
theItem[theItem.Fields["ECTField"].RelatedField]);  // more nesting

This will provide the value.  Then to stuff it back in to a new item that I had created moments before (in this case, a document set), I had to do this, with AllowUnsafeUpdates set to true around the block of code:

SPBusinessDataField dataField = theItem.Fields["ECTFieldName"] as SPBusinessDataField; // external data column

theWeb.AllowUnsafeUpdates = true;
dataField.ParseAndSetValue(theItem, tombstone);
theItem[dataField.RelatedField] = theItem["ECTFieldName"];
theItem.UpdateOverwriteVersion(); 
// if you do theItem.SystemUpdate() instead, 
//   workflows won't kick off
theList.Update(true);  // not sure if this is needed?
theWeb.AllowUnsafeUpdates = false;

This will literally set the field value to that "__b" string, at which point, you must do a refresh on the column through the UI (which does EVERY ITEM IN THE LIST!) in order for SharePoint to replace the tombstone with actual data from the external system.

If someone can get me the rest of the way with this, I would GREATLY appreciate it, but at least this "hack" might be useful to some other developer.

Happy Hacking, and Merry Coding!