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
AceldamaAceldama 

Having trouble calculating a formula field from two picklists

Hi all!  Having trouble with a formula field that is being calculated from the values in two other fields.  I've only gotten through one half of this request and already up to 3k characters, so no way I can get this to work.

Field 3 is calculated from the values present in Field 1 and 2.  Fields 1 and 2 are picklists.  The requirement is that we calculate the value for Field 3 as follows:

If Field 1 = 1,2,3,4,5 and Field 2 =1,2,3,4,5  then Field 3 = Value 1
If Field 1 = 1,2,3,4,5 and Field 2 =6,7,8,9,10 then Field 3 = Value 1
If Field 1 = 6,7,8,9, or 10 and Field 2 =1,2,3,4 5 then Field 3 = Value 1
If Field 1 = 6,7,8,9, or 10 and Field 2 =6,7,8,9,10 then Field 3 = Value 1

Here is what I was doing:
IF(AND(ISPICKVAL(Field 1,"1"),ISPICKVAL(Field 2,"1")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"1"),ISPICKVAL(Field 2,"2")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"1"),ISPICKVAL(Field 2,"3")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"1"),ISPICKVAL(Field 2,"4")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"1"),ISPICKVAL(Field 2,"5")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"2"),ISPICKVAL(Field 2,"1")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"2"),ISPICKVAL(Field 2,"2")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"2"),ISPICKVAL(Field 2,"3")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"2"),ISPICKVAL(Field 2,"4")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"2"),ISPICKVAL(Field 2,"5")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"3"),ISPICKVAL(Field 2,"1")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"3"),ISPICKVAL(Field 2,"2")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"3"),ISPICKVAL(Field 2,"3")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"3"),ISPICKVAL(Field 2,"4")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"3"),ISPICKVAL(Field 2,"5")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"4"),ISPICKVAL(Field 2,"1")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"4"),ISPICKVAL(Field 2,"2")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"4"),ISPICKVAL(Field 2,"3")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"4"),ISPICKVAL(Field 2,"4")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"4"),ISPICKVAL(Field 2,"5")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"5"),ISPICKVAL(Field 2,"1")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"5"),ISPICKVAL(Field 2,"2")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"5"),ISPICKVAL(Field 2,"3")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"5"),ISPICKVAL(Field 2,"4")),"Value 1", 
IF(AND(ISPICKVAL(Field 1,"5"),ISPICKVAL(Field 2,"5")),"Value 1", 
"Not Defined" 
)))))))))))))))))))))))))
Shashikant SharmaShashikant Sharma
If your both picklist has only 10 options and for the field 3 values possible values could only be "Value 1" or Not Defined then. You could do

1. Check if Both Picklist F1 and F2 are not null then Make F3 as Value 1
2. If any of them is Null then Not Defined

If above ddoes not satisfy your requriement then this is going to exceed limit for sure and will be a pain in any case to maintain.

For this I would suggest you to go for a trigger, which is going to be very small as sets could be created for 1,2,3,4,5 and 6,7,8,9, 10 making value 3 a Text Field. Make it read only or create another Formula field to utilze value in text value3 field. 

Thanks
Shashikant

 
AceldamaAceldama
Solved it!  I bundled up the pick lists as OR statements:
IF(AND(
OR(
ISPICKVAL(Field 1,"1"),
ISPICKVAL(Field 1,"2"),
ISPICKVAL(Field 1,"3"),
ISPICKVAL(Field 1,"4"),
ISPICKVAL(Field 1,"5")),
OR(
ISPICKVAL(Field 2,"1"),
ISPICKVAL(Field 2,"2"),
ISPICKVAL(Field 2,"3"),
ISPICKVAL(Field 2,"4"),
ISPICKVAL(Field 2,"5"))),
“Value 1,
Check #2
Check #3
Check #4
"Undefined")

The entire formula was under 3K characters.