Problem copying lookup column data from one list to another via PowerShell

Question

I have the below script that works for moving list data between two lists on sites next to each other that have the same schema. Everything works perfectly bar a column of type LOOKUP named "Name of Project" which steadfastly remains empty. The code works I can write the lookup value ID or Title to the screen or log but cannot make it stick to the target list row.

I have checked the following:

The lookup list being used for the problem column has the same IDs as the one being used for the source list.
The internal and external column names on both lists match. External = "Name of Project" Internal = "Project"

Is my code correct? Is there a way to get a more verbose output to see WHY the field fails? -Verbose does naught. (I have even tried manually writing ID;#Text without luck.)

Is there a way to make this dynamic so that it can be run with minimal configuration against lists that have many lookups?

    $srcListSiteUrl = "http://intranet/site1";
    $SourceListName = "Tasks";

    $dstListSiteUrl = "http://intranet/site2";
    $DestinationListName = "Tasks";

    Remove-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
    Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
    try
        { 
        $sourceListWeb = Get-SPWeb -identity $srcListSiteUrl
        $sourceListUrl = $sourceListWeb.ServerRelativeUrl + "/lists/" + $SourceListName;

        $dstListWeb = Get-SPWeb -identity $dstListSiteUrl
        $destinationListUrl = $dstListWeb.ServerRelativeUrl + "/lists/" + $DestinationListName;

        $SourceList = $sourceListWeb.GetList($sourceListUrl);
        $DestinationList = $dstListWeb.GetList($destinationListUrl);

        $sourceSPListItemCollection = $SourceList.GetItems();

        foreach($srcListItem in $sourceSPListItemCollection) {
            Write-host "Adding new item $srcListItem";
            $newSPListItem = $DestinationList.AddItem();
            #Write-Host "- Working with the items column values...";
            foreach($spField in $srcListItem.Fields) {
                if ($spField.Title -ieq "Name of Project") {
                    Write-Host "Found lookup column.";
                    Write-Host "$srcListItem[$spField] is type $spField.Type";
                    $lookupfieldvalue = $srcListItem[$spField] -as [Microsoft.SharePoint.SPFieldLookupValue];
                    $newSPListItem[$spField] = $lookupfieldvalue;               
                } elseif ($spField.Type -ieq "User") {
                    $newSPListItem[$spField] = $srcListItem[$spField];
                } elseif ($spField.Title -ne "Attachments") {
                    if ($spField.ReadOnlyField -ne $True -and  $spField -ne "Attachments") {
                        $newSPListItem[$spField] = $srcListItem[$spField];
                    }
                }
            }
            $newSPListItem.Update();
        }
    } catch {
        Write-host $_.exception
    } finally {
        if($sourceListWeb -ne $null){$sourceListWeb.Dispose()}
        if($dstListWeb -ne $null){$dstListWeb.Dispose()}
    }

    function Get-SPList($webUrl, $lstUrl)
    {
        $webObj = Get-SPWeb -identity $webUrl;
        $lstObj = $webObj.GetList($lstUrl);
        return $lstObj;
    }

I have updated the answer to clarify the names of the internal and external columns. The internal name is "Project" for both lists. — Alan
– Alan, Commented Sep 29, 2014 at 14:52
I am saying to try if ($spField.Title -ieq "Name_x0020_of_x0020_Project") — Aanchal
– Aanchal, Commented Sep 29, 2014 at 14:54
$newSPListItem[$spField] works the issue appears to be specific to the fact that the target column is a lookup. — Alan
– Alan, Commented Sep 29, 2014 at 15:20

Jeff Freeman · Accepted Answer · 2014-09-29 20:18:38Z

You may still need to do a lookup on the project list regardless. Here's a function that will look up the value from one list and update the other with the correct lookup value field:

Function Copy-LookupFieldValue
{
    param
    (
        [Microsoft.SharePoint.SPListItem] $srcItem,
        [Microsoft.SharePoint.SPListItem] $dstItem,
        [Microsoft.SharePoint.SPList] $lookupList,
        [string] $lookupFieldName
    )

    $oldFieldLookupValue = $srcItem[$lookupFieldName] -as [Microsoft.SharePoint.SPFieldLookupValue];
    If ($oldFieldLookupValue -ne $null)
    {
        $lookupValue = $oldFieldLookupValue.LookupValue;
        $item = $lookupList.Items | Where { $_.Title -eq $lookupValue };

        $newLookupFieldValue = New-Object Microsoft.SharePoint.SPFieldLookupValue($item.ID, $item.ID.ToString());
        $dstItem[$fieldName] = $newLookupFieldValue;
        $dstItem.Update();
    }        
}

You need to reference the source list item as $srcItem, the destination list item as $dstItem, and the lookup list (on the $dstItem site) as $lookupList. After that, you can use the above function in order to update the column.

Lastly, not sure if this is what you mean, but you can get more verbose output by inserting the following at the top of your script:

[CmdletBinding()]
Param()

Now, you can use the -Verbose switch when running your script.

westerdaled · Accepted Answer · 2015-01-30 13:36:59Z

if you have multiple lookup values to add then a slight variation on @jeff Freeman's excellent post. in my case I was required to copy the same lookup values into a column that replace the old one - don't ask!

foreach ($item in $list.getItems() )
{

# look at what is in the old get lookup values 
    $sourceLookupFieldValues = $item[$sourceLookupFieldDisplayName] #  

    # this should be empty but we could check
    $destLookupFieldValues = $item[$destLookupFieldDisplayName] # 

    $count = $destLookupFieldValues.Count
    # get rid of any current ones
    $destLookupFieldValues.RemoveRange(0,$count)

    foreach ($sourceLookupFieldValue in $sourceLookupFieldValues)
    {  

       # get the current values 
       $srcId = $sourceLookupFieldValue.LookupId;
       $srcValue = $sourceLookupFieldValue.LookupValue;

       Write-host -fore DarkYellow "Lookup Value: " $srcValue " Id: " $srcId  

       # set the corresponding value in new lookup column
       # 
       # add another lookup value since this is multi select.
       $destLookupFieldValues.Add($sourceLookupFieldValue)

    }

    $item[$destLookupFieldDisplayName] =  $destLookupFieldValues

    $item.Update()

    $list.Update();

 }

Stack Exchange Network

Problem copying lookup column data from one list to another via PowerShell

2 Answers 2

Hot Network Questions

Problem copying lookup column data from one list to another via PowerShell

2 Answers 2

Related

Hot Network Questions