You need to sign in to do that
Don't have an account?
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?
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")
All Answers
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
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
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.
Yeah. You must come from a long lineage of lawyers*.
* I mean no offense
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")
W00t! That was it, JCoppedge -- I had to use the .Name to pull from the value within the related object.
thanks!
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)