Now that you know how to create lookup lists and map them within templates from Part 1, the second part of this topic will cover the use of lookup lists in business rules by looking at the functions available. We’ll also tackle more advanced concepts around partial value lookups which offer greater flexibility when you need to locate values within strings of data.
Using Lookup Lists in Business Rules
LOOKUP is a function which allows you to convert data values using a lookup list by referencing a field. The output of the rule is the corresponding value in the lookup list in the “Value” column (when one exists). You can extend the functionality by nesting other business functions within the primary LOOKUP rule. For our example, let’s look at the “Misspelled Brands” lookup list we referenced in Part 1 of this blog:
When to use: when you need to convert a data value into something else.
Caution: values in the first column of the lookup list you are referencing are case sensitive, so “nike” is not the same as “Nike”.
Example: LOOKUP( “List Name”, Value )
- LOOKUP( “Misspelled Brands” , $brand ) will locate the brand of the product being referenced (“Auropostale”) and convert it to the normalized/correctly named value
- “Auropostale” will be converted to “Aeropostale”
- “Nike” will not be converted to anything because it isn’t in the “Name” column of the list.
Pro Tip: Ensure your source data is in the same format as the lookup list name column values. Imagine the “Misspelled Brands” list was all lowercase values – in this case, you could still use the same rule with an additional function to set your data values to all lowercase to ensure they would match:
LOOKUP( “Misspelled Brands” , TOLOWER($brand) )
Nest other functions in the place of the value in the LOOKUP function. The below example will look up the first non-blank field value between $brand and $manufacturer in the order displayed. It also applies the lowercase logic to the value. Just remember to close each function with the appropriate number of parenthesis!
LOOKUP( “Misspelled Brands” , TOLOWER( IFBLANK( $brand, $manufacturer ) ) )
Checking Against a List of Values
ISINLIST is a function that allows you to evaluate your field against those in a lookup list in the “Name” column. The output of the rule is ‘TRUE’ or ‘FALSE’. You can extend the functionality by nesting other business functions within the primary LOOKUP rule. Both of these functions will allow you to evaluate blank data, but they have slightly different purposes.
For our example, let’s look to the “Restricted Brands” lookup list we referenced in Part 1 of this blog:
When to Use: when you need to determine if a single field is blank or if a function generates a blank.
Caution: values in the first column of the lookup list you are referencing are case sensitive, so “nike” is not the same as “Nike”.
Example: LOOKUP( “List Name”, Value )
- LOOKUP( “Restricted Brands” , $brand ) will locate the brand of the product being referenced to determine if it’s in the “Restricted Brands” list.
- $brand = “Birkenstock”
- “TRUE” is returned because $brand data exists in the lookup list in the Name data points.
- $brand = “Aeropostale”
- “FALSE” is returned because the $brand data doesn’t exist in the Name data points of the lookup list.
Advanced Concepts: Partial Lookups within Business Rules
Partial Lookups
“Partial” can be added as a third parameter in both LOOKUP and ISINLIST functions to allow for partial lookups. The partial concept will look up the values in the first column of the lookup list in the field value you define. It’s the opposite of a typical lookup where you are looking for your field value in the first column of the list.
When to Use: when you have a list of keywords that help define something about an item – such as the product type – but those keywords exist within the title of the product, and a 1-to-1 lookup won’t find the value because the title is too long.
Caution: parts of words that exist within other values may be located first – if a list of jewelry types is defined (“earring”, “ring”, “bracelet”, “necklace”) then “ring” may be located for “ring” or “earring”.
Example: LOOKUP( “List Name”, $fieldname, “partial” )
LOOKUP( “Sport Type”, $title, “partial” ) will locate values in the “Sport Type” list in the product’s title and convert the partial match to the corresponding value in the lookup list.
“Sport Type” list:
$title value: “Boys Blue/Black Baseball Cleats Size 10”
- Output of the Rule: “Baseball”
$title value: “Girls Gymnastics Shoes Size 4”
- Output of the Rule: null/blank/no value, because none of the values in the “Name” data points exist anywhere in the title.
Example: ISINLIST( “List Name”, $fieldname, “partial” )
ISINLIST( “Excluded Sports”, $title, “partial” ) will locate values in the “Excluded Sports” list in the product’s title and return a value of “TRUE” when one of the values from the list is found in the title. Notice that no “Value” is needed in this case since we are not converting the data or need to output a specific value.
“Excluded Sports” list:
$title value: “Red Unisex Boxing Gloves Large”
- Output of the Rule: “TRUE”
$title value: “Girls Hockey Skates Size 6”
- Output of the Rule: “FALSE” because none of the values in the “Name” data points exist anywhere in the title.
Reverse Partial Lookups
“Reversepartial” can be added as a third parameter in both LOOKUP and ISINLIST functions to allow for reverse partial lookups. The reverse partial concept will attempt to locate the value identified in your field within the values in the Name column of the lookup list. This is more like what you might expect to happen with a partial lookup.
When to Use: this is a little less commonly used, but may be helpful when your field value
Example: LOOKUP( “List Name”, $fieldname, “reversepartial” )
LOOKUP( “Shoe Brands”, $brand, “reversepartial” ) will locate values in the $brand field in the “Shoe Brands” and return the corresponding “Value” field data if located uniquely.
“Shoe Brands” list:
$brand value: “Nike”
- Output of the Rule: “Cross Training”, because “Nike” was found uniquely in one row where “Nike Cross Training” existed, and the corresponding Value field data is “Cross Training”.
$brand value: “ke”
- Output of the Rule: null/blank/no value, because “ke” exists in both “Nike Cross Training” and “Birkenstock Casual” Name values, and the system cannot choose between them on your behalf.
$brand value: “Crocs”
- Output of the Rule: null/blank/no value, because “Crocs” doesn’t exist in the Name data points of the lookup list.
Example: ISINLIST( “List Name”, $fieldname, “reversepartial” )
ISINLIST( “Shoe Brands”, $brand, “reversepartial” ) will locate values in the $brand field in the“Shoe Brands” and return a “TRUE” or “FALSE” if the value exists.
$brand value: “Nike”
- Output of the Rule: “TRUE”, because “Nike” was found uniquely in one row where “Nike Cross Training” existed.
$brand value: “ke”
- Output of the Rule: “TRUE”, because even though it exists in the list in two different Name data points, the ISINLIST functionality doesn’t consider the number of times it exists.
$brand value: “Crocs”
- Output of the Rule: “FALSE”, because none of the values in the Name data points contain the string “Crocs”.
Pro Tip: LOOKUP and ISINLIST also support “regex” and “reverseregex” as third parameters in the event you know Regular Expressions and want to implement them in reference to Lookup Lists.
Read more about these options on our Strategy and Support Center here and be sure to read the rest of our series on business rules in Business Rules 101.
