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.
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.
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
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.
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)?