move-a-sharepoint-list.jpg

How to Move a SharePoint List with Lookup Columns Using List Templates

Caroline Sosebee is a Software Engineer at ThreeWill. She comes to us with 20+ years of software development experience and a broad scope of general IT support skills.

I recently had to find the best way to move two SharePoint lists, with content, from a testing/UAT environment into a production environment so that the tweaks the client had made in UAT were not lost. This ended up being a fairly straightforward task overall since they were new lists being created into the production site. The main caveat was that one of the lists had a lookup column pointing to the other list as well as a projected column. We finally decided to use the OOTB ‘Save list as template’ functionality since it brings overall customizations (views, new columns, etc). But it doesn’t handle moving lookup references gracefully. After playing on the web a while, I got it figured out. This post will address a fairly simple way to overcome this problem. It looks like a lot of steps, but I promise it’s not all that hard to do.

FYI, I don’t recommend letting the users treat UAT as a pre-production data massaging location as it can cause lots of problems. It just kind of turned into that in this case and luckily we managed to get the lists moved without any major mess-ups.

So what keeps List Templates that have lookup columns from working in a new, different environment is the fact that there are list and web GUIDs buried within the template xml. Luckily, we can edit this xml and change them to point to the new environment.

Here are the steps you need to follow to get your template ready for your new environment.

1. On the source site, go to the list to be saved and select List Settings.

2. From List Settings, click the ‘Save list as template’ link

3. On the Save As Template page, fill out the fields, paying special attention to the Name and Description boxes, as these are what will help you find it easily in a later step. Also, be sure to check the Include Content box if you need to move the data as well as the structure. Click OK.

4. On the ‘Success’ page (you got this, didn’t you?!), use the ‘list template gallery’ link to take you to the place where the template was saved.

5. Here you should see your newly generated template.

6. From here you will need to download a copy of the template (in the normal way) to your local machine. Navigate to the local folder you saved the file in and you should see your file there with a .stp extension.

7. Rename this file to have a .cab extension so you can extract the files. (If you are using 7-Zip, it already recognizes it as a compressed file so renaming it is not necessary.)

8. There is only one file within the .stp file, called manifest.xml. This is the file you will be manipulating.

9. We now need to shift gears and go to the destination site so that we can pull the new web GUID we will need when updating the manifest file. The easiest way to do this is via the browser, using a rest call (thanks to Mike Smith and his blog for the refresher found here). Navigate to a URL that should look something like this:

http://yourweb/sites/yoursite/_api/web/

10. This will bring back lots of info, including the GUID you will need. <img class=”wp-image-60863″ style=”display: block; margin-left: auto; margin-right: auto;” src=”https://10nkyj1cgq7f1m2hjafk6vn3-wpengine.netdna-ssl.com/wp-content/uploads/word-image-82.png” alt=”” /> 11. Now that you have the destination web GUID, head back over to the manifest file and open it in your favorite text editor (I prefer Notepad++). 12. Do a search for the phrase <strong>Type=”lookup”</strong>. This should take you to the first lookup field in the file. Yes, this is a big gnarly pile of XML, but just be careful with your fat fingering and all will be well. (Yes, I speak from experience!). And note that you should only change your own custom lookup field references and not any of the system fields, so always check the field name before updating. There are two types of lookup fields to be concerned with – a ‘regular’ lookup field and a projected column from the same lookup list. <img class=”wp-image-60864″ style=”display: block; margin-left: auto; margin-right: auto;” src=”https://10nkyj1cgq7f1m2hjafk6vn3-wpengine.netdna-ssl.com/wp-content/uploads/word-image-83.png” alt=”” /> For both field types you will see a List=”{some guid}” reference in the Field definition. You will need to replace this ‘{some guid}’ with the list name instead, using this format:

List=”Lists/listinternalname”

(Note: Be sure to use the internal name of your list)

For a projected column, there’s a second update that needs to be made as well because, for some reason, projected columns also include a GUID reference to the web itself. This GUID will have to be replaced with the GUID we pulled earlier from the destination web. The value we need to replace is found in the WebId attribute. Be sure to follow the same format when replacing the GUID. Note there are no brackets on this one like there are for lists. Gotta love consistency.

13. Once all the custom lookups have been updated, save the manifest file (you can typically ignore any errors thrown when saving in NP++). The next step is to compress the manifest back into a cab file. There’s a utility called ‘Makecab’ that comes standard on most Windows boxes that we can use to do this.

14. First, open a command prompt and navigate to the folder with the manifest file in it. Once there, it’s as simple as running this command:

Makecab manifest.xml filename.stp

The file name can be whatever makes sense to you but it ultimately needs to have an extension of .stp.

15. Go back to the browser, bring up your destination site and navigate to the List Template Gallery. This can be reached via Site Settings or via a direct URL similar to the following:

http://yourweb/sites/yoursite/_catalogs/lt/Forms/AllItems.aspx

16. Upload the newly created .stp file into the template gallery.

17. Now go to Site Contents and select ‘add an app’. From here, you will need to scroll through the ‘Apps you can add’ section to find the newly added template (often it’s near the end). This is where your naming and description come into play as naming it appropriately makes it much easier to find. For example, you can see here where I was playing around and called a couple of things ‘test’. Not very helpful when I’m trying to decide which one I need to add to create my new list, especially if it’s a day or two later.

18. Click on the appropriate template, give it a name when prompted – with no spaces in it please! – and click Create. You should now see your new list in Site Contents and can go into List Settings to rename it to a properly spaced list name.

19. While in List Settings renaming your list, you will also need to check your lookup list columns to ensure they got added correctly. A properly provisioned lookup list will show valid information in the ‘Get information from’ field and if there are any projected columns, will show those as checked. An improperly provisioned field will show these as blank. Here are a couple of examples of right and wrong for each type of field.

Correctly populated ‘regular’ lookup column will have a list reference:

The correctly populated projected column will have a list reference:

Incorrectly populated lookup column will be missing the list reference:

And that’s it. A very long blog post to show you how to do something that in reality only takes 5 to 15 minutes to accomplish. Hopefully, it will help someone else who has a need similar to this!

Thanks go to these helpful links that I used while tackling this for the first time:


Related Posts

Caroline SosebeeHow to Move a SharePoint List with Lookup Columns Using List Templates