Lookups, Associated Columns and PnP

Forms SharePoint Online
1

I have two different but related questions to try to get some help with. They are both related to setting a Lookup field that has Additional Fields with it.

First is a PnP call that goes out and gets the item that is related with the lookup described above. I'm using the "select" and "expand" methods on the query but no matter what I do to the syntax, it doesn't seem to expand the lookup and its associated fields. Example code of what I'm trying to do below. There are 3 lists...Certs, Employees (not people fields) and Audits. I'm adding an item to the Audits list and on that form I'm querying the Certs list that has a lookup in it (with associated columns) that I'm trying to get the info from. But the expand doesn't seem to work. I can see the properties that come back and EmployeeId is the only field filled in.

fd.spBeforeRender(function(ctx) {
     if( $.urlParam('certID') !== null) {
          sp.web.lists.getByTitle('Certs').select('Title, Employee/Title, Employee/EmployeeID').expand('Employee').items.getById($.urlParam('certID')).get().then(function(item)  {
         cert = item;
    	// Set Field Values from Certification Item
         if( cert !== null ) {
            fd.field('Employee').value = cert.EmployeeId;
        }

    });
}
});

Second: when I set the Employee lookup, it works well and the lookup control is updated and displays properly. But the associated field controls related to the Employee lookup (and that are associated columns on the Audit list) don't get updated. Is there a way to get them all to update when I set the controlling Employee lookup (in the Audit list, like I'm doing in the above code segment)?

Thanks in advance

2

I tried a bunch of things to format that post better. You are welcome to edit it for future view-ability if you would like.

3

Hello @officeadmin,

There are typos in pnp request. Please see the corrections below.

sp.web.lists.getByTitle("Certs").items.getById($.urlParam('certID')).select("Title", "Employee/Title", "Employee/ID").expand("Employee").get().then( function (items) {
    console.log(items.Employee);
});

Please try with the correct code.

4

Thank you mnikitina. It wasn't so much a typo but it was that I was doing the Select on items instead of getById. I did find one other issue that might help someone else. I was trying to expand the Employee column. I continued to get errors because I had yet another lookup in the select and wasn't expanding it. In other words, it appears that once you expand one of the lookups, my errors went away once I expanded all of them.

So onto part two of the question...I'm setting the Employee field (a lookup and not a people field) with the id that comes back from the query you just helped me with. On the form, I am also showing the associated field controls (let's use Full Name as an example). When I set the id of the Employee lookup, it doesn't seem to update the associated field control. Is there something I should do to get that to update along with the Employee field?

5

@officeadmin,

Full Name is stored in the same list or somewhere else? Could you please share the screenshot of the source list with the data, so I could have a full picture.

Do you want to populate Employee ID and Full Name on form load?