How do I use a formula field to capture a lookup value - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Connie ManningConnie Manning 

How do I use a formula field to capture a lookup value

How do I create a formula field (for use in reporting) that simply captures the contents of another lookup field of project manager names?

When I do it as a text formula it puts the SF.com ID not name? 

So I simply want to copy name (populated via user lookup) to a formula field & have it contain the same name i.e. John Smith but then will be able to use the new formula field in reports & dashboards
 

Thank you for your comments!

Pierre Despatis-DupontPierre Despatis-Dupont
You should be able to do this with a cross object formula. The blog post below goes over the steps. Let us know if you have any questions or run into any issues. 

http://pdespati.wordpress.com/2012/10/05/cross-object-formulas/
Connie ManningConnie Manning
Thank you for response:

I tried to add TEXT in front of the inserted field but receiving error
Error:  incorrec parameter type for for function "TEXT()" Expected Number, Date, DateTime, Picklist received Text?

Pierre Despatis-DupontPierre Despatis-Dupont
It sounds like you don't need to use TEXT(). If you are trying to pull the name, you can just put the field name in the formula. TEXT() only needs to be used if you need to convert a numeric value into a text string. 

So my first guess would be to try the formula you are using w/o the TEXT() function. 

If that still doesn't work, could you please post the formula you are using and I'll have a look. 
Steve MolisSteve Molis
Can you post a screenshot of the Formula from the Formula Builder screen?  You need to use the Advanced Formula Tab and the Insert Field Button to navigate "through" the Lookup field into the User object and get the First and Last Name fields. 

User-added image
User-added image

Nauman KhanNauman Khan
Assuming that the formula field you are creating is on the same object as lookup field to User object is, then copy and paste the below syntax into your formula box.

User__r.FirstName + " " + User__r.LastName 

Hope it helps!

Pierre Despatis-DupontPierre Despatis-Dupont
What is the formula you are using? Could you copy and paste or post a screenshot as Steve suggested?

Without it it's a bit hard to help :)
Connie ManningConnie Manning
Thank You!!

Once I navigated farther down the tree to get the First + Last works Great!
Nauman KhanNauman Khan
Glad to hear that! Do mark the best answer.. :-)
Steve MolisSteve Molis
Thanks Connie,

Personally I never manually type API Field Names or Functions when I'm writing Formulas in SFDC, I always use the insert buttons so that I know my reference points and spelling are correct.
Pierre Despatis-DupontPierre Despatis-Dupont
+1 Steve. I never type the name either. The field name may be different than the label you are used to seeing, but there are also too many variable that may change the syntax to use (is the field from the current object, from a related object, etc.). It's usually easier to just look for the field in the "Insert Field" window to ensure that you have the proper syntax. 
Anna HendryAnna Hendry
Hi Connie, come on back and let us know by marking the answer as "Best Answer" right under the comment. This will help the rest of the community should they have a similar issue in the future. 

Thanks Pierre, Steve & Nauman for your dedication to this question!
Nauman KhanNauman Khan
No problem, Anna! 
Jenni JacobsJenni Jacobs
Thank you for this. I was able to create the formula from the blog post (and figuring out how to concatenate using &" for a lookup field I needed  My end result formula looked like this. (Account_Manager__r.FirstName&" "&Account_Manager__r.LastName)
Neeharika UpadrastaNeeharika Upadrasta
+1 Steve for the humor (and for the right answer of course).
Joy HanawaJoy Hanawa
Blog post no longer there.
Ryan P.Ryan P.
To expound on the answers here, what I wanted was to pull in the lookup value but to also preserve the link for that lookup value. This can be done using the HYPERLINK formula. HYPERLINK("/" + User__r.Id , User__r.FirstName + " " + User__r.LastName )).

The first paramer just puts a slash and then the User ID--this will point to a URL for your SF instance, appending the User ID, which should create a link to that User. The second part is the display, which simply equals the text value already referenced in a previous answer. (Just adding this in case it helps somebody who finds this thread in their research as I did.)
Amanda WareAmanda Ware
I'm trying to get something similar, but with an additional requirement. I want to capture the name that is populated in a lookup (it's a lookup to lead on a custom object called disclosure) and then keep the value of that name populated in my new field populated, even if the lookup field gets cleared.