The other day I got an email from Allyson Thomas in our UK office, asking for some help writing a business rule:
What I want to do is look at field A and see if the text within it contains one of the words in my lookup list. Then if one of the words is contained, I want to return a certain value for that word. The scenario is that field A is a description which includes a brand I need to find out which brand it includes and then based on the brand, add a suffix to my Product URL So I’ll have a lookup list with brand in column A and the suffix for the URL in column B. Can you do an IF CONTAINS with a lookup and then return a matching value?
This is a lot like the LOOKUP function, except LOOKUP requires that the keys in the list match the input text EXACTLY. However, there is a way to “loop” over each row in the list, using the REDUCELIST function. Then you can do just about anything you want with the data in the list. Here’s what I wrote for her:
REDUCELIST(FUNCTION(VARS(@X,@Y,@Z), IF(CONTAINS($description,@Y),@Z,@X)), Brand Lookup”, “”)
Then Allyson hit me with this gold-medal winning idea:
This question about a rule like this has actually come up a few times recently, is there any chance that this would become a function in its own right?
So I decided to modify both LOOKUP and ISINLIST to support the idea of “partial” matches. For this example, let’s say we have the following description: “This is my description.” And we have a list called “my list” that contains this data:
| Name | Value |
| Is | was |
If your rule is ISINLIST(“my list”,$description), that returns false, and LOOKUP(“my list”,$description) returns blank. But now you can write ISINLIST(“my list”,$description,”partial”), which returns true, and LOOKUP(“my list”,$description, “partial”) which returns “was”. You can also use “regex” instead of “partial,” in which case the data in the lookup list is treated as a regular expression, and if that regex matches your input text, then ISINLIST will return true and LOOKUP will return the list value.
This new functionality will ship with our next software update in mid-August.
Blogpost by Anthony Alford, The Feed Doctor
