Datatable Query

Query and fetch specific data entries from a given Data Table

Introduction

This type of query enables users to read data from Data Tables, and then make use of that data inside rules.

There are many possible use-case examples of this, with one of the most common being the retrieval of SKU information for a particular configuration variant.

Let's imagine we have a custom shelf unit configurator, where the customer can pick a number of different columns and shelves to cover a wall. On the manufacturer's back-end, the shelves may be stored either as single packs or as 3-packs, each with their own unique SKU.

SETUP

Here is an example of a data table representing the SKUs for these shelving components. The data table shows the different SKUs for these various options depending on shelf type and color choice.

Data Table Example

We would need to setup the main Shelf Configurator Item with the following workflow:

1. First, the user makes a choice of shelf type with a particular color in the configurator.

2. We need to calculate how many shelf packs are needed, and of which type - If the user chooses 10 shelves, that would result in a total of three Packs of 3 plus a pack of 1. You can store this in PackTotal attributes for each type of bundle.

3. Determine which product was chosen, and which color choice was made through the use of metadata fields on their corresponding items. Use metadata value queries to read those values into String attributes locally.
4. Retrieve the appropriate SKUs for each pack bundle, and store it in an attribute for the front-end. We will focus on the 3Pack in this example, and use the attribute Shelf_3Pack_SKU.

In a real scenario things can get more complex, with multiple shelf type selections.

The front-end will then need to populate an order page with the list of SKUs for all components chosen.

LOGIC

In our example we will focus on the 4th Step listed above.

This is how the query logic might look on the main Shelf Configurator Item, to read the SKU from the data table.

Currently, the datatable query can only be used with attributes of type String, Number, or Asset, even though the UI may display the option for other types as well.

List of Attributes

Create the Datatable Query

Step 1

Create a set attribute value action, and select the Shelf_3Pack_SKU attribute.

Step 2

Choose the datatable query option in the To slot, and pick the Data Table from the list that contains the SKU information shown above.

Step 3

Choose to select which column we want to query. In our case we need to receive the 3 Pack SKU value for a given configuration, so we pick that column.

Step 4

Click the + Add Parameter button to add new configuration entries. In our case, we need to find the SKU for the Product Name + Color Choice variants.

On the left we choose the column name, and on the right we choose which value in that column to search for. The value can only be represented through an attribute, so we make use of the ProductName and ColorName attributes listed above. These attributes would receive their value from metadata on the catalog items.

Datatable queries can only work with attribute values. This means that you have to make use of metadata value queries in order to read the name of the Product, or the name of the Color choice.