Follow

Lookup Transformation Template

This article describes how to use lookup transformations, and in which scenarios using a lookup transformation template might be preferable instead of using conditional lookup fields.

What is the Lookup Transformation Template?

The Lookup Transformation template can be used to create multiple lookups with a variable join field and several fixed join fields. Instead of creating multiple conditional lookup fields manually, with several joins each, the lookup transformation template is created once, and can then be reapplied several times for several lookups. This saves time and improves performance, in terms of execution time, due to the way it is set up.

An example of when to use Lookup Transformation Template

Say we have a table, called "JobDept", which contains both Job related data and Department related data. Within the table there is a column called "Value" and a separate column called "Field", which describes the data being displayed in the Value column for each record. The table contains the following data: title of the job position (i.e. the records where the "Table" column is set to "Job" and the "Field" column is set to "Name"), minimum salary, maximum salary, and name of the department. Below is a preview of the table.

mceclip18.png

We have another table, called "Employees", which is a distinct list of all employees and their Job ID as well as their Department ID. 

mceclip1.png

Say we would like to bring in the Job Title and Department Name for each employee from the JobDept table into the Employees table. There are two ways to do this. The first method relies on conditional lookup fields. The second method uses the lookup transformational template.

Using Conditional Lookup Fields

The first method relies on conditional lookup fields. To bring in the Job Title field we can drag the Value column from JobDept table onto the Employees table, and create the relation where the ID in the JobDept table is equal to Job ID in the Employees table.

mceclip23.gif

The next step is to rename this conditional lookup field to "Job Title" and then add an extra join, by right clicking on joins and selecting Add Join. Set the join column to be "Table", the operator to equal, value type to fixed value and enter the value "Job". 

mceclip12.png

Add another join. This time, set the join column to be "Field", the operator to equal, value type to fixed value and enter the value "Name". 

mceclip11.png

This will result in three joins which will ensure that only the job title records are returned.

mceclip17.png

Repeat the process for Department Name, by dragging the Value field from JobDept table onto the Employees table and add a new join, where the ID is equal to the Department ID.

mceclip21.png

mceclip22.png

Then add two additional fixed value joins, with the Table column set to "Department" and the Field column set to "Name". The end result would be two fields, with three joins each. 

mceclip16.png

If we preview the table it has the following data 

mceclip15.png

Using the Lookup Transformation Template

The same result can be implemented more quickly by using the Lookup Transformation Template. This method also leads to better performance in terms of execution time, as this method relies more on CAST statements as opposed to JOIN statements. In order to implement this method, right-click on the field you would like to lookup (in our example this is the Value column, since we want to look this up and add it to the Employees table), select "Add Lookup Transformation Template".

mceclip24.png

Provide a name for the template and select the variable join, in our case this is the "ID" field since this is the field we want to dynamically join on (i.e. first on the job id and then on the department id). Then select the fixed value columns (in our case "Table" and "Field" are the static columns which we want to create fixed joins for). 

mceclip26.png

After selecting OK, the transformation template becomes visible under the "Lookup Transformation Templates" folder.

mceclip27.png

Then simply drag the Value Template onto the job_id field in the Employees table, and enter the fixed value 'Job' in the Table column, and 'Name' in the Field column. Note: The single quotes are required because the fixed values are text values. Expand the new lookup field to view the applied lookup transformation. Rename the field to Job Title.

mceclip29.gif

For the Department Name, repeat the process, dragging the Value Template onto the department_id field in the Employees table, and enter the fixed value 'Department' in the Table column, and 'Name' in the Field column. Expand the new lookup field to view the applied lookup transformation. Rename the field to Job Title.

mceclip30.png

mceclip31.png

The resulting records are the same as in the previous method.

mceclip32.png

Use the Dynamics 365 Business Central Values provider

What is a Dynamics 365 Business Central Values data source

The Dynamics 365 Business Central Values source is specifically made to work with the transformation template.

To connect with it you need to set up the data source in the portal. The setup is similar to a normal D365 Business Central provider and it is authenticated the same way. The difference is that it only gives one table back called Adapter Option Value.

The contents of that table are a Table_no field, a Field_No field, an Option_Value field, and a Text_Value field.

mceclip0.png

Set up the Lookup transformation

  1. Since the Adapter Option Value table only contains table number and field number, we will also bring in the Field table, in order to extract table and field names. To learn more about the D365 Business Central Field table see field-virtual-table.
  2. Bring the columns TableName and FieldName from the Field table into the Adapter Option Value table joining with the Table_No and Field_No fields, as shown below.mceclip3.png
  3. Right-click on the Text_Value field in the Adapter Option Value table and select Add Lookup Transformation Template.mceclip4.png
  4. Give the template a name, set the Option_Value to a Variable join field, and select TableName and FieldName as the Fixed join fields. The variable join field dynamically joins to the table where the lookup is added, and the fixed join fields are the fields that are set when using the template. mceclip11.png
  5. Click OK so the Transformations template is set in the Adapter Option Value.

mceclip6.png

Use the Lookup Transformation

Locate the field you want to find the Text_Value for, and create a new field to store the values in.

mceclip2.png

The table used is the Value Entry table, Document Type is the field and the field to add the template on is called Document Type Description.

Right-click on the Document Type Description field and choose Field transformations. In the Field Transformations view in the right pane, change the Operator to Lookup and click on Add.

mceclip8.png

Use the template that was created.

mceclip9.png

Set the equals field to Document Type, the TableName field to 'Value Entry', and the FieldName to 'Document Type'. As mentioned above, you need to add ' around the values for them to be applied correctly.

mceclip10.png

This change is applied in the field itself.

mceclip13.png

The case statement in the Transformation View shows how the template is applied.

mceclip14.png

After a deployment and full execution of the Value Entry table, you should be able to see the values for the various document types.

mceclip12.png

Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.