Use Lookup Transformation Templates
Released in Jet Analytics Data Integration 6024.1.
This article describes how to use Lookup Transformation Templates and in which scenarios they may be preferable to conditional lookup fields.
The Lookup Transformation Template allows multiple lookups to be created using a variable join field and one or more fixed join fields. Rather than creating multiple conditional lookup fields manually, the template is created once and can then be applied repeatedly for different lookups. This saves time and improves execution performance by using CASE statements rather than multiple JOIN statements.
The Lookup Transformation Template supports only one variable join field. If a join requires multiple variable fields (for example, a composite primary key), concatenate the key fields into a single field and use the concatenated field as the variable join. As an alternative, use a conditional lookup field, which supports joins on multiple fields.
Example: comparing conditional lookups with the Lookup Transformation Template
This example uses two tables. The JobDept table contains both job and department data. Each row has a Value column and a Field column that describes what the value represents — for example, job title, minimum salary, maximum salary, or department name.
The Employees table is a distinct list of employees with a Job ID and a Department ID.
The goal is to add Job Title and Department Name from the JobDept table to the Employees table. The two methods below produce the same result.
Method 1: conditional lookup fields
Drag the Value column from the JobDept table onto the Employees table to create a conditional lookup field. Set the join condition so that ID (JobDept) equals Job ID (Employees).
Rename the field to Job Title. Right-click Joins and select Add Join. Add a join where Table equals the fixed value Job, and another join where Field equals the fixed value Name. The result is three joins that together return only job title records.
Repeat the process for Department Name: drag the Value field from JobDept onto the Employees table, add a join where ID equals Department ID, then add two fixed joins where Table equals Department and Field equals Name.
The result is two lookup fields, each with three joins.
Method 2: Lookup Transformation Template
Right-click the Value column in the JobDept table and select Add Lookup Transformation Template.
Enter a name for the template. Set ID as the Variable join field — this is the field that changes with each use of the template (first joining on Job ID, then on Department ID). Select Table and Field as the Fixed join fields — these are the fields whose values are specified each time the template is applied.
After clicking OK, the template appears in the Lookup Transformation Templates folder.
To add the Job Title: drag the Value Template onto the job_id field in the Employees table. Enter Job as the fixed value for Table and Name for Field. Rename the resulting field to Job Title.
To add the Department Name: drag the Value Template onto the department_id field. Enter Department for Table and Name for Field. Rename the field to Department Name.
The resulting data is the same as with Method 1.
Using the Lookup Transformation Template with Business Central 365 Option Values
About the Business Central 365 Option Values data source
The Jet Analytics Business Central 365 Option Values data source is designed to work with the Lookup Transformation Template. It is configured in the Jet Analytics Portal in the same way as the standard Jet Analytics Dynamics 365 Business Central — Online provider and uses the same authentication. The difference is that this data source returns only one table: Adapter Option Value, which contains a Table_No field, a Field_No field, an Option_Value field, and a Text_Value field.
When using the Lookup Transformation Template with a standard Business Central Online provider, a second data source using the Option Values provider may be needed to return only this table.
Set up the Lookup Transformation
- Bring in the Field table from the Business Central data source, which contains table and field names. See the Dynamics NAV Field virtual table documentation for more information.
- Add TableName and FieldName from the Field table to the Adapter Option Value table by joining on Table_No and Field_No.
- Right-click the Text_Value field in the Adapter Option Value table and select Add Lookup Transformation Template.
- Name the template. Set Option_Value as the Variable join field — this dynamically joins to the table where the lookup is applied. Select TableName and FieldName as the Fixed join fields.
- Click OK. The template is now available in the Adapter Option Value table.
Apply the Lookup Transformation
Add a new field to the target table to store the lookup result. In this example, the target table is Value Entry and the new field is called Document Type Description.
Right-click the Document Type Description field and select Field transformations. In the Field Transformations pane, change the Operator to Lookup and click Add.
Select the template created in the previous section.
Set the equals field to Document Type, the TableName fixed value to 'Value Entry', and the FieldName fixed value to 'Document Type'. Single quotes are required around fixed string values.
The transformation is applied to the field.
The CASE statement in the Transformation View shows how the template resolves the lookup.
After deploying and executing the Value Entry table, the Document Type Description field will contain the resolved text values for each document type.