Content Type enforcing foreign key relationship RRS feed

  • Question

  • Hi,

    I have two lists, lets call them Cities and States. Each City belongs to 1 State. The Cities list has a lookup column which links to the appropriate State record. I also have a Content Type which uses a City as a lookup column (so that in the Document Information Panel in Office you have a drop down). However, I also need to have State as a lookup column in this Content Type, and have it correctly filter the drop down lists (based on the foreign relationship) when making a new instance of this Content Type.

    Does anyone know if this is possible?

    Thanks for any help.

    Tuesday, April 3, 2012 1:46 PM

All replies



    To do this, we might need to create another lookup column for the content type to lookup the states field in the cities list. However, we are unable to create a lookup column as so.

    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


    Multiple lines of text



    Person or Group



     Date and Time

    Hyperlink or Picture


    Best Regards,

    Sally Tang

    • Marked as answer by Xue-Mei Chang-MSFT Monday, April 16, 2012 2:36 AM
    • Unmarked as answer by MgSm88 Monday, April 16, 2012 12:42 PM
    Thursday, April 5, 2012 8:43 AM
  • As I said in my initial post, I am already using look up columns. I'm wondering if it's possible to have the choice of one dropdown filter another dropdown based on the lookup values.
    Monday, April 16, 2012 12:45 PM
  • Hi,

    I am sorry not explaining clearly.  I understand that you would like to create a content type, using city as a lookup column, also have state as a lookup column, the state column should be filtered according to the city column.

    To do this, we need to create another lookup column to lookup the state field in the cities list. However, as city is also a lookup column, we are unable to do this using calculated columns. Because lookup fields cannot be used in calculated columns, we cannot reference lookup fields in a formula.

    The workaround is to create a dynamic string in SPD workflow and save it to a workflow variable, then update the column with the variable.

    For more information about creating dynamic string, please refer to:


    Hope it helps.

    Best Regards,

    Sally Tang

    Wednesday, April 18, 2012 3:22 AM
  • Thanks for the response, but I need the related picklists to filter based on the choice. Your solution seems like it is designed to just set the value of some column after the user has stopped editing the metadata properties. I need to be able to do this filtering whether the user is setting the metadata properties in the Document Information Panel, or in the browser through the Edit Properties window.

    It seems like InfoPath should be able to support this type of thing (what's the point of a program that makes custom forms if it can't do the most basic of form requirements like filtering related fields)?

    Wednesday, April 18, 2012 12:48 PM