Auto-populate fields based on People field

  • 30 September 2020
  • 3 replies



I have a People field which when a user selects a name auto populates other fields. Currently the fields are auto-populated using the current formula: userProfileLookup(ID,"FirstName")  which works fine but I need another field called 'Line Manager' to auto-populate but the userProfile doesn't have that field.


So I tried to pull the information in from a list that I know contains that information called 'Starters'. If I use this formula: (lookup("Starters","Full Name",TestEmployeeName ,"Line Manager")) it works, HOWEVER TestEmployeeName is a Single Line Text box which is problematic because how do I validate it? It's very open to user error.

I tried: (lookup("Starters","Full Name",Another Name Test,"Line Manager")  Another Name Test is a People control but at best it doesn't work, at worse it stops a preview being generated.

Any suggestions for a work round? Thanks in advance


Best answer by SimonMuntz 6 October 2020, 04:59

View original

3 replies

Userlevel 5
Badge +17

The issue with the people picker is you first have to figure out what you are sending to the lookup function. Add Another Name Test into a calculated value control and look at the output.
You will probably see the output is something like:

This means that your Full Name column needs to be set the same.
In the Full Name Person/Group column set the Show field: drop down to Account to match the output of the people picker control and it should work.

Thanks @SimonMuntz what I did was add another calculated field called EmployeeNumber. In that field I added the formula: userProfileLookup(Employeesname,"Username") Employeesname is the People field, then in the calculated field called Line Manager I have the formula: parseLookup(lookup("HRSpreadsheet", "PSA ID",EmployeeNumber,"Line Manager")) and it works likes a dream!

@Sutekh can you please help me understand where to include this formula and how list has to be setup in order auto populate username from AD.  Thanks!