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
Kristina G.Kristina G. 

Help with Formula Field

I'm trying to apply some changes to a formula field and can't seem to get it to work. This is my first time working with anything like this, so I'm a little outside my comfort zone. Any help or suggestions are appreciated. 

Basically, I have a formula field that returns "Yes" or "No" based on the parameters below:

IF(
OR(
ROUND(TODAY()-At_Firm_Since__c,0)>=2000,
AND(
ABS(ROUND(Broker_since__c-At_Firm_Since__c,0))<180,
ROUND(TODAY()-Broker_since__c,0)<=1825
),
ISBLANK(Broker_since__c),
ISBLANK(At_Firm_Since__c),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2007,2,9)),
AND(CONTAINS(Employing_Firm__c,"Raymond James"),At_Firm_Since__c=DATE(2013,2,13)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,4,24)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,7,10)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,7,31)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,8,21)),
AND(CONTAINS(Employing_Firm__c,"Wells Fargo"),At_Firm_Since__c=DATE(2007,10,11)),
AND(CONTAINS(Employing_Firm__c,"Wells Fargo"),At_Firm_Since__c=DATE(2008,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,2,20)),
AND(CONTAINS(Employing_Firm__c,"Morgan Stanley Smith Barney"),At_Firm_Since__c=DATE(2009,6,1)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2009,9,18)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2009,9,19)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2010,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,10,23)),
AND(CONTAINS(Employing_Firm__c,"RBC"),At_Firm_Since__c=DATE(2009,03,13)),
AND(CONTAINS(Employing_Firm__c,"RBC"),At_Firm_Since__c=DATE(2009,10,09)),
AND(CONTAINS(Employing_Firm__c,"Barclays")),
AND(CONTAINS(Employing_Firm__c,"J.P. Morgan"))
),
"Yes","No")

All I want to do is include something that will return "No" if the "Employing Firm" field contains "Edward Jones". Is there any way to include that in here? I feel like it should be an easy thing to add, but I'm not having any luck over here. 

I really appreciate any help someone can provide. Thanks!
Best Answer chosen by Kristina G.
Chris ShadeChris Shade
Kristina,

I think I understand so the formula you gave was pre-existing and you just want to add the Edward Jones part (and if it is Edward Jones output No)?

If so I think this should work::


IF(CONTAINS(Employing_Firm__c,"Edward Jones"),"No",
IF(
OR(
ROUND(TODAY()-At_Firm_Since__c,0)>=2000,
AND(
ABS(ROUND(Broker_since__c-At_Firm_Since__c,0))<180,
ROUND(TODAY()-Broker_since__c,0)<=1825
),
ISBLANK(Broker_since__c),
ISBLANK(At_Firm_Since__c),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2007,2,9)),
AND(CONTAINS(Employing_Firm__c,"Raymond James"),At_Firm_Since__c=DATE(2013,2,13)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,4,24)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,7,10)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,7,31)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,8,21)),
AND(CONTAINS(Employing_Firm__c,"Wells Fargo"),At_Firm_Since__c=DATE(2007,10,11)),
AND(CONTAINS(Employing_Firm__c,"Wells Fargo"),At_Firm_Since__c=DATE(2008,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,2,20)),
AND(CONTAINS(Employing_Firm__c,"Morgan Stanley Smith Barney"),At_Firm_Since__c=DATE(2009,6,1)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2009,9,18)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2009,9,19)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2010,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,10,23)),
AND(CONTAINS(Employing_Firm__c,"RBC"),At_Firm_Since__c=DATE(2009,03,13)),
AND(CONTAINS(Employing_Firm__c,"RBC"),At_Firm_Since__c=DATE(2009,10,09)),
AND(CONTAINS(Employing_Firm__c,"Barclays")),
AND(CONTAINS(Employing_Firm__c,"J.P. Morgan"))
),
"Yes","No"))


I just add one initial if Statement that first check to see if the employing firm is Edward Jone and if that one criteria is true it says 'No.'  Elsewise, it goes through the original formula.

Chris

if this was helpful, hit the Like link.

All Answers

Chris ShadeChris Shade
Kristina,

I think I understand so the formula you gave was pre-existing and you just want to add the Edward Jones part (and if it is Edward Jones output No)?

If so I think this should work::


IF(CONTAINS(Employing_Firm__c,"Edward Jones"),"No",
IF(
OR(
ROUND(TODAY()-At_Firm_Since__c,0)>=2000,
AND(
ABS(ROUND(Broker_since__c-At_Firm_Since__c,0))<180,
ROUND(TODAY()-Broker_since__c,0)<=1825
),
ISBLANK(Broker_since__c),
ISBLANK(At_Firm_Since__c),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2007,2,9)),
AND(CONTAINS(Employing_Firm__c,"Raymond James"),At_Firm_Since__c=DATE(2013,2,13)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,4,24)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,7,10)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,7,31)),
AND(CONTAINS(Employing_Firm__c,"Stifel"),At_Firm_Since__c=DATE(2007,8,21)),
AND(CONTAINS(Employing_Firm__c,"Wells Fargo"),At_Firm_Since__c=DATE(2007,10,11)),
AND(CONTAINS(Employing_Firm__c,"Wells Fargo"),At_Firm_Since__c=DATE(2008,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,2,20)),
AND(CONTAINS(Employing_Firm__c,"Morgan Stanley Smith Barney"),At_Firm_Since__c=DATE(2009,6,1)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2009,9,18)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2009,9,19)),
AND(CONTAINS(Employing_Firm__c,"UBS"),At_Firm_Since__c=DATE(2010,1,1)),
AND(CONTAINS(Employing_Firm__c,"Merrill Lynch"),At_Firm_Since__c=DATE(2009,10,23)),
AND(CONTAINS(Employing_Firm__c,"RBC"),At_Firm_Since__c=DATE(2009,03,13)),
AND(CONTAINS(Employing_Firm__c,"RBC"),At_Firm_Since__c=DATE(2009,10,09)),
AND(CONTAINS(Employing_Firm__c,"Barclays")),
AND(CONTAINS(Employing_Firm__c,"J.P. Morgan"))
),
"Yes","No"))


I just add one initial if Statement that first check to see if the employing firm is Edward Jone and if that one criteria is true it says 'No.'  Elsewise, it goes through the original formula.

Chris

if this was helpful, hit the Like link.

This was selected as the best answer
Kristina G.Kristina G.
Chris, 

Thank you for the response, I really appreciate the help. I cannot get the syntax to check out with that though. I've tried all of the combinations I can think of and I keep getting a extra ',' or extra ')' or extra 'IF'. Do you see anything that I could change to help with that? Is it possible to have two IF statements in a since formula field? Thanks again Chris!

Kristina
Chris ShadeChris Shade
I pasted it into a formula field and the syntax was excepted.  What error did it give you exactly?
Kristina G.Kristina G.
I had only copied the top area where you added that additional IF statement. I didn't see the extra ) at the bottom there. It's working now and you are a life saver! Thank you so much for your help, you have no idea what a relief it is to finally have this working. 
Kristina G.Kristina G.
Ok, one more question. If I wanted to exclude any additional firms along with Edward Jones, how would that look? The names of the firms I would like to exclude are "D.A. Davidson" and "Robert W. Baird". Again, I really appreciate the help!