Apex Code Development You need to sign in to do that
Don't have an account?
Nichelle Hubley How to shorten this formula? Need to keep adding options
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.
Specifically, is there a way to combine these conditions, because the output is the same for each conditions
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!
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!
I believe the below formula would accomplish the same without having to rewrite so much logic. Please give it a try and let me know how it goes
Good Luck!
All Answers
I believe the below formula would accomplish the same without having to rewrite so much logic. Please give it a try and let me know how it goes
Good Luck!
I will test it later today and report back.
Nichelle
It highlights the first Member_Code__c
I believe the field type for your Member_Code__c field is Text. You can try the below to fix the error:
(1) Change the field type of Member_Code__c to picklist
(2) Remove the TEXT before the Member_Code_c field in your formula
Thanks for your help JSing and Luis