Select Page

Importing linked lists into existing MOSS 2007 site

Importing linked lists into existing MOSS 2007 site

Picture this situation: you have two operational MOSS 2007 sites, possibly at two different MOSS servers. Now, you have to copy two SharePoint lists from one site to another, retaining all additional information like field names, descriptions, views… Well, it’s easy: you’ll save the lists as templates on the first site, download the templates, upload the templates onto another server (if we’re talking about two different MOSS servers), create new lists from templates and voila – that’s it.

Well, almost. What if the two lists are linked, means, the "List 1" has a lookup field which retrieves data from the "List 2", a sort of foreign key?

The lists are inside MOSS defined through their guids, and lookup field in the "List 1" stores information about "List 2" through it’s guid. Now, when you create new lists from templates on the second MOSS Site, they will of course get new guids, and the link, the "foreign key", will be lost, gone in nirvana. If you then try to enter some data into the list, you’ll see the empty drop-down box, something like:

If you go to the list settings and try to edit the list field, you’ll se the disabled placeholder (oh, yes, we all love SharePoint!) in the "Get information from" field, what means, SharePoint doesn’t offer you the possibility to change the item-datasource dinamicaly and restore your relation:

Even more, it won’t let you do anything with that field, except of deleting it.

So, if you have only a few items in the "List 1", it doesn’t really matter: delete the field, create it again, and manually enter the missing values for each list-item. But, if you have few dozens, hundreds, or thousands of items in the list, then you have a problem, also. A big one. The one of the sort which is not thought of by SharePoint.

Well, luckily there is a way around (that’s the reason why I am writing this blog, right?)

For this example, let’s suppose that we have a list of all customers in the "List 1" (a few hundred, if the business goes well), and list of the countries in the "List 2". And a field named "Country" in the "Customers" list which is a lookup field which retrieves it’s data from the "Countries list". Pretty straight-forward.

So, let me describe the procedure in few steps:

1. Save both lists (Customers and Countries) as templates at the source site.

2. Download both templates on your hard drive

3. Upload "Countries" list template from the (the list with the "source" data) to the new server

4. Create a new Countries list from the "Countries list template" on the new server, so that we get a valid list with the "source data" countries.

5. Now, as we have seen, lists are linked through their guids. It means, we have to find out the guid of the newly created "Countries" list. There are few ways to find it out, but the most straight forward way is to go to the list settings of the new countries list, and to pay the attention of the URL of the list-settings page: it contains the Guid we want:

We see the List guid there:


Now we have to replace the hex characters: %7B with "{", %7D with "}" and %2D with "-" so that we get the guid in it’s original, valid form:


OK, now save it somewhere.

6. On your hard drive, where you have downloaded site templates from the source MOSS server, rename the template of the list 1 (the "Customers" list), and change it’s extension from *.STP to *.CAB (yes, STP file is actually a CAB file).

7. Open the CAB file with some archiving utility that can read and write CAB files (i.e. ACDZip).

8. Extract the manifest.xml file from the CAB, and open it in editor.

9. Find the "Field" element in the XML file which contains info about your lookup field, ie:

In this Field element, there is a "List" attribute which contains Guid of the source (Countries!) list (red marked above). Replace the value of the List attribute Guid with the one we have retrieved from the "Countries" list in the step 5.

10. Replace the original "manifest.xml" file in the CAB archive with the new one (edited one).

11. Rename again the CAB file to the STP file.

12. Upload the modified STP file to the new server, and create the "Customers" list from this new template. And: all items from the customers list are properly linked to their countries!

Well, it might be a little incovenient, but still better than manually entering a few hundred Countries

Live long and prosper!