Feed Doctor On Not A Lookup List
Lately my posts have been exceedingly long, and I know nobody wants to read that much, so I’m going to try to keep this one short. This week, “C.” in De Smet, SD writes:
Dear Feed Doctor,
My inventory data doesn’t have a shipping price, but I know what each item’s shipping price SHOULD be: it’s based on the item’s selling price. If the price is less than $20, shipping is $1.99; if the price is more than $20 but less than $50, shipping is $4.99; and for items over $50, shipping is $9.99 . Can I use a lookup list to do this?
Well, C., the answer is: no.
Piecewise Functions
What C. has described above is a “piecewise” function. The way she has described it looks a lot like the things we did with lookup lists—she has a bunch of pairs of values, plus a default—but the problem is that you can only match exactly on the keys of lookup lists. However, C. wants to match on a range of values.
Another way to state C’s problem is this way:
- If price is less than $20 Then return 1.99
- Else If price is between $20 and $50 Then return 4.99
- Else return 9.99
Now, recall last time that we introduced the “If/Then/Else” idea of rules that make decisions (which, as you recall, was If condition Then return value, Else return default value). For C’s problem, we need to extend that to write rules that work like this:
- If condition A Then return value A
- Else If condition B Then return value B
- Else If condition C Then return value C
- Else return default value
…
The SELECTCASE Function
You’re probably not surprised that we have a function that does this, but you’ll probably be surprised that we named the function SELECTCASE. Now, as I said, this works just like the IF function from last week; in fact, you could use SELECTCASE anywhere you have IF, and if you were a masochist, you could use IF anywhere you there’s a SELECTCASE (maybe I’ll show an example of that).
So here’s C’s rule:
SELECTCASE($currentprice<20,1.99,$currentprice<50,4.99,9.99)
Because of the way SELECTCASE works—it goes with the first value that matches a condition that is true—you really only have to specify one end of each price range, and you don’t even have to mention the last one; it’s just the default. One other thing to remember: you can only specify FIVE conditions and associated actions, plus a default. You can have fewer, of course, but five is the upper limit.
Also, your return values don’t have to be just text, numbers, or inventory fields; you can call other functions to compute the return values; in fact, the same is true of IF. With that in mind, here’s how you might implement C’s rule with just IF:
IF($currentprice<20,1.99,IF($currentprice<50,4.99,9.99))
Does this give you an idea of what to do with SELECTCASE if you have more than five conditions? Post your answers in the comments section!