Displaying existing record information in a panel when using the peoplepicker to compare record.

The lookup function needs the following info: 

1. List Title: Simply what the list is called. In your case it seems to be called “OnboardingOffboarding”. However written this way, the list must be in the same site as wherever you’re making the lookup call from. This means that if your Form is for a List in Site A, and your OnboardingOffboarding List that you wanna lookup from is in Site B, you need to specify the path to that other site using something like “/sites/SomeSubSite|OnboardingOffboarding” with quotes.
    
2. Column To Filter On: This will be the internal name of the column that you want to use to filter your results. To find the internal name of a column, you’ll need to go to the List Settings, find the column in question, and click on it. Afterwards, look at the URL bar in the browser and the internal column name will be displayed near the end of the URL. Note: If there are no spaces in the column name, then it’s likely going to be a 1:1 match. For instance, if you had a column called “Colors” then the internal name would also be “Colors”, however if you had a column called “Shit Colors” the internal name would look like “Shirt_x0020_Colors” (here is a link with images and instructions: https://www.sharepointdiary.com/2021/01/get-field-internal-name-in-sharepoint-online-using-powershell.html).
   
   Additionally this value will need to be in quotes, just like the List Title!
    
3. Value To Filer On: The value of the column to filter on that will result in a valid filter. This should resolve to a value that is likely to actually exist in the list - for that particular column.
    
4. Output Column: This will represent the Column of information from your List that you want to return from the lookup. This will also use an Internal Column name and should be put inside of quotation marks.

This should leave your lookup function looking something like: 

lookup("OnboardingOffboarding", "PreferredName", EmployeeName, "Manager")

And implies that the List OnboardingOffboarding has a column called “PreferredName” and a column named “Manager”, and the value of the Control called EmployeeName resolves to will be something that exists as a value for the column “PreferredName”

You can also get information like this straight from AD using the userProfileLookup function in a Calculated Control (assuming that it should match whatever is in your list that you’ve populated)

If I have a People Picker called “control_SomePerson”: 

And then create a Calculated Control with the following formula:

userProfileLookup(control_SomePerson, "PreferredName")

where the control_SomePerson is a Name Control reference inserted by double clicking the named control for my people picker in the formula builder

then the results are that it grabs the Preferred Name of whoever I select in the people picker, assuming that they have a Perferred Name in AD that can be synced to my SharePoint environment 

Let me know if you need further help or instructions! 

Hi ​@jalgarra 
Has this response helped to answer your question?

Sorry, I am thoroughly going through and testing a few things before I reply with an answer from Megajerk. I appreciate the thorough answer. 

That is perfectly alright! :) 

​
Okay, I have done this a few ways, all with negative results.

When using the formula:

lookup("OnboardingOffboarding", "PreferredName", EmployeeName, "Manager")

It gives the #value! display when using quotes. However, when taking away the quotes around filter and values:

lookup("OnboardingOffboarding", PreferredName, EmployeeName, Manager)

The #value! goes away, but nothing is displayed. Same error acting like none of those values exist.

Using my original column where I had the “EmployeeName” people picker connected to the “Employee” column looks like this using calculated value for display.

lookup("OnboardingOffboarding", Employee, EmployeeName, Manager)

As with before, the list name is "OnboardingOffboarding." I even used the internal value “%7B827CCDEF-6102-47BB-865F-37C9112FFCA8%7D” with no change. The name is correct because I can use a List Lookup with a filter using that list and it pulls a value depending on what is chosen in the people picker. the column names are correct.

The name of the “Employee” column is correct. The people picker “EmployeeName” is connected to this column when originally adding Onboarding data.

Path in settings for Employee column name.

/_layouts/15/FldEditEx.aspx?List=%7B827CCDEF-6102-47BB-865F-37C9112FFCA8%7D&Field=Employee

Once the form is submitted in that onboarding panel, that person's name and other fields are populated using UserProfile lookups and regular text fields, dates, and lookups. 

This is the “Manager” column name: 

/_layouts/15/FldEditEx.aspx?List=%7B827CCDEF-6102-47BB-865F-37C9112FFCA8%7D&Field=Manager

Another people-picker control, "ManagerName,” is used here to populate the manager's preferred name and is put into the manager column with that associated employee record. 

Those people-picker records in their columns are hyperlinked within the list that take you to that employee's personal SP profile. 
For testing purposes to see what the people picker is actually pulling, I made a calculated value asking to display what the people picker is actually pulling using the formula "EmployeeName”.

Now even though the people picker is set to show “Name” in SP, which it does, but hyperlinked and also shows the PreferedName in the people picker display, it actually is pulling the Account, which is why it is showing i:0#.w|win\368623. That is the Account number.

So this could also be the issue.

I couldn’t get any of that to work. It acts like there is no data to return, due to either the list name or no values in the columns. I even made a column for the Account number that was just text and no dice. 

I thought maybe this was due to the way the people picker was handling what it was actually pulling, I tried doing the calculated value named “PrefName” using the following formula.

userProfileLookup(EmployeeName, "PreferredName")

This calculated value is connected to a whole new column named “PreferredName” that would just return the plain text Preferred Name hoping that would help eliminate any issues that the people picker was actually pulling/displaying for comparison.

Same thing, it doesn’t display anything and gives the error in the web dev console.  

Lookup runtime function is missing valid parameters. Check that the list name, filter column, filter value and return column are specified in the function. Parameters - list: OnboardingOffboarding, filter column: , filter value: i:0#.w|win\368623, return column: 

This is using the calculated value. 

lookup("OnboardingOffboarding", PreferredName, EmployeeName, Manager)

Everyone of those names is correct, and there is data to compare, yet nothing displays.

Notice how it’s still looking for the account instead of the preferred name.

I’m at my wits end. Doing this. this way seems like it doesn't like using the people picker. 

I can get it to work using list lookups, but I don’t want check boxes, radio buttons, etc with the data I need displayed on the Offboarding page when we try to pull up that user record.

​

No suggestions, I guess.