Since the publication of this article, ShareGate Desktop has updated and unified the migration interface. The following screenshots may no longer be accurate, we are working on updating them.
Lookup columns are all about relationship. In essence, they are columns that refer to the content of another list. A lookup column can also reference data in the same list (in which case we call it a self-lookup).
In order to put this into perspective, let’s say that I have a resource list that lists all my vehicles. Those are associated with sales representatives on the road from the Employees list.
Keeping The Relation When Migrating Lookup Columns
The key to migrate lookups is to preserve the relationship between the two lists. First, it is important that the associated list (Employees) exists before the migration of the list (Vehicles). This will ensure that the column can be migrated. Secondly, the values in the Sales Representatives list also need to be the same at the source and at the destination. In sum, you want to have the associated list present at the destination with up-to-date content inside it.
In order to copy the Vehicles list from my test environment to my production environment, I can simply drag and drop it to the destination. The great thing is that Sharegate will make sure that the associated Employees list is present at the destination and it will automatically migrate it prior to the Vehicles list if needed.
A possible error message you might encounter is “The lookup field refers to a list that cannot be found.” This means that Sharegate is unable to preserve the relationship because the lookup list has either been corrupted or deleted. An example of that is if the Employees list was deleted, my Salesperson column would lose its “Get information from:” value.
In such cases, the lookup column will simply not work anymore and recreating the lookup column will be necessary in order to point to the new list. In any case, when the lookup column is corrupted in such a way, in normally does not contain any valuable information anymore, so deleting is not a problem.
If on the other hand, you get an error message like “The referenced list column ‘Full Name’ was not found in the source list.”, then it means that only the “referred to” column was deleted in the Employees list.
For example, let’s say I created my Salesperson column to refer to the ‘Full Name’ column of the Employees list. A few months later, someone went in the Employees list and removed ‘Full Name’ and replaced it with ‘First Name’ and ‘Last Name’. In this case, the Salesperson column would be referencing a non-existing column, hence the error.
You might not notice that the column is corrupted in SharePoint because it will automatically use the Title column as a fallback, so the column will still work. In order to solve this, you can simply go in the Salesperson column’s settings and click OK, SharePoint will fix the column and make it reference an existing column in the Employees list.
Another potential error is a missing value within the lookup list. The usual cause of this error is when the content of the list referenced by the lookup column is not the same at the source than at the destination. For instance, I have “Sports car 1” in my Vehicles list that is associated to “Smith, Mark” from the Employees list.
Then, I migrate the Vehicles list from my Dev environment to my Prod environment where the Employees List already existed. Yet I encounter “The value ‘Smith, Mark’ is unavailable. Please specify another value.” This indicates that “Smith, Mark” is not a valid value, because this employee is not listed within the Employees list.
Migrate SharePoint Lookups Without Losing Anything
1 - Copy the contents of the list referenced by the lookup column from the source to the destination. In this case, the list Vehicles has a lookup column called Salesperson that references the Employees list, so the contents of the Employees list should be copied before copying the contents of the Vehicles list.
2 - Set a default value in Sharegate to use when the source item contains an invalid value for the destination lookup column. This can be done by creating a Property Template and setting the column to "Set mapped value or default".
Doing this will replace every value that is no longer valid with the specified default value, so the data will be altered. In this example, if “Smith, Mark” is not in the destination list because he is no longer working here, I might want to re-associate “Sports car 1” with someone else, or leave the Salesperson column empty.
As a rule of thumb, Sharegate will preserve the important relation with the lookup list. If you encounter any lookup errors, chances are that you will need to verify that your lookup column refers to a list that exists and that has up-to-date content. Finally, if some values are invalid, take advantage of the Property template to replace them with new valid values.