SharePoint 2010 offers a lot of new functionality for listings. One of them is “Additional Columns”. So what does it mean? Let’s say we have two lists called Provinces and Cities. Each City is in one of the Provinces.
This means that you create a lookup field from the Cities list to the Provinces list. But i want to see the title and the province code of the Province. This is established using so called additional columns.
Goto the list settings of the Cities list and add a new column. Call that column Province and set the type to Lookup. Now you will get options for additional columns. Select the Provinces list from the dropdown box and choose the field Title as value for that column. Now indicate the ProvinceCode field as an additional column.
When returned to the list settings you will notice that a lookup field is created called Province and a lookup field called Province:ProvinceCode. That last field is the additional column. When a province is selected for a city the additional field from the refered province item is shown in the city item.
When you create a solution for a customer you will be creating definitions of Fields, ContentTypes and Lists. To create a lookup field and an additional column is as follow:
<Field ID=”{2E652DA6-D53B-4C5F-8C98-1ADCB98F1E71}”
Name=”Province”
DisplayName=”Province”
Type=”Lookup”
FieldRef =”ID”
ShowField =”Title”
Required=”TRUE”
Group=”MyFields”
List=”Lists/Provincies“>
</Field>
<Field ID=”{55b4c728-dc8c-4255-bcee-9ffa6ee24020}”
Type=”Lookup”
DisplayName=”Province:ProvinceCode”
List=”Lists/Provincies”
ShowField=”ProvinceCode”
FieldRef=”{2e652da6-d53b-4c5f-8c98-1adcb98f1e71}”
ReadOnly=”TRUE”
Name=”Province_x003a_ProvinceCode“>
</Field>
You will notice a few things:
a) The Field Province is needed before the additional field is created. The additional field refers to the Field Province by the FieldRef property.
b) The List property of both Field elements referes to the Provinces list.
c) The Name property contains the value Provincie_x003a_ProvinceCode which is actually the Name of the Field Province, the “:” character translated to _x003a_ and the name of the field ProvinceCode which is refered by the additional column.
d) The ReadOnly attribute fo the additional column is set to TRUE. You are not able to change additional columns. They are only retrieved as extra data from the refered list.
Add both Field elements as a FieldRef to your ContentType and schema.xml file of the list.
Because the Province:ProvinceCode column is now part of the Cities list you are able to query it by for example CAML code.
SPList list = site.RootWeb.Lists["Cities"];
SPQuery query = new SPQuery();
query.Query = string.Format(@”
<Where>
<Eq>
<FieldRef Name=’Provincie_x003a_SVnProvinceCode‘ />
<Value Type=’Lookup’>{0}</Value>
</Eq>
</Where>”,
4);
SPListItemCollection cityCollection = list.GetItems(query);
In stead of getting the cities based on the title of the province, you have the ability to do it based on the province code.
