none
Pointing inherited site columns to a new parent site column RRS feed

  • General discussion

  • This scenario is probably not officially supported or encouraged since it requires directly modifying your SharePoint content database...but it saved us a lot of time and I think can be useful if done carefully and properly.

    We have a list on our top-level site that contains one row for each Bid (Proposal) our company has submitted. The key field in this list is the Bid Number. We want the Bid Number to be available to other lists in the top-level site and in multiple sub-sites. The way we do this is to create a Site Column in the top-level site that is a Lookup field into the Bid Number field of the Bids list. Then when we add this Site Column as a new column in other lists, those lists have access to the current list of Bid Numbers in our system (the nice thing is that this lookup is dynamic...every time we add a new Bid to the Bids list, the new Bid Number is now accessible in all other lists that reference the Site Column).

    However, we ended up moving our Bids list to another list to take advantage of a different built-in list type. The list ended up with the same name and the same data...but as far as SharePoint was concerned it was a totally different list than the original Bids list because the list's unique ID (GUID) was different. This also meant that the Site Column that was based on the Bid Number field was affected. How could we get all the lists that were referencing the Bid Number in the old Bids list to point to the Bid Number in the new Bids list with the least amount of effort?

    It turns out that this was fairly easy through a direct modification to our Content Database. Again...if you are not confident with making direct data modifications to your content database...you probably want to avoid doing this or at least practice on a development copy of your SharePoint database before trying this in a Production environment. Here's what we did:

    1. Open SQL Server Management Studio (if using SQL Server 2005...not sure how the steps might differ in SQL Server 2000 since I believe the structure of the content databases are a bit different) and locate the Content Database for the SharePoint site in question
    2. Find the ContentTypes table and open the table to display the data
    3. Locate the Site Column you are looking for...it will be represented by a single row in this table. You need to be looking at the "Definition" column. In our case, the row that represented our Bid Number site column had this data in the Definition column of the ContentTypes table:
      • <Field Type="Lookup" DisplayName="Bid Number" Required="FALSE" List="{6a50ab6f-99af-4aab-830e-dbec9b506b97}" WebId="e76b685c-6c78-42c9-b8d9-6e2d5fb39aa9" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" Group="Our Site Columns" ID="{5141fad8-17eb-46d5-b425-0c21cad3be23}" SourceID="{e76b685c-6c78-42c9-b8d9-6e2d5fb39aa9}" StaticName="Bid_x0020_Number" Name="Bid_x0020_Number" />
    4. The data in the Definition field contains the values you entered when you originally created the site column. If you have created a Site Column before, most of these key/value pairs should look familiar. The long strings of numbers and digits are the Unique ID (GUID) values that SharePoint uses to uniquely identify just about everything...no two will be the same.
    5. All we had to do was CAREFULLY modify the value of the List= field in the Definition column...changing it to the GUID of the new Bids list. Because the name of the Bid Number field did not change from the old to the new list, we did not have to modify either the StaticName or Name fields. See the highlighted value below to see what was changed:
      • <Field Type="Lookup" DisplayName="Bid Number" Required="FALSE" List="{6a50ab6f-99af-4aab-830e-dbec9b506b97}" WebId="e76b685c-6c78-42c9-b8d9-6e2d5fb39aa9" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" Group="Our Site Columns" ID="{5141fad8-17eb-46d5-b425-0c21cad3be23}" SourceID="{e76b685c-6c78-42c9-b8d9-6e2d5fb39aa9}" StaticName="Bid_x0020_Number" Name="Bid_x0020_Number" />
    6. There are multiple ways to find the GUID of the new list...you can look for it in the AllLists table in your Content Database (it will be the value in the tp_ID field), or you can get the GUID from within SharePoint by going to the Settings page for the list and looking at the URL of the Settings page...the GUID will be shown after the List= parameter in the URL (be careful...some parts of the GUID may be HTML-encoded...such as the "{" character being encoded as "%7B"...so be VERY CAREFUL that you know for sure that you are using the correct GUID value) as follows:
    7. Once we changed the GUID to the GUID of the new Bids list, we went into SharePoint Site Settings for the top-level site and went to the Site Columns Gallery where the Bid Number Site Column was located.
    8. We then clicked on the Bid Number site column to edit it, and made sure that the "Update all list columns based on this site column?" option was set to "Yes" and clicked OK. This forces a refresh of all columns in the system that are referencing this Site Column, so that it now starts looking at the data in the new Bids list and no longer looks at the old Bids list.

    Please take this for what it's worth and be careful as you work with your Content Databases.

    Friday, January 12, 2007 8:54 PM