A while back (good grief, it was nearly 2 years ago!) I got asked a question about using a lookup list to calculate shipping from a table. Back then, I said it couldn’t be done with a lookup list. Then a few days ago when I started thinking of a topic for a blog post, I realized that now that we have the REDUCELIST function, we CAN use a lookup list to do it.

I don’t want to spoil the surprise, so click below to see the whole thing.

Let’s say your shipping table is based on weight:

And of course, the way to calculate shipping is to start at the top of the table and go down until you find the row where your weight is between the numbers in the first two columns. Wait a minute. That table has THREE columns, but our lookup lists only get TWO. Well, let’s just take the last two columns and see what we get:

Now you calculate shipping the same way: start at the top and go down until…Ok, maybe it’s a little different. You start at the top and go down until you find the last row where your weight is less than (or equal to) the number in the first column. Just for fun, let’s tweak it just a little bit more:

Now the way to calculate shipping is to start at the top and go down until you find the last row where your weight is greater than the number in the first column. Note that with this method, as well as the previous one, it’s VERY important that your table’s rows are ordered correctly. So let’s put that shipping table into ShoppingAdvisor as a lookup list and save it. Now, we’ll go back and check the list to make sure it’s ordered correctly:

Wait; what just happened? Well, it turns out that the system will order the lookup list, by “name,” FOR YOU. Only the order is alphabetical. This probably only makes sense if you’re a computer, but in alphabetical order, 10 and 15 come before 5. This might make more sense if you pretend that the numbers 0 through 9 are the letters A through J. So 10 is “BA” and 5 is “F.” OK then. Well, to fix it, you just “zero pad” the numbers in the “name” column, like this:

That seems kind of goofy, but it works. Now we’ve just got to write a business rule that implements the shipping calculation algorithm. If you’ve managed to remember the beginning of the post, you’ll recall that I said we’re going to use REDUCELIST. You may also be wondering what happened to the “0.99″ shipping value. As you can probably guess, that’s going into REDUCELISTS’s “seed value.” To come up with the “mini rule,” let’s walk through how REDUCELIST should operate:

1. Set the intermediate value to seed value 0.99
2. First row of list: “name” is 1, “value” is 1.99. If weight is greater than “name,” set intermediate value to to “value.” Otherwise, leave intermediate value as is.
3. Second row of list: “name” is 5, “value” is 2.99. If weight is greater than “name,” set intermediate value to to “value.” Otherwise, leave intermediate value as is.
4. etc…

Let’s do that one more time, but with @X, @Y, and @Z substituted for “intermediate value,” “name,” and “value.” We’ll also use mathematical symbols to make it a little shorter:

1. Set the @X to seed value 0.99
2. First row of list: @Y is 1, @Z is 1.99. If weight > @Y then @X = @Z. Otherwise, @X = @X
3. First row of list: @Y is 5, @Z is 2.99. If weight > @Y then @X = @Z. Otherwise, @X = @X
4. etc…

Now the “mini rule” should be clear, so let’s go ahead and write the entire thing:
REDUCELIST(FUNCTION(VARS(@X,@Y,@Z),
IF($weight>@Y,@Z,@X)),"ship","0.99")

That’s probably enough for now. Enjoy!

Share This