Thursday 31 December 2020 @ 2:39 pm

I had a customer today who wanted to write a formula that assigned a numeric value to each Employee. Normally I would write an IF THEN statement like this:

if {table.EmpNO} = 123 then 10 else
if {table.EmpNO} = 124 then 15 else ...

But the list of employees and values was in a spreadsheet with 4000 employee rows. A linked table wasn’t an option so I decided to do something I don’t do very often. I wrote the formula using SELECT CASE instead of IF THEN.

Normally I prefer to use IF THEN because you can use more complex conditions.  I also find that IF THEN reads more naturally. But in cases like this where you have a single field and LOTS of comparison values, the SELECT CASE pattern is more efficient. The pattern below reduced the total character count by 70% of an equivalent IF THEN structure:

Select {table.EmpNO}
case 123:10
case 124:15
default : 0

The first time I used SELECT CASE was when I had to translate a large spreadsheet grid into a formula. I wanted the formula structure to look like the spreadsheet so we could visually check the formula by comparing the columns. The spreadsheet grid had 30 rows (Types) and 20 columns (Sizes) so my formula ended up with 30 rows that looked like those below, except that each row had 16 more case instances where you see the ellipsis […]:

if {prod.Type}=1 then(select {prod.Size} case 01:03 case 02:03 case 03:05 case 04:06 ... default:00) else
if {prod.Type}=2 then(select {prod.Size} case 01:02 case 02:03 case 03:04 case 04:05 ... default:00) else

Note that I combined an IF THEN to determine the row (Type) with a SELECT CASE to determine the value for each column(Size).  I found this more manageable and easier to validate than 600 additional IF THEN loops.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server