Best way to get to Lookup item details with Get-MgSiteListItem?

[o-o00o-o]

building on #764 my sharepoint list has some lookups - pretty commonly a Contact lookup with a name, email address etc nicely available in the list UI.

I want to be able to easily get to the Contact details from the list item. It seems very difficult to do so I'm sure I must be missing something here.

I have a ContactLookupId reference that is an integer e.g. 6 from the item.fields.AdditionalProperties.ContactLookupId field

How do I get the details for Contact 6 using the Powershell API?

• I've found this that describes how to do it directly using the rest API. https://stackoverflow.com/questions/64867311/get-sharepoint-user-by-lookup-id-using-microsoft-graph - however I don't know which list holds the Contacts that this list relates to. tried Get-MgContact to no avail.

Get-MgContact -Filter 'Id eq 6'
## invalid filter clause
Get-MgContact -OrgContactId 6
## invalid object identifier 6 (guess it needs a guid.. but I don't have the guid I have "6" from the list)

• Perhaps I have to resort to Invoke-MgGraphRequest but how do I find the list that contains the contacts? (it doesn't seem to be the same as the UserInformationList as item 6 in there is not the item that the list lookup references). It seems that neither UserInformationList or Contacts are shown when I just do a Get-MgSiteList -SiteId 'mysiteurl

Any tips or links welcome

[o-o00o-o]

This is the best I have been able to come up with so far

$ListBaseUri = 'something.sharepoint.com:/sites/mySite:'

$UsersListId = ((Invoke-MgGraphRequest -Method GET -Uri "https://graph.microsoft.com/v1.0/sites/$ListBaseUri/lists?filter=DisplayName eq 'User Information List'" -OutputType Json | ConvertFrom-Json).value).id

$UserLookup = @{}

(Get-MgSiteListItem -SiteId $ListBaseUri -ListId $UsersListId -Select 'fields' -ExpandProperty 'fields($select=id, title, email)').fields |
    Select-Object -Property `
        Id,
        @{Name = 'AdditionalProperties'; Expression = { New-Object PSObject -Property $_.AdditionalProperties}} |
    select -Property Id -ExpandProperty AdditionalProperties |
    Foreach-Object {
        $userLookup.Add($_.id, ($_ | select title, email))
    }

# now we have a super fast lookup method
<#
$UserLookup."6"

Title          EMail
-----          -----
A Person aperson@adomain.com
#>

Now we can use that hashtable to get the data out of the list we are really interested in

$listItems = Get-MgSiteListItem -SiteId $ListBaseUri -ListId 'The List' -select 'fields' -ExpandProperty 'fields'

# Exclude all the normal fields
$listItemValues =
    $listItems.fields.AdditionalProperties |
    ForEach-Object {New-Object PSObject -Property $_} |
    Select-Object -ExcludeProperty '@odata.etag','_ComplianceFlags','_ComplianceTag','_ComplianceTagWrittenTime','_ComplianceTagUserId','_UIVersionString','LinkTitle','LinkTitleNoMenu','Modified','Created','ContentType','Edit','FolderChildCount','ItemChildCount','Attachments','AuthorLookupId','EditorLookupId'

# Now do any lookups if necessary (see related section on lookups)
$listItemValues2 = $listItemValues |
    Select-Object *,
       @{Name = 'Name'; Expression = {($UserLookup.Item($_.ContactLookupId)).Title}},
       @{Name = 'Email'; Expression = {($UserLookup.Item($_.ContactLookupId)).Email}}

It has taken me a few hours to get to this point. Feels like improvements would be

• it would be nice if AdditionalProperties is a proper PSObject rather than hashtable as per non-powershell experience with getting field values from Get-MgSiteListItem  #1659 so I can avoid the conversion step so I can use Select-Object
• ExpandProperty should support multiple nested expansion so we can do without the prepared hashtables and lookup stages

[peombwa]

It looks like you were able to get what you wanted using the steps above.

On having AdditionalProperties as a PSObject type, see my response in #1659.

On ExpandProperty (OData $expand) supporting nested expansion, this is limited to what the REST API supports. I'd recommend you open a feature request at https://developer.microsoft.com/en-us/graph/support for this to be supported by the API.

In the future, please open a question at https://developer.microsoft.com/en-us/graph/support to get assistance directly from the API owner. They will be able to help you with API specific questions. This repo is intended for issues related to the functionality of the module we provide.

[peombwa]

Also, you can shorten you implementation by using Get-MgSiteListItem to get ContactLookupId details from the User Information list item as show below:

$listItemValues2 = @()
$UsersList = Get-MgSiteList -SiteId $ListBaseUri -Filter "DisplayName eq 'User Information List'" -Select Id
$listItems = Get-MgSiteListItem -SiteId $ListBaseUri -ListId $ListId -Select 'fields' -ExpandProperty 'fields'
$listItems.Fields.AdditionalProperties.ContactLookupId | ForEach-Object {
    if ($null -eq $_) { return }
    # Get ContactLookupId details from the User Information List Item.
    $userLookup = Get-MgSiteListItem -SiteId $ListBaseUri -ListId $UsersList.Id -ListItemId $_ -Select 'fields' -ExpandProperty 'fields($select=id, title, email)'
    $listItemValues2 += New-Object PSObject -Property @{
        Id = $userLookup.Id
        Title = $userLookup.Fields.AdditionalProperties.Title
        Email = $userLookup.Fields.AdditionalProperties.Email
    }
}
Write-Output $listItemValues2

[o-o00o-o]

Thanks @peombwa for the improved code suggestion. This special 'User Information List' was difficult to find and actually I didn't realise that there was one for each site, so was looking at the global one for a while and nothing made any sense as the ID's didnt match up).

The only thing I might change is to continue to get the contact list details into a cached hashtable up-front to improve the performance for most use-cases

My suggestion is that it would be great to add this example as documentation for the API in the standard docs. There are a lot of things found when you search to try to solve this problem and most of it is not focused on this PS API but general REST or even previous versions of the API (non graph) so would be great to have some easy to discover, official examples that uses the PS API in the best way.

Thanks again

[peombwa]

Got it!

Please provide feedback to the API owner at https://learn.microsoft.com/en-us/graph/api/resources/sharepoint?view=graph-rest-1.0#whats-new (Feedback section) for the example to be added. Examples in the API reference are provided by the API owner.