Apex Code Development You need to sign in to do that
Don't have an account?
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!
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!
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
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.
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