Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
DevLaura

# Is there such a thing as a OR(CASE formula field?

I'm trying to write a formula that basically says that if the values of two different fields are equal to something, then certain values exist for one field. I'm creating a new formula field for those values and here's my (sad) attempt at a formula. How can I write this?

OR(CASE(Number_of_Trees__c ,
1, "A tree",
2, "Two trees",
3, "Three trees",
4, "Four trees",
5, "A grove of five trees",
6, "A grove of six trees",
7, "A grove of eight trees",
9, "A grove of nine trees",
10, "A grove of ten trees",
11, "A grove of eleven trees",
12, "A grove of twelve trees",
13, "A grove of thirteen trees",
14, "A grove of fourteen trees",
15, "A grove of fifteen trees",
16, "A grove of sixteen trees",
17, "A grove of seventeen trees",
18, "A grove of eighteen trees",
19, "A grove of nineteen trees",
20, "A grove of twenty trees",
21, "A grove of 21 trees",
22, "A grove of 22 trees",
23, "A grove of 23 trees",
24, "A grove of 24 trees",
25, "A grove of 25 trees",
26, "A grove of 26 trees",
27, "A grove of 27 trees",
28, "A grove of 28 trees",
29, "A grove of 29 trees",
30, "A grove of thirty trees","",
(CASE(Other_Number_of_Trees__c
1, "A tree",
2, "Two trees",
3, "Three trees",
4, "Four trees",
5, "A grove of five trees",
6, "A grove of six trees",
7, "A grove of eight trees",
9, "A grove of nine trees",
10, "A grove of ten trees",
11, "A grove of eleven trees",
12, "A grove of twelve trees",
13, "A grove of thirteen trees",
14, "A grove of fourteen trees",
15, "A grove of fifteen trees",
16, "A grove of sixteen trees",
17, "A grove of seventeen trees",
18, "A grove of eighteen trees",
19, "A grove of nineteen trees",
20, "A grove of twenty trees",
21, "A grove of 21 trees",
22, "A grove of 22 trees",
23, "A grove of 23 trees",
24, "A grove of 24 trees",
25, "A grove of 25 trees",
26, "A grove of 26 trees",
27, "A grove of 27 trees",
28, "A grove of 28 trees",
29, "A grove of 29 trees",
30, "A grove of thirty trees",
31, "A grove of 31 trees",
32, "A grove of 32 trees",
33, "A grove of 33 trees",
34, "A grove of 34 trees",
35, "A grove of 35 trees",
36, "A grove of 36 trees",
37, "A grove of 37 trees",
38, "A grove of 38 trees",
39, "A grove of 39 trees",
40, "A grove of forty trees",
41, "A grove of 41 trees",
42, "A grove of 42 trees",
43, "A grove of 43 trees",
44, "A grove of 44 trees",
45, "A grove of 45 trees",
46, "A grove of 46 trees",
47, "A grove of 47 trees",
48, "A grove of 48 trees",
49, "A grove of 49 trees",
50, "A grove of fifty trees",
51, "A grove of 51 trees",
52, "A grove of 52 trees",
53, "A grove of 53 trees",
54, "A grove of 54 trees",
55, "A grove of 55 trees",
56, "A grove of 56 trees",
57, "A grove of 57 trees",
58, "A grove of 58 trees",
59, "A grove of 59 trees",
60, "A grove of sixty trees",
61, "A grove of 61 trees",
62, "A grove of 62 trees",
63, "A grove of 63 trees",
64, "A grove of 64 trees",
65, "A grove of 65 trees",
66, "A grove of 66 trees",
67, "A grove of 67 trees",
68, "A grove of 68 trees",
69, "A grove of 69 trees",
70, "A grove of seventy trees",
71, "A grove of 71 trees",
72, "A grove of 72 trees",
73, "A grove of 73 trees",
74, "A grove of 74 trees",
75, "A grove of 75 trees",
76, "A grove of 76 trees",
77, "A grove of 77 trees",
78, "A grove of 78 trees",
79, "A grove of 79 trees",
80, "A grove of eighty trees",
81, "A grove of 81 trees",
82, "A grove of 82 trees",
83, "A grove of 83 trees",
84, "A grove of 84 trees",
85, "A grove of 85 trees",
86, "A grove of 86 trees",
87, "A grove of 87 trees",
88, "A grove of 88 trees",
89, "A grove of 89 trees",
90, "A grove of ninety trees",
91, "A grove of 91 trees",
92, "A grove of 92 trees",
93, "A grove of 93 trees",
94, "A grove of 94 trees",
95, "A grove of 95 trees",
96, "A grove of 96 trees",
97, "A grove of 97 trees",
98, "A grove of 98 trees",
99, "A grove of 99 trees",
100, "A grove of 100 trees",
"")

William Tran
use this:

Thx

CASE( Value(Text(Number_of_Trees__c))  + VALUE(Text(Other_Number_of_Trees__c)),
1, "A tree",
2, "Two trees",
3, "Three trees",
4, "Four trees",
5, "A grove of five trees",
6, "A grove of six trees",
7, "A grove of eight trees",
9, "A grove of nine trees",
10, "A grove of ten trees",
11, "A grove of eleven trees",
12, "A grove of twelve trees",
13, "A grove of thirteen trees",
14, "A grove of fourteen trees",
15, "A grove of fifteen trees",
16, "A grove of sixteen trees",
17, "A grove of seventeen trees",
18, "A grove of eighteen trees",
19, "A grove of nineteen trees",
20, "A grove of twenty trees",
21, "A grove of 21 trees",
22, "A grove of 22 trees",
23, "A grove of 23 trees",
24, "A grove of 24 trees",
25, "A grove of 25 trees",
26, "A grove of 26 trees",
27, "A grove of 27 trees",
28, "A grove of 28 trees",
29, "A grove of 29 trees",
30, "A grove of thirty trees",
31, "A grove of 31 trees",
32, "A grove of 32 trees",
33, "A grove of 33 trees",
34, "A grove of 34 trees",
35, "A grove of 35 trees",
36, "A grove of 36 trees",
37, "A grove of 37 trees",
38, "A grove of 38 trees",
39, "A grove of 39 trees",
40, "A grove of forty trees",
41, "A grove of 41 trees",
42, "A grove of 42 trees",
43, "A grove of 43 trees",
44, "A grove of 44 trees",
45, "A grove of 45 trees",
46, "A grove of 46 trees",
47, "A grove of 47 trees",
48, "A grove of 48 trees",
49, "A grove of 49 trees",
50, "A grove of fifty trees",
51, "A grove of 51 trees",
52, "A grove of 52 trees",
53, "A grove of 53 trees",
54, "A grove of 54 trees",
55, "A grove of 55 trees",
56, "A grove of 56 trees",
57, "A grove of 57 trees",
58, "A grove of 58 trees",
59, "A grove of 59 trees",
60, "A grove of sixty trees",
61, "A grove of 61 trees",
62, "A grove of 62 trees",
63, "A grove of 63 trees",
64, "A grove of 64 trees",
65, "A grove of 65 trees",
66, "A grove of 66 trees",
67, "A grove of 67 trees",
68, "A grove of 68 trees",
69, "A grove of 69 trees",
70, "A grove of seventy trees",
71, "A grove of 71 trees",
72, "A grove of 72 trees",
73, "A grove of 73 trees",
74, "A grove of 74 trees",
75, "A grove of 75 trees",
76, "A grove of 76 trees",
77, "A grove of 77 trees",
78, "A grove of 78 trees",
79, "A grove of 79 trees",
80, "A grove of eighty trees",
81, "A grove of 81 trees",
82, "A grove of 82 trees",
83, "A grove of 83 trees",
84, "A grove of 84 trees",
85, "A grove of 85 trees",
86, "A grove of 86 trees",
87, "A grove of 87 trees",
88, "A grove of 88 trees",
89, "A grove of 89 trees",
90, "A grove of ninety trees",
91, "A grove of 91 trees",
92, "A grove of 92 trees",
93, "A grove of 93 trees",
94, "A grove of 94 trees",
95, "A grove of 95 trees",
96, "A grove of 96 trees",
97, "A grove of 97 trees",
98, "A grove of 98 trees",
99, "A grove of 99 trees",
100, "A grove of 100 trees",
"")

William Tran
Not sure what you want to do:

if Number_of_Trees__c =1

and

Other_Number_of_Trees__c=2

What are you expecting as a result?

Number_of_Trees__c can only go up to 30 while Other_Number_of_Trees__c can go up to 100?

Thx
DevLaura
I know it seems confusing and redundant... I'm actually just covering all bases. The information will flow from our website into Salesforce. On our website, the end user will have the option to choose Number of Trees 1, 2, 3, 4, 5, and then in increments of 10 (10, 20, 30). Let's call this field, FIELD A. They will also have the option to choose Other Number of Trees, so they could input 6 or 11 and any other number they decide (up to 95). Let's call that field, FIELD B. They can only choose to enter into one field - either FIELD A or FIELD B. For the third field (the field that will contain a CASE or IF formula or FIELD C), I am including all number possibilities in case FIELD A gets changed to include more numbers and because they can enter any number into FIELD B. FIELD C - through the use of a mail merge app - gets merged into cards or certificates. So, it is printed on a card or certificate and needs to have language on it that will be reflected on the card. So, if someone input the number "9" into FIELD B because they want to dedicate a grove of 9 trees to someone, it would then show up on FIELD C as "A grove of 9 trees" and then with the use of other fields (and our mail merge app) show, for example, "A grove of 9 trees has been dedicated to Jane Doe in honor of her graduation". I'm trying to get both FIELD A and FIELD B to show in FIELD C. If that is not possible, I might just create 2 fields (a FIELD C and FIELD D) and use IF formulas. Two different fields will require more complex merging/formatting through our mail merge app, however. Let me know the best solution if you can and thanks!
William Tran
Just use this:

Assuming if not chosen the value is 0.

Otherwise default it to zero or use function value (Number_of_Trees__c) or .... (there are many ways to turn it to zero if empty/blanks/nulls etc.)

Thanks

CASE(Number_of_Trees__c  + Other_Number_of_Trees__c
1, "A tree",
2, "Two trees",
3, "Three trees",
4, "Four trees",
5, "A grove of five trees",
6, "A grove of six trees",
7, "A grove of eight trees",
9, "A grove of nine trees",
10, "A grove of ten trees",
11, "A grove of eleven trees",
12, "A grove of twelve trees",
13, "A grove of thirteen trees",
14, "A grove of fourteen trees",
15, "A grove of fifteen trees",
16, "A grove of sixteen trees",
17, "A grove of seventeen trees",
18, "A grove of eighteen trees",
19, "A grove of nineteen trees",
20, "A grove of twenty trees",
21, "A grove of 21 trees",
22, "A grove of 22 trees",
23, "A grove of 23 trees",
24, "A grove of 24 trees",
25, "A grove of 25 trees",
26, "A grove of 26 trees",
27, "A grove of 27 trees",
28, "A grove of 28 trees",
29, "A grove of 29 trees",
30, "A grove of thirty trees",
31, "A grove of 31 trees",
32, "A grove of 32 trees",
33, "A grove of 33 trees",
34, "A grove of 34 trees",
35, "A grove of 35 trees",
36, "A grove of 36 trees",
37, "A grove of 37 trees",
38, "A grove of 38 trees",
39, "A grove of 39 trees",
40, "A grove of forty trees",
41, "A grove of 41 trees",
42, "A grove of 42 trees",
43, "A grove of 43 trees",
44, "A grove of 44 trees",
45, "A grove of 45 trees",
46, "A grove of 46 trees",
47, "A grove of 47 trees",
48, "A grove of 48 trees",
49, "A grove of 49 trees",
50, "A grove of fifty trees",
51, "A grove of 51 trees",
52, "A grove of 52 trees",
53, "A grove of 53 trees",
54, "A grove of 54 trees",
55, "A grove of 55 trees",
56, "A grove of 56 trees",
57, "A grove of 57 trees",
58, "A grove of 58 trees",
59, "A grove of 59 trees",
60, "A grove of sixty trees",
61, "A grove of 61 trees",
62, "A grove of 62 trees",
63, "A grove of 63 trees",
64, "A grove of 64 trees",
65, "A grove of 65 trees",
66, "A grove of 66 trees",
67, "A grove of 67 trees",
68, "A grove of 68 trees",
69, "A grove of 69 trees",
70, "A grove of seventy trees",
71, "A grove of 71 trees",
72, "A grove of 72 trees",
73, "A grove of 73 trees",
74, "A grove of 74 trees",
75, "A grove of 75 trees",
76, "A grove of 76 trees",
77, "A grove of 77 trees",
78, "A grove of 78 trees",
79, "A grove of 79 trees",
80, "A grove of eighty trees",
81, "A grove of 81 trees",
82, "A grove of 82 trees",
83, "A grove of 83 trees",
84, "A grove of 84 trees",
85, "A grove of 85 trees",
86, "A grove of 86 trees",
87, "A grove of 87 trees",
88, "A grove of 88 trees",
89, "A grove of 89 trees",
90, "A grove of ninety trees",
91, "A grove of 91 trees",
92, "A grove of 92 trees",
93, "A grove of 93 trees",
94, "A grove of 94 trees",
95, "A grove of 95 trees",
96, "A grove of 96 trees",
97, "A grove of 97 trees",
98, "A grove of 98 trees",
99, "A grove of 99 trees",
100, "A grove of 100 trees",
"")
DevLaura
Thank you so much for your response. Will the formula add those two fields (Number_of_Trees__c  and Other_Number_of_Trees__c)
together? I'm trying to get it to choose the number listed on either Number_of_Trees__c  or Other_Number_of_Trees__c and just return the value of one of them (the one that's completed) into the one field. Can you confirm that this is what that formula will do? Many, many thanks again...
DevLaura
Or, maybe that's unnecessary because only one field will have a number in it anyway...
DevLaura
Actually - I tried this again and it's still not working. I think it's not working because the fields,
Number_of_Trees__c  and Other_Number_of_Trees__c, are picklists. How can I write that around a CASE formula? I tried using TEXT before the field names but I still get an error indicating the following: Error: Syntax error. Missing ')'
William Tran
use this:

Thx

CASE( Value(Text(Number_of_Trees__c))  + VALUE(Text(Other_Number_of_Trees__c)),
1, "A tree",
2, "Two trees",
3, "Three trees",
4, "Four trees",
5, "A grove of five trees",
6, "A grove of six trees",
7, "A grove of eight trees",
9, "A grove of nine trees",
10, "A grove of ten trees",
11, "A grove of eleven trees",
12, "A grove of twelve trees",
13, "A grove of thirteen trees",
14, "A grove of fourteen trees",
15, "A grove of fifteen trees",
16, "A grove of sixteen trees",
17, "A grove of seventeen trees",
18, "A grove of eighteen trees",
19, "A grove of nineteen trees",
20, "A grove of twenty trees",
21, "A grove of 21 trees",
22, "A grove of 22 trees",
23, "A grove of 23 trees",
24, "A grove of 24 trees",
25, "A grove of 25 trees",
26, "A grove of 26 trees",
27, "A grove of 27 trees",
28, "A grove of 28 trees",
29, "A grove of 29 trees",
30, "A grove of thirty trees",
31, "A grove of 31 trees",
32, "A grove of 32 trees",
33, "A grove of 33 trees",
34, "A grove of 34 trees",
35, "A grove of 35 trees",
36, "A grove of 36 trees",
37, "A grove of 37 trees",
38, "A grove of 38 trees",
39, "A grove of 39 trees",
40, "A grove of forty trees",
41, "A grove of 41 trees",
42, "A grove of 42 trees",
43, "A grove of 43 trees",
44, "A grove of 44 trees",
45, "A grove of 45 trees",
46, "A grove of 46 trees",
47, "A grove of 47 trees",
48, "A grove of 48 trees",
49, "A grove of 49 trees",
50, "A grove of fifty trees",
51, "A grove of 51 trees",
52, "A grove of 52 trees",
53, "A grove of 53 trees",
54, "A grove of 54 trees",
55, "A grove of 55 trees",
56, "A grove of 56 trees",
57, "A grove of 57 trees",
58, "A grove of 58 trees",
59, "A grove of 59 trees",
60, "A grove of sixty trees",
61, "A grove of 61 trees",
62, "A grove of 62 trees",
63, "A grove of 63 trees",
64, "A grove of 64 trees",
65, "A grove of 65 trees",
66, "A grove of 66 trees",
67, "A grove of 67 trees",
68, "A grove of 68 trees",
69, "A grove of 69 trees",
70, "A grove of seventy trees",
71, "A grove of 71 trees",
72, "A grove of 72 trees",
73, "A grove of 73 trees",
74, "A grove of 74 trees",
75, "A grove of 75 trees",
76, "A grove of 76 trees",
77, "A grove of 77 trees",
78, "A grove of 78 trees",
79, "A grove of 79 trees",
80, "A grove of eighty trees",
81, "A grove of 81 trees",
82, "A grove of 82 trees",
83, "A grove of 83 trees",
84, "A grove of 84 trees",
85, "A grove of 85 trees",
86, "A grove of 86 trees",
87, "A grove of 87 trees",
88, "A grove of 88 trees",
89, "A grove of 89 trees",
90, "A grove of ninety trees",
91, "A grove of 91 trees",
92, "A grove of 92 trees",
93, "A grove of 93 trees",
94, "A grove of 94 trees",
95, "A grove of 95 trees",
96, "A grove of 96 trees",
97, "A grove of 97 trees",
98, "A grove of 98 trees",
99, "A grove of 99 trees",
100, "A grove of 100 trees",
"")

This was selected as the best answer
DevLaura
The formula received no errors! Still have to test it, but I think I'm on the right track right now. Really appreciate your help,
William Tran!