How to retrieve lookup field value

[pierodamafalda]

Hello, I am using the Pnp Core trying to read a lookup value in a sharepoint list without success. Do you have a working example?
My try is this:

var listNameBookings = "Bookings";
var itemsBookings = await pnpContext.Web.Lists.GetByTitleAsync(listnameBookings, p => p.Title,
p => p.Fields.QueryProperties(p => p.InternalName,
p => p.FieldTypeKind,
p => p.TypeAsString,
p => p.Title));

var dict = new Dictionary<string, bool>();
foreach(var d in itemsBookings.Items)
{
var Who = (string)d["Who"];
var When = (DateTime)d["When"];
var Where = ((IFieldLookupValue)d["Where"]).LookupValue; // ERROR
dict[Where] = true;
}

but it result in an error:
'((IFieldLookupValue)d["Where"]).LookupValue' threw an exception of type 'PnP.Core.ClientException'

Thanks you

[jansenbe]

Hey @pierodamafalda ,

Hope below can get you unblocked:

var listTitle = "FieldTypes";
var list = await context.Web.Lists.GetByTitleAsync(listTitle, p=>p.Items);

var firstItem = list.Items.AsRequested().First();

Looking inside firstItem we have this: you'll see the single/multi lookup fields with the respective IDs of the related list items.

[jansenbe]

@pierodamafalda , also notice that when using the LoadListDataAsStreamAsync approach the LookupValue is returned by default.

var list = await context.Web.Lists.GetByTitleAsync(listTitle, p => p.Fields.QueryProperties(p => p.InternalName, p => p.FieldTypeKind, 
                                                                                             p => p.TypeAsString, p => p.Title));

await list.LoadListDataAsStreamAsync(new RenderListDataOptions()
{
    RenderOptions = RenderListDataOptionsFlags.ListData
});

var firstItem = list.Items.AsRequested().First();

[OctavianSfetcu]

Hi @jansenbe
if your first example can we somehow load the lookupValue? Not only the lookupId?
Or we should always use LoadListDataAsStreamAsync?
Thanks
Octavian

[OctavianSfetcu]

The second example doesn't work for me. I get this error (I have a document library not a custom list):

"The given key was not present in the dictionary."

" at System.Text.Json.JsonElement.GetProperty(String propertyName)\r\n at PnP.Core.Model.SharePoint.ListDataAsStreamHandler.Deserialize(String json, List list)\r\n at PnP.Core.Model.SharePoint.List.LoadListDataAsStreamAsync(RenderListDataOptions renderOptions)\r\n at

Thanks.

[jansenbe]

That's strange, can you try on a plain vanilla doc library?

[OctavianSfetcu]

I've created a CAML query and in ViewFields I set only the fields I need. I don't get that error anymore. Thanks.

[OctavianSfetcu]

@jansenbe I have another issue about lookups. If my list has another field from the lookup list (Add a column to display these additional fields), it looks like that just the values for Title are loaded and the additional are not loaded. There is any way to load the additional fields of the lookup field? Thanks

[jansenbe]

@OctavianSfetcu : I've tried to load the additional columns by specifying them in the CAML query, but their data is not correctly parsed, but it's retrieved by the query. This is a bug that I'll work on to get fixed

[OctavianSfetcu]

@jansenbe thanks

[jansenbe]

@OctavianSfetcu : I've added a fix, if you know specify the additional fields they'll be populated in the returned field. In below sample the lookup field also pulled the Created column from the connected list, that field value is loaded into the LookupSingle_x003a_Created field:

var list = await context.Web.Lists.GetByTitleAsync("Fieldtypes", p => p.Fields.QueryProperties(p => p.InternalName, p => p.FieldTypeKind,
                                                                             p => p.TypeAsString, p => p.Title));

string viewXml = @"
<View>
    <ViewFields>
        <FieldRef Name='Title' />
        <FieldRef Name='FileDirRef' />
        <FieldRef Name='LookupSingle' />
        <FieldRef Name='LookupSingle_x003a_Created' />
    </ViewFields>
</View>";

await list.LoadListDataAsStreamAsync(new RenderListDataOptions()
{
    RenderOptions = RenderListDataOptionsFlags.ListData,
    ViewXml = viewXml

});

var firstItem = list.Items.AsRequested().First();

See 93fbc04 for the details. This change will appear in the next nightly build.

[tomasz-sintel]

@jansenbe Hi, the described situation works seamlessly, I am facing issues with multi-valued lookup field with additional fields though. If the main lookup field has the "Allow multiple values" option set, I get no data in the additional fields. Looking at your commit, I suspect the problem lies in the FieldLookupValue.cs file:

if (properties.Count == 1)
{
    LookupValue = properties.First().Value?.ToString();
}

I am not sure what happens here if the additional lookup field is multi-valued or even if we reach this part, but I suspect it's somewhere close. Could you please have a look at this?