Need help with expand - Lookup fields (@pnp/sp/items)

%3CLINGO-SUB%20id%3D%22lingo-sub-341338%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20expand%20-%20Lookup%20fields%20(%40pnp%2Fsp%2Fitems)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-341338%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20folks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20lists%20(1.%20Projects%2C%202.%20Schedule).%26nbsp%3BSchedule%20list%20include%20a%20Lookup%20Field%20to%20Projects%20List.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20trying%20the%20(ref.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fpnp.github.io%2Fpnpjs%2Fsp%2Fdocs%2Fitems%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fpnp.github.io%2Fpnpjs%2Fsp%2Fdocs%2Fitems%2F%3C%2FA%3E)%20%22Retrieving%20Lookup%20Fields%22%20according%20to%20guidance%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22kr%22%3Eimport%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22p%22%3E%7B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22nx%22%3Esp%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22p%22%3E%7D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22nx%22%3Efrom%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3E%22%40pnp%2Fsp%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%3B%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22nx%22%3Esp%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Eweb%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Elists%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3EgetByTitle%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%22LookupList%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Eitems%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Eselect%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%22Title%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3E%22Lookup%2FTitle%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3E%22Lookup%2FID%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Eexpand%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%22Lookup%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Eget%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E().%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Ethen%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E((%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Eitems%3C%2FSPAN%3E%3A%20%3CSPAN%20class%3D%22kt%22%3Eany%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%5B%5D)%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22o%22%3E%3D%26gt%3B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22p%22%3E%7B%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22nx%22%3Econsole%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Elog%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22nx%22%3Eitems%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E)%3B%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22p%22%3E%7D)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20implement%20this%20code%20we%20get%20issue%2Ferror%20trying%20to%20query%20underling%20special%20fields%20(Choice%20or%20Lookup%20Fields).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3Bsp.web.lists.getByTitle(%22Vessel%2520Schedule%22).items.select(%22*%2C%20Project%2FTitle%2C%20Project%2FPhase%22).expand(%22Project%22).getAll()%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20throw%20error%2C%20it%20can't%20read%2Fget%20the%20%22Phase%22%20choice%20field%20value%20from%20the%20underlying%20Project%20List.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20assistance%20and%20guidance.%26nbsp%3B%26nbsp%3BThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352494%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20expand%20-%20Lookup%20fields%20(%40pnp%2Fsp%2Fitems)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1615%22%20target%3D%22_blank%22%3E%40Rajashekhar%20Sheelvant%3C%2FA%3E%26nbsp%3B%2C%20thank%20you.%20We%20have%20thought%20about%20this%20as%20last%20resort%2C%20as%20we%20would%20prefer%20a%20more%20simplified%20solution%20without%20use%20of%20Flow%2C%20tweak%26nbsp%3Band%20workload%20for%20this%20type%20of%20simple%20data%20extract.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20there%20is%20other%20ways%2C%26nbsp%3Bconsidering%20performance%20issues%20and%20solution%20maintenance.%20Thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352488%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20expand%20-%20Lookup%20fields%20(%40pnp%2Fsp%2Fitems)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352488%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F284781%22%20target%3D%22_blank%22%3E%40gselvag%3C%2FA%3E%26nbsp%3B%2C%20You%20may%20probably%20store%20this%20value%20in%20a%20hidden%20field.%20Please%20do%20not%20use%20calculated%20column%20for%20this%20but%20you%20can%20use%20designer%20workflow%20or%20MS%20flow.%20and%20you%20can%20try%20fetching%20this%20hidden%20field%20using%20same%20logic.%20Not%20sure%20how%20it%20fits%20your%20requirement%2C%20but%20this%20is%20one%20of%20the%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352472%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20expand%20-%20Lookup%20fields%20(%40pnp%2Fsp%2Fitems)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352472%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1615%22%20target%3D%22_blank%22%3E%40Rajashekhar%20Sheelvant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20feedback.%20We've%20tried%20without%20the%20Lookup%20field%20and%20it%20works%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20workaround%2C%26nbsp%3Bon%20how%20do%20we%20get%20the%20lookup%20value%20into%20our%20list%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352467%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20expand%20-%20Lookup%20fields%20(%40pnp%2Fsp%2Fitems)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352467%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F284781%22%20target%3D%22_blank%22%3E%40gselvag%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%3CP%3Epnpjs%20is%20a%20wrapper%20js%20utility%20for%20sharepoint%20REST%20API%20and%20it%20is%20not%20possible%20to%20fetch%20%3CSTRONG%3Echoice%3C%2FSTRONG%3E%20field%20using%20lookup%20functionality%20of%20REST%20API.%20Can%20you%20try%20excluding%20the%20choice%20field%20and%20check%20if%20your%20query%20works.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi folks,

 

I have two lists (1. Projects, 2. Schedule). Schedule list include a Lookup Field to Projects List.

 

We are trying the (ref. https://pnp.github.io/pnpjs/sp/docs/items/) "Retrieving Lookup Fields" according to guidance:

 

import { sp } from "@pnp/sp";

sp.web.lists.getByTitle("LookupList").items.select("Title", "Lookup/Title", "Lookup/ID").expand("Lookup").get().then((items: any[]) => {
    console.log(items);
});

 

After implement this code we get issue/error trying to query underling special fields (Choice or Lookup Fields).

 

 sp.web.lists.getByTitle("Vessel%20Schedule").items.select("*, Project/Title, Project/Phase").expand("Project").getAll();

 

This throw error, it can't read/get the "Phase" choice field value from the underlying Project List. 

 

Appreciate assistance and guidance.  Thanks!

 

 

 

4 Replies
Highlighted

Hi @gselvag , 

pnpjs is a wrapper js utility for sharepoint REST API and it is not possible to fetch choice field using lookup functionality of REST API. Can you try excluding the choice field and check if your query works.

Highlighted

Hi @Rajashekhar Sheelvant 

Thank you for your feedback. We've tried without the Lookup field and it works fine.

 

Is there a workaround, on how do we get the lookup value into our list?

 

 

Highlighted

@gselvag , You may probably store this value in a hidden field. Please do not use calculated column for this but you can use designer workflow or MS flow. and you can try fetching this hidden field using same logic. Not sure how it fits your requirement, but this is one of the option.

Highlighted

@Rajashekhar Sheelvant , thank you. We have thought about this as last resort, as we would prefer a more simplified solution without use of Flow, tweak and workload for this type of simple data extract.

 

Hope there is other ways, considering performance issues and solution maintenance. Thanks!