1

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:

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!

  • What is the type of the lookup column? – GVIrish Apr 3 '18 at 17:51
  • The column is listed as type Lookup. It is looking up the Title column of a custom list. – Laura Calaway Apr 3 '18 at 19:16
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)"
}
  • I tried this with both the display and internal names, and still get blank results. There must be something else I'm missing. – Laura Calaway Apr 3 '18 at 12:55
  • Update: I tried this method on another lookup column in my list, and it does work (using the internal name). It still comes up empty for the column I'm interested in, though. Now I'm really confused! – Laura Calaway Apr 3 '18 at 13:07
  • That's quite peculiar. What are you "looking up" to ? Is the lookup list in the same site ? What columns are being fetched in this lookup field ? Probably that might us figure this out. The fact that its working on a different list suggests that the code is quite right, but there might be some issue with the column – Gautam Sheth Apr 4 '18 at 5:24
1

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

enter image description here

'%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.

  • Thank you Joanna. I tried this and I still get blank values in the results. – Laura Calaway Apr 3 '18 at 12:54
  • What the account are you using? Try admin account. – JoannaW_MSFT Apr 4 '18 at 7:02
  • This should have been the first place I looked for the actual field name, not the display value. You can also find the field name using a CAML query tool. – Taco_Buffet Oct 31 '18 at 20:04
0

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.

  • Thanks GVIrish. Unfortunately that produced an error: "Unable to find type [Microsoft.SharePoint.Client.SPFieldLookupValue]. I think this may be one of the differences between PnP PowerShell and other versions? – Laura Calaway Apr 2 '18 at 21:28
  • Hmm OK so if FieldLookupValue is the correct call, maybe the issue is the internal vs display name of the column. What did you try for the internal name? Also, you may not need the subexpression parenthesis in there $( ) – GVIrish Apr 2 '18 at 21:38
  • I had also tried the internal name, and the result is the same. Could you help me understand how to format it with the subexpression parenthesis? I'm a novice at this. – Laura Calaway Apr 3 '18 at 3:42
  • I was just suggesting that you try the output line like this: write-host "$itemTitle, lookupValue - $($lookup.LookupValue)" But it looks like that's not the problem. There may potentially be some sort of issue with the actual column. – GVIrish Apr 3 '18 at 17:43
0

Use column internal name instead of display name.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.