Sunday 22 August 2010

How to create a BCS lookup field in a content type

Hellloooooo fellow frustrated SharePoint devs!
(Well I assume you are, otherwise you wouldn't be interested in this ...)

I'm currently working on a little project/proof-of-concept which involves heavy usage of the new SharePoint 2010 BCS functionalities. I've already come across a few bumps in the road for which I couldn't immediately find some useful info, so I guess this will be the first of many posts on this subject.

Today I'll start with the following issue: how to use a BCS lookup field in a content type.
Normally this shouldn't be to hard to do:
  1. Create a site column "A"
  2. Create a content type (on site collection level) "CT"
  3. Add a reference to column "A" in content type "CT"
However, no matter how hard I looked, I just couldn't find an option to create a BCS lookup field as site column. Fyi: a BCS lookup field is called External Data field in the UI.
Sure, you can easily create such field on a list level, but not on a site level.


Adding a list BCS lookup. Notice the External Data type


Trying to add a site BCS lookup. No external data type here ... :(



So after googling around without any result, I found it was time to fumble around with my trusty Powershell and see how these list fields are defined.
Apparently the field's SchemaXml is:
<field
type="BusinessData"
displayname="bcs_field"
required="FALSE"
enforceuniquevalues="FALSE"
id="{GUID}"
sourceid="{GUID}"
staticname="bcs_field"
baserenderingtype="Text"
name="bcs_field"
colname="nvarchar11"
rowordinal="0"
version="3"
group=""
systeminstance="SPSDEV1"
entitynamespace="SPSDEV1.BCS"
entityname="dbo_Persons"
bdcfield="Names"
profile=""
hasactions="True"
addfieldoption="AddToAllContentTypes, AddFieldToDefaultView">
</field>
Notable properties are:
  • SystemInstanceName: this is the BCS instance name.
    You can find this value via Central Admin > Application Management > Manage Service Applications > Business Data Connectivity Services. Then select External Systems and select the system's name. This should result in an URL like http://centraladmin/_admin/BDC/ViewBDCLobSystemInstances.aspx?AppId=...

  • EntityNamespace: BCS entity's namespace
  • EntityName: BCS entity you're referencing to
  • BdcFieldName: the BCS field you want to display in the column
Those last three properties' values can be found in the External Content Types overview of Central Admin > Application Management > Manage Service Applications > Business Data Connectivity Services





Armed with this info, I cooked up this piece of code which will create a BCS lookup site column. Of course, you can then reuse this column in any content type.
private string CreateBCSLookupField(SPWeb targetWeb,
string lookupFieldName, string groupName,
string systemInstanceName, string entityNamespace,
string entityName, string entityFieldName, bool hasActions)
{
SPBusinessDataField lookupField =
targetWeb.Fields.CreateNewField("BusinessData", lookupFieldName) as SPBusinessDataField;
lookupField.Group = groupName;
lookupField.SystemInstanceName = systemInstanceName;
lookupField.EntityNamespace = entityNamespace;
lookupField.EntityName = entityName;
lookupField.HasActions = hasActions;
lookupField.BdcFieldName = entityFieldName;
return targetWeb.Fields.Add(lookupField);
} 

*UPDATE*
Okay, apparently there's still something fishy going on behind the scene. I've been using this code for a while now and it seems there's an inconsistency when you create multiple BCS site columns in a row.
Lets say you create 3 columns (A, B, C) with this code.
  • First test:
    A was ok, B too, but C resulted in an error about an improperly configured lookup.
  • Second test (same code, same setup):
    A was ok, B impropertly configged and C was ok
  • Third test (again same setup):
    A failed, B was ok and C failed too
To be honest, I really don't know why it sometimes fails.  It's the same frikking code, but somehow SharePoint sometimes decides to funk things up.
Funking things up apparently comes down to bad property values.  I compared the schema xml's of properly created BCS fields and those that weren't: the correct fields had the same XML as I posted before, while the erronous fields had this xml
<Field  
Type="BusinessData"  
DisplayName="bcs_field"  
Required="FALSE"  
EnforceUniqueValues="FALSE"  
Group="BCS Test"  
ID="{GUID}"  
SourceID="{GUID}"  
StaticName="bcs_field"  
Name="bcs_field"  
BaseRenderingType="Text"  
ColName="nvarchar11"  
RowOrdinal="0" />
As you can see, this xml doesn't have the BCS related properties ... but why?!
Currently I only have one "workaround":
  1. Create BCS field
  2. Check if the resulting fields schema xml contains a reference to SystemInstance (or another BCS related property)
  3. If it doesn't, remove the field and try again.  Usually it'll then be created properly
  4. Rince and repeat if it still doesn't have a correct schema xml (of course, limit the number of repeats to prevent endless loops)
I know, it's definately not kosher ... but I just don't have a clue at the moment on what's causing these missing properties.
Any ideas are welcome :)

5 comments:

  1. hey.. how do we add a picture column to an external list?

    ReplyDelete
  2. A while loop in an event receiver to keep pumping the same field into SharePoint every time it funks up - classic! I hate that SharePoint drives us to take these measures!!!

    ReplyDelete
  3. Have you been able to set secondary fields using this method? When I use SPBusinessDataField.SetSecondaryFieldsNames I get a null pointer exception. It also seems I am not able to set them using the gui afterwards either.

    ReplyDelete
  4. I tried your code above, when I went back into the Site Columns configuration area for the site, I could see that the configuration didn't save properly.
    So, then I used SharePoint Manager 2010 (see codeplex) and grabbed the Schema XML for a external lookup column created through the SharePoint gui.
    Using an empty SharePoint element, I created the lookup column using CAML and was able to deploy it correctly.

    One serious gotcha in attempting to deploy that CAML (elements.xml) xml was that the schema definition for the attributes was not validating (reported by Visual Studio 2010 when I attempted to deploy the CAML).

    So, to resolve that issue, I located another post at http://social.msdn.microsoft.com/Forums/en-US/sharepointgeneralprevious/thread/249ca2b3-491a-496a-89b9-7c3b83236d54

    It talked about adding entries to the wss.xsd file. Once those entries were added, the CAML would deploy.

    I had to add the following lines to the c:\Program Files\Common Files\Microsoft Shared\web server extensions\14\TEMPLATE\XML\wss.xsd file to get this to work.
    Locate the following line and add these new attributes after it if they don't exist already.

















    The final elements.xml looked as follows:




    ReplyDelete
  5. Sorry... not sure why the elements.xml info didn't paste...
    Here you go.


    <
    Field Type="BusinessData" DisplayName="Customer Lookup" Required="FALSE" EnforceUniqueValues="FALSE" Group="BCT BCS Lookup" ID="{5E3CF399-930E-45C4-BC23-9C483526087B}" StaticName="_Customer_Lookup" Name="_Customer_Lookup" SystemInstance="BCT_BCSInstance" EntityNamespace="BCT_BCS" EntityName="Customer" BdcField="sName" Profile=""
    HasActions="True" SecondaryFieldBdcNames="0" RelatedField="Customer_ID1" SecondaryFieldWssNames="0" RelatedFieldBDCField="" RelatedFieldWssStaticName="Customer_ID1" SecondaryFieldsWssStaticNames="0" AddFieldOption="AddFieldInternalNameHint"
    /
    >

    ReplyDelete