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)
Solved! Go to Solution.
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.
Are you suggesting the following?
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.