Example: Cluster Query

Owned by Thomas Olthuis
Aug 30, 2024
2 min read

Cluster type queries are useful when you want to return multiple rows of data per item. Whereas Simple queries can only return a single row, cluster queries supports having multiple rows of data and dynamically returning columns.

The most common use cases for Cluster type queries are:

  1. Fetching product/variant structures

  1. Fetching complex price information from ERP

  1. Calculate or concatenate complex product information

Cluster type queries very well with Reports to generate complex and dynamic Report content.

How to setup Remote Query

In the Administration menu select Remote Queries, right-click and select Add

image-20240830-093004.png

 

First define a name for your new Remote Query, then select where you want to select data from by select a Remote Connection from the dropdown. Then switch to Cluster in the Remote Query Type dropdown.

image-20240830-093117.png

 

Now you can define your query. For more complex queries, we recommend using the Setup XML functionality in the right-hand. For simple queries, it is enough to define a SELECT statement.

For Simple remote queries, you need to specify a KEYVALUE field in your SELECT statement. This is a requirement for Perfion to be able to relate the incoming data with items in Perfion.

In Cluster type queries the same requirement is applied and we also need to specify a VARIANT field. For the full details about Cluster queries see the Remote Query - Perfion Knowledge Base - Perfion Knowledge Base (atlassian.net) article.

When we’re satisfied with the query click Save to save and close the window.

Create Feature For The Cluster Query

With the Remote Query ready, we can now create a feature to store the query result in.

The feature should be a String, non-localizable and not inheritable. In the Query dropdown in the Remote section, select the Query we just created.

For cluster queries it does not matter which column you select as the Data Field.

Now we add the new Feature to the Configuration of Product.

 

Cluster queries can not be displayed in the Grid, they can only be displayed in the Item Editor where we will see the result as a non-editable table of values.

Â