I know it’s been a while. At the rate I’m going, I probably won’t write another blog post until Thanksgiving! Also, just a warning: the new function I’m writing about today won’t ship until our next release. I just can’t wait to write about it!
As a software developer, I find that one of the great things about working here is that there are lots of taste-testers for the dog food I create. In fact, our services team often comes up with great ideas for new functions.
The other day, Dan in the UK asked me for help writing a business rule for the following problem. He’s got a lookup list of colors:
| name |
value |
| black |
Black |
| white |
White |
| blue |
Blue |
| red |
Red |
Just as an aside, notice how the “value” column is “proper-cased.” That is, the first letter is capitalized. The “name” column is case-insensitive, so a lookup will match any casing and output the proper-cased form. But that’s not the story. What Dan needed was a rule that would look for all these colors in his items’ descriptions and output a comma separated list of the colors that were found:
| Item Description |
Rule Output |
| Big red thing with blue spots |
Blue, Red |
| Shiny red toy with white and blue stripes |
White, Blue, Red |
| Blue-black crayon |
Black, Blue |
The bad news is: you can’t really do this with a lookup list, so I wound up writing an extremely large rule that was a bunch of REGEXREPLACE functions all jammed together. It worked, but it was ugly. And there’s nothing that a software guy hates more than ugly code.
A few days later, Vanessa here in RTP came by for help with this situation: she’s got item titles with “accented” characters, such as á and é, that she wanted to replace with their un-accented version. She was hoping to create a lookup list, like this:
You may recall that I wrote about a function called “REPLACEWORDLIST” that will replace entire words like this, but it won’t work on characters that are inside of words. Again, I suggested she use a bunch of REPLACE functions.
This got me to thinking: the whole reason I came up with REPLACEWORDLIST in the first place was that Mark asked if he could use a lookup list as an input to the REDUCE function. Since he couldn’t, I wrote a whole new custom function to solve his problem.
Now, however, I’m starting to recognize a pattern. Users are essentially asking for the ability to “loop over” lookup lists, as REPLACEWORDLIST does, but to do something besides replace words. In hindsight, this should have been obvious; Mark originally asked for a version of REDUCE that would loop over a lookup list instead of an array of child-item attributes. So, I’m finally creating it.
The new function is called REDUCELIST. The syntax looks like this:
REDUCELIST(f(x,y,z), list name, seed value)
This is a lot like REDUCE, except the “mini-rule”–f(x,y,z)–has three parameters instead of two. The first (”x”) is still the “intermediate” value, but the 2nd and 3rd, “y” and “z,” are the “name” and “value” from each row in the lookup list, in turn. Here’s how it might work on Dan’s problem I mentioned above. Let’s say our description is “What’s black and white and red all over?” and that the lookup list is called “color list.” Here’s the rule:
REDUCELIST(FUNCTION(VARS(@X,@Y,@Z),
IF(CONTAINS($offerdescription,@Y),JOIN(",",@X,@Z),@X)),"color lookup","")
The key to the whole thing is the “mini-rule:”
IF(CONTAINS($offerdescription,@Y),JOIN(",",@X,@Z),@X)
Remember the way this works: We take each row in the lookup list and evaluate this mini-rule. “X” is the intermediate value–it starts out as the “seed value” we pass in (which is blank), and gets set to the output of the mini-rule each time we evaluate it. “Y” and “Z” are the “name” and “value” columns from the row of the lookup list. Here we go:
- X=”" (initial value), Y=”black” Z=”Black”, output = “,Black”
- X=”,Black” Y=white Z=”White”, output = “,Black,White”
- X=”,Black,White” Y=”blue” Z=”Blue”, output = “,Black,White” (note: unchanged since blue wasn’t in the description)
- X=”,Black,White” Y=”red” Z=”Red”, output = “,Black,White,Red”
Pretty close! As usual, we can put a REGEXREPLACE around the whole thing to strip off that leading comma.
Now, as a software developer, I love creating things that are general-purpose; writing code that can only be used for one thing is like…well, it’s cheating. So I’m really happy with this new function. The downside is: it’s a little bit harder to use. This is a tension that’s always going to exist, I think. The good news is, I’m always on the lookout for patterns, and if we can find a pattern of use with REDUCELIST, there’s a good chance we’ll wrap it up in its own function—just like we did with REPLACEWORDLIST, even though it technically came first.
One final note: if you’re coming to our Catalyst event, stop by one of the “Ask the Expert” roundtables and say hello. Hope to see you there!
Share This
