Error
This content is not currently supported on this browser.
+ Start a Discussion
PerceptiumPerceptium 

Match values in 2 picklist to find the result

 I'm trying to compare values of two picklists to get the right value

 

For example if the cruise type is 'Half Day' and the Reseller is 'INP' the value is $16

 

I am using the following..... (excerpt only)

 

IF(ISPICKVAL(RID__c,"Direct"),(Price_Quoted__c/1.2),
IF ((ISPICKVAL(RID__c,"Acorne")&&(ISPICKVAL(ET__c,"Half Day"))),16,
IF ((ISPICKVAL(RID__c,"Activity Gift/Maximise")&&(ISPICKVAL(ET__c,"Half Day"))),15,
IF ((ISPICKVAL(RID__c,"Activity Gift/Maximise")&&(ISPICKVAL(ET__c,"High Speed"))),20,
IF ((ISPICKVAL(RID__c,"Attraction World")&&(ISPICKVAL(ET__c,"Half Day"))),18,
IF ((ISPICKVAL(RID__c,"Buy a Gift")&&(ISPICKVAL(ET__c,"Half Day"))),12,
IF ((ISPICKVAL(RID__c,"Buy a Gift")&&(ISPICKVAL(ET__c,"Half Day for 2"))),6,
IF ((ISPICKVAL(RID__c,"Buy a Gift")&&(ISPICKVAL(ET__c,"High Speed"))), 7,

etc,etc

 

 

The formula works but there are so many picklist values that it is too big when it compiles

 

I'm trying to find a more effecient way of writing it so that it will compile and have tried 

 

IF (ISPICKVAL (ET__c,"Half Day"),CASE(RID__c,"Acorne",1,"Direct",2,0),
IF ((ISPICKVAL(ET__c, "High Speed"),CASE ((RID__c,"Acorne",1,"Direct",2,0))))

etc...

 

and also

 

CASE(ET__c,"Half Day", CASE(RID__c, "Acorne", 1, "Direct", 2, 0), "High Speed", CASE (RID__c, "Acorne", 5, "Direct", 10, 0)

 

but can't get the syntax right to make them work

 

Any suggestions?

phiberoptikphiberoptik

Do you know how much over the max characters you are? I see a LOT of extra characters in your formula. Mostly just unnecessary parentheses. Also, remove the spaces (I see one after every "IF"). It appears you have about 2 extra characters per line you could remove. You could really lean this formula out that way.

 

You cannot use CASE() in this instance since you have two variables to consider.

PerceptiumPerceptium

Thanks xphiberoptikx for the feedback

 

I've removed the spaces and parentheses and that's reduced it, but I'm still over the limit, and it doesn't allow for any future additions that might come allow. 

 

Not sure if there is another way around it?

Noam.dganiNoam.dgani
Take a look at the case method that might shorten it.
Additionally I noticed that you ask more than once about ET being equal to "half day" -
You can probably merge that and only ask once.
phiberoptikphiberoptik

Noam,

 

I couldnt think of how to get CASE() to work. Can you post a starting example for the OP (and me).

 

Thanks.

Steve MolisSteve Molis

I think you need to come up with a conversion chart for each list of picklist values then you could probably crack this thing with some basic math.  then you could do something kinda like this: 

 

CASE(RID__c,
"Direct", 1,
"Acorne", 2,
"Activity Gift/Maximise", 2.5,
"Attraction World", 3, 
"Buy a Gift",  3.5,
0) 
* 
CASE(ET__c,
"Half Day",5,
"High Speed",10,
0)