CASE formula with an AND function inside?

All,
I hit a character limit in a formula field due to too many IF statements. Therefore I changed it to a CASE statement, but I still have 16 conditionals within the formula that need to return a different accounting number depening on the Product the user chooses. Is it possible to have an AND function within a CASE function?

CASE({!Category__c},
'Advisory Council - Non-sales', '800-114',
'Advisory Council - Sales', '800-113',
'Advisor forum travel - Non-sales', '800-111',
'Advisor forum travel - Sales', '800-112',
'Advisor Road Show', '800-119',
'Advisor Road Show - travel', '800-119',
'Airfare - Non-sales', '800-003',
'Business Meals and Entertainment - Non-sales', '800-003',
'Car Service / Taxi - Non-sales', '800-003',
'Charitable Contributions', '900-800',
'Conference travel - Non-sales', '800-003',
'Copying and Printing', '700-590',
'Courier Services', '700-620',
'Due Diligence', '850-971',
'Employee Meetings', '800-195',
'Gifts - sales', '900-910',
'Gifts - non-sales', '900-910',
'Industry conferences - non-sales', '700-585',
'Internet', '700-637',
'IT Services', '700-575',
'Lodging - Non-sales', '800-003',
'Marketing', '700-579',
'Membership Dues', '700-580',
'Mileage - Non-sales', '800-003',
'Misc. Travel Expenses - Non-sales', '800-003',
'NRS Sales Meetings', '800-004',
'NRS Sales Meetings travel - Non-sales', '800-004',
'NRS Sales Meetings travel - Sales', '800-004',
'Office Maintenance and Repairs', '700-495',
'Office Supplies', '700-540',
'Personal Charges', '900-900',
'Personal Meals', '800-120',
'Phone', '700-639',
'Postage', '700-620',
'Promotional items - sales', '900-705',
'Promotional items - non-sales', '900-705',
'Publications', '700-580',
'Registration Costs', '850-500',
'Managed Company OPEX', '120-515',
'Managed Company O&O', '120-513',
'Managed Company direct investment expense', '120-160',
'Computer Supplies', '700-560',
'Computer Software', '700-570',
'Computer Support', '700-575',
'Airfare - Sales' && TEXT(Product__c) = 'NS HI', '800-001',
'Airfare - Sales' && TEXT(Product__c) = 'NS II', '800-002',
'Business Meals and Entertainment - Sales' && TEXT(Product__c) = 'NS HI', '800-001',
'Business Meals and Entertainment - Sales' && TEXT(Product__c) = 'NS II', '800-002',
'Car Service / Taxi - Sales' && TEXT(Product__c) = 'NS HI', '800-001',
'Car Service / Taxi - Sales' && TEXT(Product__c) = 'NS II', '800-002',
'Conference travel - Sales' && TEXT(Product__c) = 'NS HI', '800-001',
'Conference travel - Sales' && TEXT(Product__c) = 'NS II', '800-002',
'Hosted events' && TEXT(Product__c) = 'NS HI', '800-191',
'Hosted events' && TEXT(Product__c) = 'NS II', '800-192',
'Lodging - Sales' && TEXT(Product__c) = 'NS HI', '800-001',
'Lodging - Sales' && TEXT(Product__c) = 'NS II', '800-002',
'Mileage - Sales' && TEXT(Product__c) = 'NS HI', '800-001',
'Mileage - Sales' &&TEXT(Product__c) = 'NS II', '800-002',
'Misc. Travel Expenses - Sales' && TEXT(Product__c) = 'NS HI', '800-001',
'Misc. Travel Expenses - Sales' && TEXT(Product__c) = 'NS II', '800-002',
null
)

October 7, 2014
·
Like
0
·
Follow
1

Best Answer chosen by SeanCeno

SeanCeno
I had to add TEXT in front the Category__c field in the IF statements. That may be why I've gone over the character limit. It's strange because Category__c and Product__c are just picklist fields, not formula.

CASE({!Category__c},
'Advisory Council - Non-sales', '800-114',
'Advisory Council - Sales', '800-113',
'Advisor forum travel - Non-sales', '800-111',
'Advisor forum travel - Sales', '800-112',
'Advisor Road Show', '800-119',
'Advisor Road Show - travel', '800-119',
'Airfare - Non-sales', '800-003',
'Business Meals and Entertainment - Non-sales', '800-003',
'Car Service / Taxi - Non-sales', '800-003',
'Charitable Contributions', '900-800',
'Conference travel - Non-sales', '800-003',
'Copying and Printing', '700-590',
'Courier Services', '700-620',
'Due Diligence', '850-971',
'Employee Meetings', '800-195',
'Gifts - sales', '900-910',
'Gifts - non-sales', '900-910',
'Industry conferences - non-sales', '700-585',
'Internet', '700-637',
'IT Services', '700-575',
'Lodging - Non-sales', '800-003',
'Marketing', '700-579',
'Membership Dues', '700-580',
'Mileage - Non-sales', '800-003',
'Misc. Travel Expenses - Non-sales', '800-003',
'NRS Sales Meetings', '800-004',
'NRS Sales Meetings travel - Non-sales', '800-004',
'NRS Sales Meetings travel - Sales', '800-004',
'Office Maintenance and Repairs', '700-495',
'Office Supplies', '700-540',
'Personal Charges', '900-900',
'Personal Meals', '800-120',
'Phone', '700-639',
'Postage', '700-620',
'Promotional items - sales', '900-705',
'Promotional items - non-sales', '900-705',
'Publications', '700-580',
'Registration Costs', '850-500',
'Managed Company OPEX', '120-515',
'Managed Company O&O', '120-513',
'Managed Company direct investment expense', '120-160',
'Computer Supplies', '700-560',
'Computer Software', '700-570',
'Computer Support', '700-575',
Case(1,
If(TEXT(Category__c) ='Airfare - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',
If(TEXT(Category__c) ='Airfare - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',
If(TEXT(Category__c) ='Business Meals and Entertainment - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',
If(TEXT(Category__c) ='Business Meals and Entertainment - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',
If(TEXT(Category__c) ='Car Service / Taxi - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',
If(TEXT(Category__c) ='Car Service / Taxi - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',
If(TEXT(Category__c) ='Conference travel - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',
If(TEXT(Category__c) ='Conference travel - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',
If(TEXT(Category__c) ='Hosted events' && TEXT(Product__c) = 'NS HI',1,0), '800-191',
If(TEXT(Category__c) ='Hosted events' && TEXT(Product__c) = 'NS II',1,0), '800-192',
If(TEXT(Category__c) ='Lodging - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',
If(TEXT(Category__c) ='Lodging - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',
If(TEXT(Category__c) ='Mileage - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',
If(TEXT(Category__c) ='Mileage - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',
If(TEXT(Category__c) ='Misc. Travel Expenses - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',
If(TEXT(Category__c) ='Misc. Travel Expenses - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',
null
))

October 8, 2014
·
Like
0
·
Dislike
0

Blessy Voola 4
Hi Sean

You can try this way

CASE({!Category__c},

'Advisory Council - Non-sales', '800-114',

'Advisory Council - Sales', '800-113',

'Advisor forum travel - Non-sales', '800-111',

'Advisor forum travel - Sales', '800-112',

'Advisor Road Show', '800-119',

'Advisor Road Show - travel', '800-119',

'Airfare - Non-sales', '800-003',

'Business Meals and Entertainment - Non-sales', '800-003',

'Car Service / Taxi - Non-sales', '800-003',

'Charitable Contributions', '900-800',

'Conference travel - Non-sales', '800-003',

'Copying and Printing', '700-590',

'Courier Services', '700-620',

'Due Diligence', '850-971',

'Employee Meetings', '800-195',

'Gifts - sales', '900-910',

'Gifts - non-sales', '900-910',

'Industry conferences - non-sales', '700-585',

'Internet', '700-637',

'IT Services', '700-575',

'Lodging - Non-sales', '800-003',

'Marketing', '700-579',

'Membership Dues', '700-580',

'Mileage - Non-sales', '800-003',

'Misc. Travel Expenses - Non-sales', '800-003',

'NRS Sales Meetings', '800-004',

'NRS Sales Meetings travel - Non-sales', '800-004',

'NRS Sales Meetings travel - Sales', '800-004',

'Office Maintenance and Repairs', '700-495',

'Office Supplies', '700-540',

'Personal Charges', '900-900',

'Personal Meals', '800-120',

'Phone', '700-639',

'Postage', '700-620',

'Promotional items - sales', '900-705',

'Promotional items - non-sales', '900-705',

'Publications', '700-580',

'Registration Costs', '850-500',

'Managed Company OPEX', '120-515',

'Managed Company O&O', '120-513',

'Managed Company direct investment expense', '120-160',

'Computer Supplies', '700-560',

'Computer Software', '700-570',

'Computer Support', '700-575',

Case(1,

If(Category__c =' Category__c Airfare - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',

If(Category__c ='Airfare - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',

If(Category__c ='Business Meals and Entertainment - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',

If(Category__c ='Business Meals and Entertainment - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',

If(Category__c ='Car Service / Taxi - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',

If(Category__c ='Car Service / Taxi - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',

If(Category__c ='Conference travel - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',

If(Category__c ='Conference travel - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',

If(Category__c ='Hosted events' && TEXT(Product__c) = 'NS HI',1,0), '800-191',

If(Category__c ='Hosted events' && TEXT(Product__c) = 'NS II',1,0), '800-192',

If(Category__c ='Lodging - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',

If(Category__c ='Lodging - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',

If(Category__c ='Mileage - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',

If(Category__c ='Mileage - Sales' &&TEXT(Product__c) = 'NS II',1,0), '800-002',

If(Category__c ='Misc. Travel Expenses - Sales' && TEXT(Product__c) = 'NS HI',1,0), '800-001',

If(Category__c ='Misc. Travel Expenses - Sales' && TEXT(Product__c) = 'NS II',1,0), '800-002',

null

)

Hope this helps!

Thanks
Blessy Voola

October 7, 2014
·
Like
1
·
Dislike
0

Pratik (Salesforce Developers)
Hi SeanCeno,

You can refer to salesforce document which will help you to solve your issue by reducing the formula size.
https://help.salesforce.com/help/pdfs/en/salesforce_formula_size_tipsheet.pdf

Thanks,
Pratik

October 8, 2014
·
Like
1
·
Dislike
0

SeanCeno
Blessy,

I like your idea, but unfortunately the formula field is now giving my the error:
Error: Compiled formula is too big to execute (5,312 characters). Maximum size is 5,000 characters.

Is it possuble to shrink this any further or I do I just need to add it to my apex code? I could make a trigger, it just makes editing less efficient for changes in the future.

October 8, 2014
·
Like
0
·
Dislike
0

This was selected as the best answer

Blessy Voola 4
I tried to shrink, please try this and should work now.

CASE(category__c,
'Advisory Council - Non-sales', '800-114',
'Advisory Council - Sales', '800-113',
'Advisor forum travel - Non-sales', '800-111',
'Advisor forum travel - Sales', '800-112',
'Advisor Road Show', '800-119',
'Advisor Road Show - travel', '800-119',
'Airfare - Non-sales', '800-003',
'Business Meals and Entertainment - Non-sales', '800-003',
'Car Service / Taxi - Non-sales', '800-003',
'Charitable Contributions', '900-800',
'Conference travel - Non-sales', '800-003',
'Copying and Printing', '700-590',
'Courier Services', '700-620',
'Due Diligence', '850-971',
'Employee Meetings', '800-195',
'Gifts - sales', '900-910',
'Gifts - non-sales', '900-910',
'Industry conferences - non-sales', '700-585',
'Internet', '700-637',
'IT Services', '700-575',
'Lodging - Non-sales', '800-003',
'Marketing', '700-579',
'Membership Dues', '700-580',
'Mileage - Non-sales', '800-003',
'Misc. Travel Expenses - Non-sales', '800-003',
'NRS Sales Meetings', '800-004',
'NRS Sales Meetings travel - Non-sales', '800-004',
'NRS Sales Meetings travel - Sales', '800-004',
'Office Maintenance and Repairs', '700-495',
'Office Supplies', '700-540',
'Personal Charges', '900-900',
'Personal Meals', '800-120',
'Phone', '700-639',
'Postage', '700-620',
'Promotional items - sales', '900-705',
'Promotional items - non-sales', '900-705',
'Publications', '700-580',
'Registration Costs', '850-500',
'Managed Company OPEX', '120-515',
'Managed Company O&O', '120-513',
'Managed Company direct investment expense', '120-160',
'Computer Supplies', '700-560',
'Computer Software', '700-570',
'Computer Support', '700-575',
Case(1,
If(or((TEXT(category__c) ='Airfare - Sales' && TEXT(product__c) = 'NS HI'),(TEXT(category__c) ='Business Meals and Entertainment - Sales' && TEXT(product__c) = 'NS HI'),(TEXT(category__c) ='Car Service / Taxi - Sales' && TEXT(product__c) = 'NS HI'),(TEXT(category__c) ='Conference travel - Sales' && TEXT(product__c) = 'NS HI'),(TEXT(category__c) ='Lodging - Sales' && TEXT(product__c) = 'NS HI'),(TEXT(category__c) ='Mileage - Sales' && TEXT(product__c) = 'NS HI'),(TEXT(category__c) ='Misc. Travel Expenses - Sales' && TEXT(product__c) = 'NS HI')),1,0), '800-001',
If(or((TEXT(category__c) ='Airfare - Sales' && TEXT(product__c) = 'NS II'),(TEXT(category__c) ='Business Meals and Entertainment - Sales' && TEXT(product__c) = 'NS II'),(TEXT(category__c) ='Car Service / Taxi - Sales' && TEXT(product__c) = 'NS II'),
(TEXT(category__c) ='Conference travel - Sales' && TEXT(product__c) = 'NS II'),(TEXT(category__c) ='Lodging - Sales' && TEXT(product__c) = 'NS II'),(TEXT(category__c) ='Mileage - Sales' && TEXT(product__c) = 'NS II'),(TEXT(category__c) ='Misc. Travel Expenses - Sales' && TEXT(product__c) = 'NS II'))
,1,0), '800-002',
If(TEXT(category__c) ='Hosted events' && TEXT(product__c) = 'NS HI',1,0), '800-191',
If(TEXT(category__c) ='Hosted events' && TEXT(product__c) = 'NS II',1,0), '800-192',
null
))

Thanks!

Please mark best once this is resolved.

October 8, 2014
·
Like
1
·
Dislike
0

SeanCeno
Thanks Blessy! I added this to a field update within a workflow rule and it's working great. Thanks again!

October 9, 2014
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

CASE formula with an AND function inside?

All Answers

You need to sign in to do that.