+ Start a Discussion
paulmagpaulmag 

Big formula using IF,AND,OR & PICKLISTS

Looking for some help here as am getting lost in this formula

 

We have 14 territories which are housed in Territory__c (text field)

 

We have 6 types of accounts, there are 3 key ones which are banded "Proactive","Reactive","Head Office" and are housed in Banding__c (picklist)

 

Conditions

If an account is banded "proactive" or "reactive" AND falls in territory 1-6 then it should be Telephone Manager A

If an account is banded "proactive" or "reactive" AND falls in territory 7-14 then it should be Telephone Manager B

If an account is banded "Head Office" then it should be Head Office

Any other banding should be Field Sales

 

I have been working on this a while now and trying to build it up but keep getting stuck with errors.

 

IF(AND(OR( Territory__c ="01",Territory__c ="02",Territory__c ="03",Territory__c ="04",Territory__c ="05",Territory__c ="06"),AND( ISPICKVAL( Banding__c ,"Reactive",),1,

0)))

 

Any help greatly appreciated.

 

Paul

Best Answer chosen by Admin (Salesforce Developers) 
Satya.KonaSatya.Kona

Please let me know if this works..if works mark it as solution, else let me know the errors...i tried on my sandbox and works fine..

 

replace field names

terr__C = territory__C

band__C = Banding__c

 

 

 

CASE(
IF(
AND(
OR(
terr__c ="01",terr__c ="02",terr__c ="03",terr__c ="04",terr__c ="05",terr__c ="06"),
OR(ISPICKVAL( band__c ,"Proactive") , ISPICKVAL( band__c ,"Reactive"))
), 'A',
IF(
AND(
OR(
terr__c ="07",terr__c ="08",terr__c ="09",terr__c ="10",terr__c ="11",terr__c ="12",terr__c ="13",terr__c ="14"),
OR(ISPICKVAL( band__c ,"Proactive") , ISPICKVAL( band__c ,"Reactive"))
),'B',
IF(
ISPICKVAL(band__c, "Head Office"), 'C', 'NONE')
)
),
'A', 'Telephone Manager A',
'B', 'Telephone Manager B',
'C', 'Head Office',
'Field Sales'
)

All Answers

SteveMo__cSteveMo__c

What kind of a result are you trying to return in your formula?  What is the datatype of the Formula field?

Satya.KonaSatya.Kona

Please let me know if this works..if works mark it as solution, else let me know the errors...i tried on my sandbox and works fine..

 

replace field names

terr__C = territory__C

band__C = Banding__c

 

 

 

CASE(
IF(
AND(
OR(
terr__c ="01",terr__c ="02",terr__c ="03",terr__c ="04",terr__c ="05",terr__c ="06"),
OR(ISPICKVAL( band__c ,"Proactive") , ISPICKVAL( band__c ,"Reactive"))
), 'A',
IF(
AND(
OR(
terr__c ="07",terr__c ="08",terr__c ="09",terr__c ="10",terr__c ="11",terr__c ="12",terr__c ="13",terr__c ="14"),
OR(ISPICKVAL( band__c ,"Proactive") , ISPICKVAL( band__c ,"Reactive"))
),'B',
IF(
ISPICKVAL(band__c, "Head Office"), 'C', 'NONE')
)
),
'A', 'Telephone Manager A',
'B', 'Telephone Manager B',
'C', 'Head Office',
'Field Sales'
)

This was selected as the best answer
paulmagpaulmag

Thanks for that, works a treat! :smileyhappy: