SELECT.PERFION.FROMTABLE

Retrieves content from Perfion using the Perfion API Query Syntax. The query is executed based on the input DataTable, and the results are returned in a neutral table format.

Properties

From

Must contain (or reference) a connection string, in one of the following ways:

The name of the Input Data Source

Name of the Output Data Source. The output is a Table format.

Script

The Perfion API Query used to query the Perfion database. The Query will be delimited to items listed in the Input Data Source.
The API Query can also be defined dynamically by including parameter references. The parameter values are evaluated in the same way as the script method FillTemplate.

The Perfion Query needs to follow the XML encoding and chars like ' need to be replaced with ' The method System.Security.SecurityElement.Escape(xmlString) can do the encoding.

Example 1:
<Clause id='ExteriorColor' operator='=' value='{=$Color}' />

If you combine parameters with other curly brackets you need to escape the curly brackets with double curly brackets like:

<Clause id='{{ID' operator='=' value='{=$Color}' />

Example 2:
If you want to create the entire query dynamically, simply just include a reference to the parameter containing the query such as:
{=$MyQuery}

Parameters

@KeyColumn	The column of the input data table to include in the query.
@KeyFeature	The feature in Perfion to query against
@Join	The join type between the input table and the result from the Perfion query. Possible join type: Inner (default), Left
@Filter	Filter the result of the selected data. See Command Maps & Parameters for more information.
@Order	Order the result of the selected data. See Command Maps & Parameters for more information.

Mapping

From	Name of a feature, property or feature + property from Perfion as requested by the Perfion API Query. Here are some examples: Value - The base-value of the current Item ID - The unique Perfion ID of the current Item CreatedDate - The date the current Item was created Brand - The brand of the current Item Vendor - The value of the Vendor feature Vendor.id - The unique Perfion ID of the Vendor Vendor.ModifiedDate - The last modified date of the Vendor EnginePower - The value of the EnginePower feature Image - The unique Guid value of the Image feature The examples above are for illustration purposes only. Please refer to the API Query Reference guide for the complete list of property and attribute names, as well as their relevance for the current item and the supported features of different data types (e.g., string, number, images, files, etc.).
To	Name of the Header as it should be called in the Output table If unspecified it will automatically get its name from the input (e.g. From column).
Script	Note: This Command does not apply scripting. If you want to use scripting in relation to values, then pass the result to the SELECT or SELECT.UNIQUE command.

Guide

This command is designed to simplify querying additional data from Perfion when you already have a data table containing a key for a selectable feature in Perfion. Typical use cases include:

The Item data table from Execute from Grid on Items
The New, Previous or Coalesced table from a Triggered Action
A list of product numbers/identifiers from a remote system

When writing the Perfion Query for this command, no additional steps (such as inserting variables) are required - the command handles this internally.

By specifying which KeyColumn to use in the From data table and which @KeyFeature to match in the API result, you can easily query additional data to your tab. Internally, the command adds an IN-clause to the query to perform the matching.

Important notes

All limitations and behaviors of standard API queries still apply.
Querying by Item ID requires specifying ID as the @KeyFeature.
To query the base value of a string feature, you should use String as the @KeyFeature
The @Join parameter controls whether all rows from the From table are included in the resulting To data table or only rows that have a match in the query results.

Behaviors to be aware of

The first column of the To table will always be the @KeyColumn of the From data table
If a column exist in both the From table and the query results, the result column will replace the From column.
Using *-mapping allows you to include all columns from both the From table and the query results.

Example

The following example retrieves a feature ProductCode and uses this to set the Product base value using a combination of the ProductCode and the Perfion ID.