Quick question about a Case function

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Hi,

I have a simple numeric formula field that I would like to calculate based the value of another text value field

[LP Contract Length] is a Text - Multiple Choice field.

Case([LP Contract Length],
ToText("Month to Month"), ([Amount] * 0.01),
ToText("1 Year"),([Amount] * 0.02),
ToText("2 Years"),([Amount] * 0.03),
ToText("3 Years"), ([Amount] * 0.04),
null)

For some reason, the formula for the "Month to Month" works, but for the 1 Year, 2 Years, and 3 Years, I get empty values.... I'm sure it's something simple I'm missing...

Thoughts?

Thanks!

Photo of George

George

  • 50 Points

Posted 4 years ago

  • 0
  • 1
Try this and post back if it's still not working

Case([LP Contract Length],
"Month to Month", [Amount] * 0.01,
"1 Year",[Amount] * 0.02,
"2 Years),[Amount] * 0.03,
"3 Years", [Amount] * 0.04)

If that is not working then it's likely that the  1 Year and other choices are not letter perfect matching your case.  I've that is not actually what's in your data.
Photo of George

George

  • 50 Points
Thanks Mark,
I had tried without the ToText function as well, which didn't work.
Interestingly enough, I tinkered more with it, and I got this to work.

 Case([LP Contract Length],
("1"), ([Amount] * 0.01),
("12"),([Amount] * 0.02),
("24"),([Amount] * 0.03),
("36"), ([Amount] * 0.04),
null)

and This to work
Case([LP Contract Length],
("Month to Month"), ([Amount] * 0.01),
("One"),([Amount] * 0.02),
("Two"),([Amount] * 0.03),
("Three"), ([Amount] * 0.04),
null)

Which means, that for some reason, it's not liking a value with a space?... Pretty odd!