The name of the person is said to be the display value of the person. The display value of a person could be variable, depending on the context of where this person is referenced. The display value might even be stored in another table, especially when dealing with link tables. Obtaining a set of keys and display values and translating a key into a display value is called a look-up.
This blog is about look-up display values and how to use them effectively.
Terminology
In the example with persons, the Person table is used as a look-up table. The look-up table is the table you would see if you were to open the look-up pop-up. The look-up table determines the content of a combo and the possible auto-completion suggestions. This is sometimes referred to as the source table.The table where a value has to be translated is called the target table. For instance, when a person is referred to from an employee table, the employee table is the target table.
The Name column of the Person table is configured to be the display field, sometimes also referred to as display column, presentation field or list field. The display field contains the display value.
The foreign key column which can be translated into a display value, the field with the look-up pop-up button next to it, is referred to as the look-up field or look-up column. Task parameters and report parameters can also be a look-up parameter. When talking about the underlying reference of a look-up, the look-up field can also be referred to as the data field.
A look-up field must always be supported by a look-up reference. The look-up field is always the last reference column of the look-up reference. A look-up field must always be supported by a single look-up reference.
There are situations where the value of the data field alone is not enough to determine the proper display value. When this happens, one or more filter fields are used to find the proper display value. For instance, when the primary key of the Person table consists of Company_id and Person_id, the Company_id is referred to as a filter field and the Person_id is referred to as the data field.
The translation table is a concept that will be mentioned later in this blog. The translation table is the table containing the display field. Often, the translation table is the same table as the look-up table.
The history of display values
The way the Thinkwise Software Factory suite deals with display values has changed a lot over time.G6 and earlier
Display values used to be a property of a column in Software Factory version G6 and earlier. This makes sense in a way, the column holds the data value which needs to be translated into a display value. The queries for obtaining and translating would be constructed by the Software Factory based on the reference supporting the look-up.Instead of having the Software Factory construct the query, an query could instead be manually specified for translating the look-up. This query would have to provide all the filter fields, the data field and the display value. This query was known as the custom look-up query. These custom look-up queries provided great versatility and allowed developers to construct a custom look-up set and display value with a query.
However, this proved a bit problematic.
- It could be hard for the user interface to decorate the custom look-up query with prefilters. If a prefilter was set on a field that was not a filter- or data field, the prefilter could not be applied on the custom look-up query.
- When the user would type a value into the field, it was used as a contains-filter on opening the look-up pop-up. But on what field? The display field in the look-up query is not a field of the look-up table.
- The filter fields and the data field in the custom look-up query would have to match the matching foreign key columns. This could only be validated at runtime.
- Authorization could prove difficult – the custom look-up query could be based on a different table than look-up table.
G7 & G8
To deal with the complexity of designing and maintaining custom look-up queries, a new concept was added in the G7: Display expressions.Display expressions (or: presentation field expressions) allowed the developer to set an SQL expression as the display value, without having to use a custom look-up query. For instance, one could aggregate a persons first name and last name and use it as the display value.
The queries for obtaining the look-up set with a display expression would still be constructed by the Software Factory based on the reference supporting the look-up. Custom look-up queries were still a possibility.
Early G9
The G9 heralded the end of custom look-up queries. Existing custom look-up queries were migrated by the upgrade to display expressions. Custom look-up queries that could not be transformed to a display expression would have to be replaced by a view.The information about the display field or display expression was migrated from the column to the reference. Since custom look-up queries no longer exist, a supporting reference will always be required to translate a data value to a display value.
In contrast to the G8 and earlier, the GUI would from now on be responsible for creating the look-up query, instead of the Software Factory. This guaranteed that the GUI could place prefilters within the look-up query without any problems.
Final G9
Since G9.4, the look-up display expressions at the reference have been migrated to expressions at the look-up table. This way, there was no difference anymore for the user interface between a regular column as display value and an expression as display value.
This allowed for authorization, filters, conditional layout and various other options for the look-up display expressions.
Expressions aren’t the solution for everything
Expressions allow the developer to use a display value which is not present in the current table. For example, imagine the following data-model:
When a user creates a record at the link table Project_employee and selects an employee, the user wants to see the name of this employee. However, the Name is not in the look-up table Employee, but in the table Person. An expression column such as Person_name can be added to the Employee table which provides access to the corresponding Name. This works in this case, but when the display field of Person is changed to another column, the expression of Person_name, used as display field at Employee, will not change with it. And there are more challenges when using expressions to transfer the look-up display value to a ‘deeper’ table. To illustrate this, take a moment to understand the following data-model:
When a user creates an Hour record, the user first selects an employee from the Employee look-up table and selects a project from the Project_employee table. An expression column Project_name would have to be added at the Project_employee table to use as display field, to provide the name of the project.
When the user creates a Planned_capacity record, the user first selects a project from the Project look-up table and selects an employee from the Project_employee look-up table. An expression column Employee_name would have to be added at the Project_employee table to use as display field, to provide the name of the employee, which can be found at the Person table.
To deal with this, we would have to add two expression columns to Project_employee and set them to be the display field specifically per reference. This would introduce a lot of maintenance for the development team.
Look-up display value resolvement
Instead, the concept of look-up display resolvement can be used to have the GUI automatically determine the proper display value. This is one of the major reasons why the generation of look-up queries has been moved from the Software Factory to the GUI at version G9.To have look-up display resolvement kick in, simply leave the display field setting on a table or on a reference empty.
When a display field is not set for the table Employee, look-ups using Employee as a look-up table will automatically resolve to Person.name. In the example, the look-up for Project_employee.person will automatically be resolved.
When a display field is not set for the table Project_employee, the look-ups using Project_employee as a look-up table will automatically resolve to either Project.name or all the way to Person.name. The look-up chain, the path taken, depends on the data field, the last column of the reference supporting the look-up.
For instance – Hour.person_id:
- The column Hour.person_id has as look-up data field Employee.person_id. No look-up display field is found yet.
- The column Employee.person_id has as look-up display field Person.name
- Thus: The column Hour.person_id will use Person.name as display field.
Resolving Hour.project_id:
- The column Hour.project_id has as look-up data field Project_employee.project_id. No look-up display field is found yet.
- The column Project_employee.project_id has as look-up display field.
- Thus: The column Hour.project_id will use Project.project_name as display field.
Resolving Planning_capacity.person_id:
- The column Planning_capacity.person_id has as look-up data field Project_employee.person_id. No look-up display field is found yet.
- The column Project_employee.person_id has as look-up data field Employee.person_id. No look-up display field is found yet.
- The column Employee.person_id has as look-up display field Person.name.
- Thus: The column Planning_capacity.person_id will use Person.name as display field.
A query generated by the GUI to fill a combo of employees at Planned_capacity, with the filter field Project_id having the value 6, would looks as following:
code:
select
t1.project_id as filter_1,
t1.person_id as data_field,
t3.name as list_field
from project_employee t1
join employee t2
on t2.person_id = t1.person_id
join person t3
on t3.person_id = t2.person_id
where t1.project_id = 6
order by
t1.project_id,
t3.name
There are a few limitations. The look-up chain, the path from the look-up table to the translation table, requires a certain level of integrity. Please keep the following limitations in mind when designing your look-ups.
Invalid chains
When going up the look-up chain towards the translation table, the filter fields must stay constant or get reduced. Introducing new filter fields in the look-up chain can cause unexpected problems and is also logically unsound.Breaking the chain
When a look-up display field is chosen at any reference in the look-up chain, the look-up display resolvement stops there. Even when the chosen look-up display field is a column with a look-up, the translation of this column will not be used. Instead, the data value of the column will be shown as display value.Unresolvable chains
When there is no display field chosen in the entire chain, the GUI will just show the data value for this look-up. When a chain has a loop in it, the GUI will not be able to start.
IAM Rights
The user must have rights on the entire look-up chain in order to see the translated field. For example, if an user needs the field Hour.person_id to be visible, the IAM will not allow this until you grant selection rights on Employee.person_id and Person.name.This is also the reason why a display field cannot be changed in a variant. This would have direct impact on the required rights, and the rights are not specified per variant.
Service tiers
Service tiers know the look-up chains so they can apply them when the GUI requires sorting or filtering on a look-up display value. Contrary to queries built by the GUI, queries built by the service tiers will use subqueries to obtain the look-up display value, instead of joins.Proxy service tiers will generate services for the entire look-up chains required by the Mobile GUI.
Tasks and reports
Look-up resolving for task- and report parameters works exactly the same as columns. Big difference is that a task or report in itself can never be a look-up table, translation table or part of a look-up chain.Modifying the selection set
The look-up pop-up, the look-up combo, suggestion controls and autocompletion controls will always use the look-up table as the source for choosing a value. The translation table is only used to obtain the display value. This allows the developer to limit the set of look-up values based on look-up filters that are not present in the translation table. This is also done in the previously mentioned examples.Selection cannot be performed on the look-up translation table if the translation table is different from the look-up table. If additional information from the translation table is required, consider adding expressions to the look-up table.
Prefilters from the translation table cannot be used if the translation table is different from the look-up table. If the look-up set has to be filtered using prefilters from the translation table, the prefilters must be copied to the look-up table.
If required, prefilters can be changed for a specific variant, and the variant can be used for a certain look-up.
Final notes
Look-ups are one of the more complicated objects which are modeled in the Software Factory. If a situation calls for it, there’s always the option to create a view and have a simple look-up point towards the view.At this point the only feature that still has to be developed for the GUI is using the control of the display field as the control of a look-up column. This will be made available for read-only look-up columns at first. Examples include look-ups being displayed as an image or a multiline.
But even editable controls other than our current combo, suggestion and auto-completion could be imagined for future releases, such as a calendar limited to days available in the look-up set or a carousel of images to choose from.
We have come a long way when it comes to look-ups since the G6, and we consider the current look-up architecture to be at it’s finest. If there are any ideas for further advancement or quality-of-life improvements, do not hesitate to create a ticket in TCP