Retrieve value in lookup field type using SharePoint PnP PowerShell

Question

I am trying to retrieve the value of a lookup field type from a library in SharePoint Online. I am using the PnP PowerShell Framework, having downloaded the March 2018 release from GitHub (https://github.com/SharePoint/PnP-PowerShell/releases).

I reviewed the following posts, and have tried various combinations of the answers provided therein:

• Fetch Username from Lookup-Column using Powershell
• SharePoint online list get value lookup field with PowerShell
• Fetch Username from Lookup-Column using Powershell-PNP

I've included the entire script here in case there's something more fundamental I've missed elsewhere:

$policy = Get-ExecutionPolicy
if($policy -ne "RemoteSigned"){
    Set-ExecutionPolicy unrestricted
    }

$url = "https://company.sharepoint.com/sites/sitename"
$listTitle = "A&B Library Title"

Connect-PnPOnline -Url $url -UseWebLogin

$list = Get-PnPList -Identity $listTitle

$items = Get-PnPListItem -List $listTitle

foreach ($item in $items)
{    
$itemTitle = $item.FieldValues.FileLeafRef
$lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$item.FieldValues["A&B Column"]
write-host "$itemTitle, lookupValue - $($lookup.LookupValue)"
}

No errors are returned, but the values are all returned blank. For example, despite the fact that ABC.docx, 123.xlsx, and A53.docx all have values in the A&B Column, the output displays as follows:

ABC.docx, lookupValue -
123.xlsx, lookupValue -
A53.docx, lookupValue -

I also wanted to highlight that both the library name and the column name have ampersands in them. This doesn't seem to be an issue for the library title, but I'm not sure if that's what may be preventing the script from returning values from the lookup column. I've tried using both the internal and display names for the column, to no avail.

Thank you in advance!

Answer 1

You dont need to do a type-casting whenever you are using the $item.FieldValues.

Simply remove this line [Microsoft.SharePoint.Client.FieldLookupValue] from your code and it should work.

The foreach loop should be as below, use the internal column name:

foreach ($item in $items)
{    
    $itemTitle = $item.FieldValues.FileLeafRef
    $lookup = $item.FieldValues["A&B Column"]
    write-host "$itemTitle, lookupValue - $($lookup.LookupValue)"
}

Answer 2

Per my testing results, the PnP PowerShell script works as expected.

As motioned by other communities, use the internal name.

To get the actual internal name for your column, browse to the library Settings > Edit Column and look at the path.

The path will be like:

https:///_layouts/15/FldEditEx.aspx?List=&Field=A%5Fx0026%5FB%5Fx0020%5FColumn

'%5F' is the '_'. The internal name of my 'A&B Column' column is 'A_x0026_B_x0020_Column'.

Check the internal name of your column by yourself, avoid using the wrong internal name.

Answer 3

I think you need to use:

$lookup = [Microsoft.SharePoint.Client.SPFieldLookupValue]$item.FieldValues["A&B Column"]

Looks like you're missing SP there in the type cast.

Answer 4

Use column internal name instead of display name.

$lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$item.FieldValues["A_x0026_B_x0020_Column"]