SharePoint and Business Data Columns

Problem

I've been struggling for a while copying data from one SharePoint List to another where one of the fields links to Business Data Connectivity (BCS) data. The copy always works with no errors, but the recipient BCS field remained blank.

Solution

If you tell SharePoint Designer (SPD) to populate the BCS data column in List B from the BCS data column in List B it simply won't work. (It does actually copy a name in the field but the link to the BCS data is not actually created). This is because it also needs to have an ID entered in the [yourBCSdataname]_id field.

Annoyingly SPD will let you read from this id field but it won't let you write to it.

My workaround therefore was to write the id field from List A into a temporary field in List B. To make things a bit easier for me I also copied the display name of the BCS item to a temporary field in List B. Upon item creation in List B I then fired a visual studio designed workflow to copy the data from the temporary field into the id field in List B. The code was relatively simple:

SPListItem item = workflowProperties.Item;
item["MyBCSData_ID"] = item["tempid"].ToString();
item["MyBCSDataField"] = item["tempname"].ToString();
item.Update();

Note: If you have any additional BCS fields displayed these won't automatically be populated. You will need to refresh the BCS data for this to happen. There is code out there to do that, but it wasn't an issue for me I was purely concerned with getting the link created on that one field.

See my post back on the original Microsoft Forum HERE