Business Rules 201: Introduction to Lookup Lists, Part 1
Have you ever needed to do any of the following:
- Convert your own category data into a valid value for a marketplace or digital marketing feed?
- Normalise names of brands that may be spelled inconsistently throughout your data?
- Apply different rates of price markups to different Brands/Manufacturers?
- Check a Brand against a list of restricted Brands?
There are a variety of ways to accomplish these using business rules and methods outside the ChannelAdvisor platform, but it’s more work than simply using our lookup lists. Today’s post focuses on creating lookup lists and converting data values.
A lookup list contains two columns of data — the first column represents a value you already have in your data or may exist within your data, and the second column represents the value it should be converted into.
The first step to creating a lookup list is deciding what values need to exist in your list. The second step is actually creating the list by adding those values and their converted values to it. Let’s look at where you’ll go to create or update a lookup list.
One of the most important things to remember when creating a lookup list is that the values in the first column will be case-sensitive, and you cannot duplicate values in lower and uppercase. Later in the blog, we have some tips on how to address this limitation.
Where to Access Lookup List Creation Menus (click to enlarge)
Marketplaces > Pick Any Marketplace > Lookup Lists
Digital Marketing > Marketing Management > Lookup Lists
Step-by-Step Creation of Lookup Lists
Once in the Lookup Lists section of our platform, you may see existing lookup lists in the profile — this is normal as we have some default lists created for a variety of purposes. However, to create a new list:
- Click on the button
- Make sure you name the list in the “Name” field at the top first, as you won’t be able to move forward without one. Add “Name” values (add “Value” column data if you may need to convert the “Name” data) and click the “+ Add” button OR
Paste data from Excel OR
Upload a tab delimited two column file (click image to enlarge)
- Click “Save” or “Save & Exit” to complete the list creation
List Example with Original and Converted Values:
Imagine you want to normalise a list of brand values that are often misspelled into the same value for consistency. Your list will look something like the table below (let’s call this list “Misspelled Brands”):
List with Only Original Value:
Sometimes you don’t need to convert the data — you simply need to find out if a value exists in a list of values. For this example, the goal may be to find out if the brand of your product is in the list of brands you’re restricted from selling (let’s call this list “Restricted Brands”):
Converting Data Values
Once your lookup lists are in place, you can access them through our standard mapping templates or using business rules to convert data.
Inventory / Marketplaces Mapping Templates
The screenshot below is from an inventory template, but the behaviour within the marketplaces templates is almost identical.
- In the first field, you can search for and define the lookup list name you want to use.
- In the second field, you search for and define the name of the field in your data or stored in the ChannelAdvisor system that will contain the data value in the first column of the lookup list.
- The final field allows you to define a backup value when the value in your data doesn’t exist in the lookup list. To set a static value for all products not in the lookup list, just enter it in the default “Text” field; to set the value in the field you used for the lookup, search for it and select it as we did in the example below or leave the default blank by taking no action in this field (click image to enlarge).
Digital Marketing Mapping Templates
Locating and setting lookup lists with Digital Marketing will take a few more mouse clicks, but the same concepts will apply.
Once a field is set and ready to apply a value, instead of choosing an “Inventory Fields” to apply, click on the “Business Rules & Lookup Lists” tab, and in the right column, you can search for a Lookup List. In this example, we’ve searched for “brand” to limit the number of lists. Double-click the name of the list you want and it’ll be applied and highlighted in a yellow box (see the green arrow below).
Then you’ll select the field to apply — you can type it is as long as it’s in the right format, or you can change tabs back to “Inventory Fields” to locate a specific field value, and apply it in the same way for the lookup list name.
Finally, you’ll indicate what field to use as a backup in the event the field value isn’t found on the lookup list noted. As before, you may opt to leave this blank.
Stay tuned for Part 2 on Lookup Lists, where we’ll cover partial lookups, the ISINLIST and the LOOKUP functions so that you may use lookup lists within business rules. For now, make sure to check out the first installation of our ongoing courses on Business Rules here.