541.201.9965 Support_Email
Website
Contents
Display Legacy Contents

Search:
Home > Tools > Lookup Data

Lookup Data

Why would I need it?

In the past, you may have found yourself in a position where you were repeating a rule ... for example, here is a 'set' of fairly typical rules to set the Google_Product_Category:

  • If ParentCategoryID matches 37, then google_product_category=Apparel & Accessories > Clothing Accessories > Belts
  • If ParentCategoryID matches 41, then google_product_category=Apparel & Accessories > Clothing Accessories > Gloves & Mittens
  • If ParentCategoryID matches 29, then google_product_category=Apparel & Accessories > Clothing Accessories > Hats
  • If ParentCategoryID matches 28, then google_product_category=Apparel & Accessories > Clothing > Underwear & Socks > Socks
  • If ParentCategoryID matches 154, then google_product_category=Apparel & Accessories > Clothing > Pants > Casual Pants

Here's another example of 'repeated rules', this time about setting "color"

  • If ProductID equals 1401, or 1402, or 1403, or 1404, then set color=White
  • If ProductID equals 1513, then set color=Red
  • If CategoryID equals 21, then set color=Red
  • If ProductID equals 1421, or 1422, or 1423, then set color=Blue
  • If ProductID equals 1673, or 1674, then set color=Black

Because these rules need to be set up individually they take time AND beyond a certain number of rules the DotFeed platform slows down and becomes difficult to use. Therefore, we have built a 'lookup' to help with repeated rules. More features will likely be added later - this is an 'iteration' which is likely to spark ideas for further iterative versions.

Let's get started!

First, you'll create your spreadsheet. You'll need to note the following restrictions:

  • Microsoft Excel Spreadsheets with the extension “.xls” or “.xlsx” are the only file types accepted.
  • Only the first 10 columns will be imported. Columns will be validated and require a value. All characters except for alpha-numeric characters and spaces will be stripped.M/li>
  • The first row is assumed to be the headers for the spreadsheet, so if you forget to add a 'header row' then your first row of real data will be treated as your headers.
  • Your headers don't need to match anything, but they should be named as a useful reminder of the content of that column. (If you use A, B etc, then you'll be creating more work for yourself later.)
  • The document can be no larger than 10MB.

If we wanted to put the multiple rules (that we defined above) into a spreadsheet, then it could look like this:



For the sake of completeness you should also know that you can compress the spreadsheet and it will work just as well if it looks like this:

Once you've prepared your spreadsheet, you should save it.



Next, we will load the spreadsheet into the DotFeed Portal.

The spreadsheet will be processed on upload and both the spreadsheet and meta-data about it will be saved to the database. The filename of the uploaded spreadsheet will be the display name for the related rule data - just as you did with the column headers, it is a good idea to name the spreadsheet in a way that will remind you what it contains. In the example that we are working through, we will save our spreadsheet with the name products0122.xlsx.

You can find the place to carry out the upload on the feeds listing page above the list and below the tabs.

A simple file upload control accepts the document and the upload is initiated with the button labeled “Upload”. Successful uploads as well as errors in operation will be displayed as a notification above the upload area.

This is the error you will see if you try to upload a .csv file instead of an .xls/.xlsx file:


Once a document has been successfully uploaded, it will be displayed in a list of all Rule Data documents below the upload control and above the feed list.

 

Now that the spreadsheet has been uploaded, we can refer to it within rules, on any channel.

A new rule has always looked like this (below) - the 'input' half of a rule gives you the chance to say "if the data that comes into DotFeed from my storefront, in the field called "ProductID" equals ...

 

Until now, the only options for the input box were:

  • One of two provided tokens - {current} and {original}
  • Manual data entry
With this 'data lookup' feature, we add a third token.

 

When we refer to a piece of data from a spreadsheet, the token looks like { data: “My Spreadsheet Name”, column: “My Column Header” } ... in the example we are working through, our token might be { data: “products0122”, column: “Product ID” }. You need to wrap your spreadsheet name, and your column header in double quotes. You can use as much white space as you like in the token.

We have given you a shortcut to create a token. This is in the form of a dropdown - so instead of carefully typing your token, you can select it from the dropdown.

NOTE: Be careful - when you use the dropdown box and 'click' to select, DotFeed will simply insert your selected value wherever your cursor is. It is easy to send up with concatenated tokens! You have been warned.



Until now, the only way to set the 'color' rules (above) would have been with a long stream of rules:



With this latest feature, we can create the same results with one spreadsheet and one rule:
 

Caveats

There are a few caveats to be aware of:
  • Multiple spreadsheets can be referenced in the criteria of a single rule. Matching rows are tracked independently for each spreadsheet, and each spreadsheet can be referenced in the result.
  • If a spreadsheet is referenced in the result without being referenced in the criteria, it will return a blank.
  • Some fields on a product, such as Category ID can contain multiple values (such as when a product is mapped to multiple categories). When comparing such fields to a spreadsheet, these are always handled as “any one of” rather than “all of”. So, for Category ID, the Equals operator means “If there is any one row that equals any one of the product’s Category ID’s” and the Does not Equal operator means “If there is any one row that does not equal any one of the product’s Category ID’s”. There is no means yet to specify “all of”.
  • If a rule data token is applied to the result of an additive field (a key example being 'product_type'), one element is added for every matching row.
  • If a rule data token is applied to the result of a non-additive field, only the first matching row is used.
  • Spreadsheet rows are evaluated top to bottom. As stated above, non-additive rules take the first matching row. Because of this, it is best to structure spreadsheets from most-specific to least-specific.
  • When criteria are OR’d together, all rows from all criteria are combined. When criteria are AND’d together, only rows found in all of the returned.

Downloading Documents

In the event you lose a spreadsheet you’ve previously uploaded, a download operation has been provided, initiated by the button labeled “Download”. The original document that was uploaded will be streamed from the database down to the browser. Uploaded documents are not modified in any way.

Updating Rule Data

If/when you upload a document with the same name as a previously uploaded document, the system will consider it an update. Columns will be re-evaluated and added or deleted as necessary. Because the system tracks columns by name alone, any columns that were renamed will be treated as an add operation with the new name and a delete operation on the old name.

Any column that existed in the old document that no longer exists in the new document will be deleted. Before deleting old columns, the system will look for rules referencing the column. If any are found, the system will cancel the operation and display a notification to the merchant. If the merchant wants to force the operation without tracking down all of the referencing rules, they can check the checkbox labeled “Delete referenced rules”, telling the system to delete any rules found across the merchant’s feeds that reference the columns being deleted. This operation cannot be undone.

Deleting Rule Data

When a document is no longer used, it can be deleted via the button labeled “Delete” located in the same row as the document. If there are any rules that reference the rule data being deleted, the system will cancel the operation and display a notification to the merchant. If the merchant wants to force the operation without tracking down all of the referencing rules, they can check the checkbox labeled “Delete referenced rules”, telling the system to delete any rules found across the merchant’s feeds that reference the columns being deleted. This operation cannot be undone.



See also