Lookups

system · December 31, 2017, 11:00pm

Lookups define the way how to fill-in Drop-Down lists and how to translate id’s to names both in the user interface and business logic.

Lookups are the Swiss Army knife of the data model. Often you will need to get a list of values which can be entered to a field (e.g. in a drop-down list) or you need to look-up a value by a specified key (e.g. getting a customer’s name by providing an Id of his record). Data Lookups are the tool for getting this done.

Every data lookup provides 2 main features:

List: Provides a list of values (so you can possibly pick one)
Value: Gives a value depending on an Id passed (so you can display something understandable to the user when you have only Id)

Some data lookup consumers use both features (list and value), some use only one of them.

Usage

The Model elements which reference data lookups:

Scenarios

Drop-Down Lists in GUI	Drop-Down lists are user controls that allow users to pick up a value from a list. They also display a current value instead of displaying an Id which is stored in the record's field. They use both Value and List features of Data Lookups Drop-down list is loaded with the lookup list If the IsTree attribute is set, then the Drop-down part of the drop-down control is rendered as s treeview with a hierarchical view of the data. Every time a value is being displayed to the user, the Value is looked up by the Id.
Entity Model SQL	When designing reports, record IDs are usually NOT the data being displayed to the user. Instead, we want to display some understandable text. The Data Structure's UseLookupValue attribute or entity Lookup Field allow you to substitute the Id with a looked-up value directly in the database query by generating a SQL subquery. This way data structures can stay simple and data queries faster. Data Structure lookup value substitution uses only the Value part of the lookup. It does not use the List part.
Data Audit Log	When data auditing is enabled in the data entity, the data service stores an audit trail of all the changes that happened because of data editing done by the user or the workflow. When displaying the audit log, the user does not want to see which ID changed to another ID, he wants to see, what value was referenced by it at that time. Therefore before saving the audit record, the data service queries the lookup for the lookup value and stores that value in the audit together with the ID. Auditing also uses only the Value part of the lookup. It does not use the List part.
XSL Stylesheet Transformations in Workflows	Sometimes data transformations require substituting Id which arrives with the transformation data source with a looked-up value. A good example can be when we get a business case record and we need to generate an e-mail, knowing only the customer's ID, stored in the business case. From the XSL stylesheet we can call a LookupValue or a LookupList function, which will return us the value instead of the Id. The LookupOrCreate function allows you to create a record if it does not exist in a single statement. The LookupValue uses the Value part of the lookup while the LookupList uses the List part of the lookup.

Attributes

List Part

AlwaysAllowReturnToForm	The Select and Return to <lookup page> button will be always shown even when the lookup list is filtered.
IsTree	Indicates that the lookup has a hierarchical structure. You have to specify the `TreeParentMember` attribute if this value is set to `True`.
TreeParentMember	Specifies the field in the `ListDataStructure`, which points to the parent record. E.g. in a folder structure type of entity, this would be the `refParentFolderId`, which would point to an `Id` in the same entity.
ListDataStructure	A Reference to the data structure, using which the data for the lookup list will be loaded. The data will loaded from the root entity. The LookupOrCreate this data structure is used for inserting data. For this work it has to have AllFields `= True`.
ListMethod	Specifies an optional filter set for the list part of the data lookup. If the filter contains parameters, these parameters must be provided when requesting the list. This happens e.g. by setting ParameterMappings in a DropDown widget.
ListSortSet	Optional sort set that will change the way how the data will be sorted when displayed using the Drop-Down list. By default, the data are sorted by the first column alphabetically in an ascending order.
ListDisplayMember	Specifies a field from the `ListDataStructure` root entity, which is displayed to the user in a drop-down list. E.g. `Name`. Multiple fields can be specified, delimited by semicolon (;).
ListValueMember	Specifies a field from the `ListDataStructure` root entity, which will be used as a key to be returned when the user selects an item from the drop-down list. E.g. customer's `Id`.

Value Part

ValueDataStructure	Reference to the data structure, which will be used to look up a single value in the data source.
ValueMethod	Specifies a filter set for getting a single looked up value. DATA MODEL USAGE When the lookup is used in the data model (either as Lookup Field or in a data structure by setting `UseLookupValue = True` this filter must have 1 and only 1 parameter, which results basically in filtering by a primary key. XSLT USAGE When used via LookupValue XSLT function you can have many parameters. Then you will use it to return a single value but using multiple parameters. If more than 1 value exists in the database, it always returns the TOP one. You can take advantage of using a `ValueSortSet` attribute to sort data in a way that it will e.g. return the highest value etc.
ValueSortSet	Specifies a sort set by which the data should be sorted. Since only 1 value can be returned it is only useful if you know that there exist multiple results but you need to control which of them will be returned (e.g. the highest number).
ValueDisplayMember	Specifies a field from the `ValueDataStructure` root entity, which will be used for displaying the resulting looked-up value to the user e.g. in a drop-down list. E.g. `Name`. Multiple fields can be specified, delimited by semicolon (;), the displayed values will be delimited by comma (,).
ValueValueMember	not used at the moment

Children

NewRecordScreenBinding

If you define this child element under the DataServiceDataLookup a + button will appear next to the dropdown button when the lookup field is displayed in the client application. Clicking on the + button will open a form where you can add new dropdown items.

NewRecordScreenBinding Attributes

MenuItem	Defines the screen that will open after clicking on the `+` button
DialogHeight	Height of the dialogue in the client application in pixels.
DialogWidth	Width of the dialogue in the client application in pixels.
Roles	Roles that will see the button.

Version 2024.4 introduces a possibility to prefill values in the opened form. This feature relies on two sets mappings with the data structure parameters as a joint point. The first set of mappings is based on model element NewRecordScreenBindingParameterMapping below NewRecordScreenBinding.

NewRecordScreenBindingParameterMapping Attributes


ParameterName	Parameter used in (added to) the list data structure of the lookup. The referenced source column will be set in the properties of the screen section combo box where the lookup is used. If the actual text content of the lookup field should be copied, the value should be set to `SearchText`.
TargetRootEntityField	Column in target root entity to be filled.

If you need to only insert text content of the lookup control into the from, you just need to create NewRecordScreenBindingParameterMapping with ParameterName SearchText and a target column and you’re done. If you need to map other columns, you need to use the data structure parameters (they need to be create in the lookup list data structure) and map them in the screen section.

A dedicated data structure parameter was created to facilitate transfer of numeric value to the new record form.