Create Lookup Site Column based on External List (SharePoint 2010)


  • I've got an External List and I want to create a Site Column, which is Lookup to a column in my external list. If I tick any of the columns in 'Add a column to show each of these additional fields' and click OK i get the following error message:

    [NullReferenceException: Object reference not set to an instance of an object.]
      Microsoft.SharePoint.SPBusinessDataField.IsInDefaultView() +58
      Microsoft.SharePoint.SPBusinessDataField.CreateSpFieldForBdcType(String bdcName, Boolean hidden, Boolean readOnly, Boolean idField, SPAddFieldOptions op) +809
      Microsoft.SharePoint.SPBusinessDataField.AddNeededSecondaryFields(String wssNames, String[] oldNames, String[] newNames, SPAddFieldOptions op) +315
      Microsoft.SharePoint.SPBusinessDataField.OnAdded(SPAddFieldOptions op) +240
      Microsoft.SharePoint.SPFieldCollection.AddFieldAsXmlInternal(String schemaXml, Boolean addToDefaultView, SPAddFieldOptions op, Boolean isMigration, Boolean fResetCTCol) +759
      Microsoft.SharePoint.SPFieldCollection.Add(SPField field) +61
      Microsoft.SharePoint.ApplicationPages.FieldNewPage.Save() +513
      Microsoft.SharePoint.ApplicationPages.FieldNewPage.BtnOk_Click(Object sender, EventArgs e) +46
      System.Web.UI.WebControls.Button.OnClick(EventArgs e) +114
      System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +139
      System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +28
      System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2980

    Is this a bug or (more likely) am I doing something wrong / missing the point?

    Also, does anyone know if there will be support for mutlivalue lookups to external lists?

    Tuesday, May 11, 2010 8:54 AM

All replies

  • When you are creating site column as look up from external list, you can choose extra columns but it won’t work. So don’t add extra columns and save it. You can add extra columns once you actually add this site column in your list.List lookup column can be created from external list with selecting extra columns as well. I also think it’s a bug.


    On selecting external list for lookup the option “Allow multiple values” gets disappeared. As per my knowledge multiple values for external list can’t be possible not at the moment. I think here we need this option. It is very likely business need for multiple values look up.

    MCPD (PRO: Designing and Developing Web-based Applications by Using the Microsoft® .NET Framework 2.0) MCTS (MOSS 2007 - Application Development) MyBlogs
    Tuesday, May 11, 2010 11:18 AM
  • Using an external list as a lookup for a site column doesn't work.  Even with the suggestion of not selecting extra columns.

    Robert Stark MCTS - MOSS 2007 MCTS - WSS 3.0 ---
    Friday, May 21, 2010 1:28 AM
  • It does work completely fine for me if I don’t chose the extra columns for lookup -external list. Make sure that your external entity is properly deployed and you have required permission on it.
    MCPD (PRO: Designing and Developing Web-based Applications by Using the Microsoft® .NET Framework 2.0) MCTS (MOSS 2007 - Application Development) MyBlogs
    Friday, May 21, 2010 10:31 AM
  • Can we confirm this functionality?

    Site Columns can be created from External Content Types but adding additional fields isn't supported?

    I have a Site Column containing a reference to an external list but when ever I try to add additional fields I get the same error that Andrew Cope posted.

    Monday, July 12, 2010 11:05 AM
  • I have the same issue.

    Creating a lookup site column from an external list works fine.  You just can't check off additional columns.

    Tuesday, September 21, 2010 6:53 PM
  • Hi! I´m havinge the same problem... does anyone find a solution? Thanks, Nelson
    Tuesday, October 26, 2010 4:01 PM
  • Hi,


    When I try to add the additional site column, the additional column is added to the list without any errors. There is a checkbox to add associated external list columns and even though it is selected, it doesn't show me those columns under Library Settings (I am working on a Document library - not a list). The funny thing is that when I go to create/modify a view, I am able to see those external list columns all there! 


    The main problem I am having is that I need to group-by one of those external list columns and they don't appear in the group-by drop down - sooo annoying :-{  I found a workaround on this forum for this - by using SPD to add the column via a definition but the issue there is that if anyone edit/modifies that view from the front-end, all my SPD changes are gone. 


    Any suggestions/recommendations for me?


    Saturday, November 06, 2010 7:16 AM
  • Hi ,  

      My requirement  says- I have one Content Type Defined -"Course Content Library" in my site. I need to add a site Column "CourseCategory" with field CourseCategory Name as a lookup in External List "Course Category" (ID,NAME ,Category are other fileds ..) so that whenever I ll add new Course Category Entry in the External List from SharePoint 2010 UI . The Existing Course Category should automatically populate(with mapping with "CourseCategory Name") from External List "Course Category" 



    The script below can be used to create a new site column. It assumes you are creating the column in the top-level

    site of a site collection. It can be used to create columns in a Content Type "Course Content Library",


                Here , The main scenerio

                Content Type-> "Course Content Library",

                Site Column -> {CourseCategory}



    #Script can also be used to create a site column in the Content Type Hub


    #Get site and web object

    $site = Get-SPSite -Identity "http://localhost:4444/"

    $web = $site.RootWeb


    #Assign fieldXML variable with XML string for site column


      #now adding Site Column CourseCategory

                            $fieldXML = '<Field Type="Lookup"  --à We can’t use Type=“External Data”


                            Group="HUB Columns"                    

                            DisplayName="Course Category"







        write-host $fieldXML



          $dtField = $web.Fields["Course Category"]




    write-host "Site Column CourseCategory is added ......."   


          #write-host "Please wait web/site is getting disposed now  ......."     





    Any Help Will be appreciated a lot . I am searching but not getting . As in the BLOG also given some information as

    Thanks in Advance.

    Friday, November 19, 2010 10:36 AM
  • I had the extact same problem.


    I fixed it by specifically entering in the "LOB Name" property on each Method to the name of the actual method. 


    Even though the List worked, the site column lookup would not.


    Ryan J
    Tuesday, December 21, 2010 10:33 PM
  • I'm having the same issue, any ideas on how to fix?

    Dean MCTS-SQL 2005 Business Intelligence
    Friday, March 04, 2011 9:27 PM
  • I had the extact same problem.


    I fixed it by specifically entering in the "LOB Name" property on each Method to the name of the actual method. 


    Even though the List worked, the site column lookup would not.


    Ryan J
    This is what worked for me. I still couldn't add additional fields when creating the lookup column, but the column was created and worked fine.
    Tuesday, April 12, 2011 8:28 PM
  • Same here


    Wednesday, January 16, 2013 6:34 PM
  • The Same for me!!


    Thursday, January 17, 2013 4:14 PM
  • I have experienced the same issue. 

    After using Reflector to inspect the code in SPBusinessDataField.IsInDefaultView, there was one line: 

    private bool IsInDefaultView() 

    return this.m_Fields.List.DefaultView.ViewFields.Exists(base.InternalName); 

    An lo and behold, the list on which I added the BCS field with additional columns, did NOT have a defaultview... The errorhandling by sharepoint could be quite a lot more gracefull, but what are you gonna do... 


    Edit list properties, create a new view (e.g. just id) and keep check mark in "create as new default view" (or something similar. Alternatively set one of the existing views to default view. 

    Monday, December 09, 2013 7:35 PM