Calculated value lookup and userProfileLookup on returned value does not work

[marksawers]

I'd like to get an email address of a person returned from a sharepoint lookup in a calculated value. The column in the sharepoint list is a person, not a string, but it is returned with prefixed apparently with some index semicolon and hash, and then a Last Name, First Name.  

Firstly, parseLookup() does not work on the forth argument to lookup. Only

on Lookup List value types. Second, userProfileLookup() wants a real person field, not some string.

Ideally I could do something like:

lookup("Projects", "Title", parseLookup(Standard), userProfileLookup("Project Manager", "WorkEmail"))

Instead I have to suffer with something quite ugly like this just to get the Last Name, First Name

substring(lookup("Projects", "Title", parseLookup(Standard), "Project Manager"), 4, length(lookup("Projects", "Title", parseLookup(Standard), "Project Manager")))

And even this is shaky as the index/id could be 1-x digits, not just 2.

Has anyone done this? (Without creating another column in the source list with the desired info)

[eharris04]

Can I recommend using the userProfile lookup function available within the calculated control. You can possible have the lookup control provide the name and then reference that as part of the formula to get the email address or just get the email address directly.

[marksawers]

Are you suggesting the following?

• creating a new form field with type Person
• populating it with the lookup name value (FYI it is in the form last name, first name)
• Running the userProfileLookup on that Person field  

[johnfriday13]

yes I could use the answer to this as well, what is the exact syntax to get the userProfileLookup based on another field / @eharris04 how would you structure it?

[dweyrick · Accepted solution]

I just added this to a form myself this morning;  I have a calculated value field that uses the userProfileLookup function to display the Job Title of the person entered into the Employee field on the form (which is a People field).   The configuration for this is as follows...

Here is how it appears in the properties of the Calculated Value control:

Here is the Calculated value field working in the form:

Hope this helps.

[goel_shobhit]

The internal property name you mentioned is the properties of oser profie as"PreferredName","Manager" etc.not the internal name of the list