• JSing
  • NEWBIE
  • 5 Points
  • Member since 2015

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
    Questions
  • 1
    Replies
Is there a way to combine some of these cases to shorten the formula? I need to keep adding options, but the formula field has reached the character limit.
IF(ISBLANK(Member_Code__c), 
((CASE(TEXT(Annual_Revenue__c), "Up to $100k", 155, "$100k to $1m", 300, "$1m to $5m", 430, "$5m to $10m", 765, "$10m to $20m", 990, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 85, "2", 170, "3", 255, "4", 340, "5", 425, 0))), 

(CASE(Member_Code__c, 

"bmac2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), 

"exce2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), 

"amrs2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), 

"restocan", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), 

/*Add new member codes above*/ 
((CASE(TEXT(Annual_Revenue__c), "Up to $100k", 155, "$100k to $1m", 300, "$1m to $5m", 430, "$5m to $10m", 765, "$10m to $20m", 990, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 85, "2", 170, "3", 255, "4", 340, "5", 425, 0))))))

Specifically, is there a way to combine  these conditions, because the output is the same for each conditions
 
(CASE(Member_Code__c, 

"bmac2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), 

"exce2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), 

"amrs2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), 

"restocan", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) 
+ 
CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)),
So, the complete formula would be:

If the field is blank, use the first set of numbers to calculate,
else if the field is bmac2015 or exce2015 or amrs2015 or restocan, use the second set of numbers to calculate,
if the field is full but is not one of those conditions, use the first set of numbers to calculate.

Is there a better way to approach this formula?

The use case is that when the person puts in one of the correct discount codes, the price is calculated accordingly. Right now, there is only one set of discounted pricing. There is the possibility in the future that there would be another set of discounted pricing, so the formula would look like: 

If the field is blank, use the first set of numbers to calculate,
else if the field is bmac2015 or exce2015 or amrs2015 or restocan, use the second set of numbers to calculate,
else if the field is *some other conditions*, use a third set of numbers to calculate
if the field is full but is not one of those conditions, use the first set of numbers to calculate.

I hope someone can point me in the right direction with this. I appreciate any help.

Thanks!