Released in TimeXtender 6024.1
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.
We have another table, called "Employees", which is a distinct list of all employees and their Job ID as well as their Department ID.
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.
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".
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".
This will result in three joins which will ensure that only the job title records are returned.
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.
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.
If we preview the table it has the following data
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".
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).
After selecting OK, the transformation template becomes visible under the "Lookup Transformation Templates" folder.
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. Expand the new lookup field to view the applied lookup transformation. Rename the field to Job Title.
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.
The resulting records are the same as in the previous method.
Use the TimeXtender Business Central 365 Option Values provider
What is a TimeXtender Business Central 365 Option Values data source
The TimeXtender Business Central 365 Option 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 TimeXtender Dynamics 356 Business Central - Online provider and authentication is handled in the same way. The difference with this provider is that it only returns back one table, which is called Adapter Option Value. Therefore, using the Lookup Transformation Template with a TimeXtender Dynamics 365 Business Central - Online provider, you may have to set up a second data source that only returns this Adapter Option Values table. In the TimeXtender Portal, the two TimeXtender Dynamic Business Central 365 providers may appear similar to the following.
The contents of that table are a Table_no field, a Field_No field, an Option_Value field, and a Text_Value field.
Set up the Lookup transformation
- 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 Dynamic Business Central 365 Field table see field-virtual-table.
- 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.
- Right-click on the Text_Value field in the Adapter Option Value table and select Add Lookup Transformation Template.
- 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.
- Click OK so the Transformations template is set in the Adapter Option Value.
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.
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.
Use the template that was created.
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.
This change is applied in the field itself.
The case statement in the Transformation View shows how the template is applied.
After a deployment and full execution of the Value Entry table, you should be able to see the values for the various document types.
