none
Content Type or Lists Lookup Column can't show additional fields which are Choice or lookup columns

    Question

  • I am trying to create some content types, but this also happens when creating new columns in a list.

    Ok, here is my setup (which is the most basic way to replicate the issue):

    List 1: Department List
    Columns: Department Name (Single Line of Text)

    List 2: Document Owners
    Columns: Document Owner (single line of text), Department (Choice or Lookup from Department List), Email (single line of text), Lookup Field (Calculated).

    Content Type: Quality Document
    Columns: File Name, Title, Document Owners (Lookup from Document Owners List), Email (pulled in with Document Owner), Then i want to also pull in Department from the Document Owners list.

    That is where my problem is.  When i select the Document Owners List as the list to get the information from, there isn't the option under "Add a column to show each of these additional fields" to pull in the Department column.  This occurs if i use a Choice in the Document Owners list, or if i do a lookup from the Department List.

    My preferred method of implementing this system would be to have a multi-value lookup field in the Department List for Document Owners so that the Owners are attached to the Department, and if the owners change for the department they also change for the document that is for said department.  But, i run into the same issue that i can't pull those columns from that list either into the content type.

    Is this a bug or is this by design that you can't pull in columns via a lookup into another list or content type if they were a choice column or pulled in via a lookup in the source list?

    Any help would be appreciated, even other possible solutions.  Let me know if you have any questions.  Thanks.

    Adam

    Tuesday, June 08, 2010 5:49 PM

All replies

  • It should by design. Please read what follows from the attached document (http://sharepoint.microsoft.com/blogs/GetThePoint/Blog%20Documents/Create%20list%20relationships%20by%20using%20lookup%20and%20unique%20columns.zip ) in http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=316 :

     

    Creating lookup columns

    To create a relationship between two lists, in the source list, you create a lookup column that retrieves (or "looks up") one or more values from a target list if those values match the value in the lookup column in the source list. Once you create this lookup column (the primary column), you can continue to add additional columns (secondary columns) from the target list to the source list, as long as those additional columns have a supported data type described below.

    The following table summarizes what column types can and cannot be used to create lookup columns.

    Supported Column Types

    Unsupported Column Types

    Single line of text

    Currency

    Multiple lines of text

    Lookup

    Choice

    Person or Group

    Number

    Calculated

     Date and Time

    Hyperlink or Picture

    Yes/No

    Custom Columns

    • Proposed as answer by Pedro Gordo Friday, July 06, 2012 2:37 PM
    • Unproposed as answer by Pedro Gordo Friday, July 06, 2012 2:37 PM
    Tuesday, June 15, 2010 3:19 AM
    Moderator
  • That just makes everything a lot more difficult to configure in a logical and effective way.  If you can't link multiple lists together like that, how can you make information dynamically update in the situation that something changes.  e.g. in my case, if someone moves to a different department or leaves the company, we don't want that document to be still linked to that user account, we want to change all documents linked to that department or person to the new owners in that department.

    Let me know if you have any suggestions for situations such as these.  Thanks.

    Friday, June 18, 2010 9:51 PM
  • Are you sure that  choice columns are supported beacuse I can´t get it working with them as "additional columns".
    Tuesday, October 19, 2010 2:45 PM
  • It's a pain, since this isn't the only context in which you are restricted from using column types in ways that ought to be allowed.  One of the chief ways to deal with situations like this is to create a hidden text column, along with a workflow or an event receiver to populate it.
    Tuesday, October 19, 2010 3:46 PM
  • This is a known issue - see my thread here: http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/5a80988a-6b37-4454-957b-856ea16b2e60

    You should be able to add a choice column as an "additional column" to a Lookup Column, but it does not work in the RTM version of SP2010. It worked in the beta. I am trying to find out which column types are officially supported as "additional columns" for a Lookup Column, but have not yet got an answer.

    Someone provided a list of the column types that can be used as Lookup columns here:  http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/726d13b7-5dc1-4880-ab06-e9c4cb61ebc2

    But I dont' know if the column types that can be used as "additional columns" is the same.

    Tuesday, October 19, 2010 5:55 PM
  • Some digging with Reflector yielded the following in LookupFieldEditor.PopulateShowFieldDropDown.  This method is used to populate both the additional column list and the lookup field dropdown:

        if (((((field.Type == SPFieldType.Counter) || (field.Type == SPFieldType.Text)) || ((field.Type == SPFieldType.Number) || (field.Type == SPFieldType.DateTime))) || ((field.Type == SPFieldType.Computed) && ((SPFieldComputed) field).EnableLookup)) || ((field.Type == SPFieldType.Calculated) && (((SPFieldCalculated) field).OutputType == SPFieldType.Text)))
        {
          AddFieldToDropDown(showField, field, strSelectedField, null);
          continue;
        }
    
    

    Of the field types that are exposed in the UI, then, the supported ones appear to be:

    Text
    Number
    DateTime
    Calculated (only with output type Text)

    This differs from the list given in the link, but matches my experience.  For example, calculated columns with output type Text are available to me, whereas the link says they are unsupported.

    Note that the above code is not complete; there is some other logic in that method involving edge conditions.  But the line I picked out should be relevant for the vast majority of cases.

    Wednesday, October 20, 2010 1:51 PM
  • Any body know a fix for this?

    Seriously, anything useful in Sharepoint has been deliberatly blocked.

    Use a calculated column to make a auto-generated hyperlink realted to the item? Dream about it.
    Get decent interactivity from the autocomplete menu? Not gonna happen.
    Make a usefull link between two related listsd? Yeah bloody right!!

    WHY do I hit nothing but brick walls with this sharepoint thing?

    Friday, August 26, 2011 3:05 AM
  • Would like to see this changed in SP too.
    Friday, June 01, 2012 9:28 PM
  • Here is a workaround utilizing SharePoint Designer workflows.
    http://rrfreeman.blogspot.com/2013/05/projected-lookup-field-lookup-column.html


    RobertRFreeman

    Sunday, May 05, 2013 7:16 PM