function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
tomc1227tomc1227 

How do I use a lookup field in an IF formula?

We have a custom object lookup field (RF_Reseller__c) in our opportunities that defines what company is handling the opp.  What I want to do is create a formula field that outputs "Direct sale" when the lookup field has our company name in it, and "Indirect sale" when it's any other value (i.e. our resellers).

 

What I've got is

 

IF(CONTAINS(RF_Reseller__c , "ABC Co."), "Direct Sale", "Indirect Sale")
 
I get no syntax errors, but every opp comes back with the false value (Indirect sale), even when the lookup field is "ABC Co." with case-sensitivity taken into account.
 
Any idea how I can get a simple IF like this to return a certain text value based on the value in a lookup field? 
 
 
Message Edited by tomc1227 on  02-05-2009 04:52 PM


Best Answer chosen by Admin (Salesforce Developers) 
JCoppedgeJCoppedge
Sorry Tom, completely misread the Q.
 
You need to use the value from the related list.  Hit the insert field button from the advanced formula editor, point it from the related list to the account name as the field to do the lookup from.  Agency is a custom field (lookup to account) on our opp, and this formula works for me:
 
IF(CONTAINS( Agency__r.Name , "WorkPlace"), "WorkPlace", "Other")
 
Is that what you're looking for?
 


All Answers
JCoppedgeJCoppedge
If the RF_Reseller__c field is a picklist, then you need to enclose "Direct Sale" and "Indirect Sale" in either a CASE() or ISPICKVAL() statement.


Steve :-/Steve :-/
The problem with lookup fields is that the value that is actually stored in them is not the Record Name (Company Name, Person Name, Etc...) it's the Salesforce Record_ID.  So even though you see "ACME Widget" when you are looking at an Account Name through the UI, or Report.  The actual value that is stored in that field is something like "0014000000Mx6aB"


tomc1227tomc1227
Stevemo,
 
I figured that might be the case, and had tried inserting the actual record ID for "ABC Co.", with no luck.  Then again, I was still using CONTAINS, which might only look for a text value not a record ID.  Any idea of how to get it to check for a record ID specifically?
 
Tom
 
P.S. - it is not a picklist, it is a lookup field 


Steve :-/Steve :-/
I *think* that the only way to reference a RecordId in a SF Formula is to do a VLOOKUP (but don't quote me kn that)
 
 
* usually not a good thing 
Message Edited by Stevemo on  02-06-2009 12:21 PM


tomc1227tomc1227
Thanks Stevemo.  According to the documentation, VLOOKUP is only available for validation rules.
 
I'm surprised this is as difficult as it appears to be.  I've got to think lots of people have one field spit out results from another lookup field.  Oh well. 


Steve :-/Steve :-/
You did see my disclaimer above, didn't you?  ;-)


tomc1227tomc1227
Yeah.  You must come from a long lineage of lawyers*.
 
 
* I mean no offense 


JCoppedgeJCoppedge
Sorry Tom, completely misread the Q.
 
You need to use the value from the related list.  Hit the insert field button from the advanced formula editor, point it from the related list to the account name as the field to do the lookup from.  Agency is a custom field (lookup to account) on our opp, and this formula works for me:
 
IF(CONTAINS( Agency__r.Name , "WorkPlace"), "WorkPlace", "Other")
 
Is that what you're looking for?
 
This was selected as the best answer
tomc1227tomc1227
W00t!  That was it, JCoppedge -- I had to use the .Name to pull from the value within the related object. 
 
 
thanks!


JCoppedgeJCoppedge
No prob... FYI you can also pull the ID of related records this way.


ajwajw
I am trying to accomplish something similar with a workflow rule. The Product field is a lookup field on the Opportunity. If the product.name = anything but "Wealthcare BPO" AND the probability stage on the opportunity = 75%, I want to fire off the rule and send out a note to a group of individuals.
 
I have a separate rule that should fire when the product.name = "Wealthcare BPO" and the probability stage=50%.
 
Below is what I've tried based on this post. I get no compilation errors, however my email never fires. Can anyone tell me what I'm doing wrong?
 
AND(IF(CONTAINS(Product__r.Name,"Wealthcare BPO"), TRUE,FALSE)=FALSE, Probability =75)