Using Automatic ID Lookup in Views

Lookup field title

Views determine the title of the displayed ID or lookup field using the following rules, listed from highest precedence to lowest:

If the view contains a custom title for the ID field, use that title. (From Building, To Building)
If the ID field is a foreign key ( mo.from_bl_id ) and the title is not the same as the title of the ID field in the reference table ( bl.bl_id ), use the main table ID field title.
Otherwise, display the field title as follows:

lookupDisplayType	Display this field title
id	ID field
lookup	Lookup field
concatenate	ID field

Hide ID or lookup fields in specific views

You typically want the ID field hidden in reports when the lookup values are present, as the ID fields are not very useful. By default, the ID fields would be hidden.

However, you may not always want the ID field hidden. For example, views that show the relationship between Employee ID and Employee Last and First Name need to show the ID. In addition, you may want the ID field displayed in views where the ID field source table (such as, Departments) is the main table, such as the Select panel of Select-Edit views.

You may also need to hide the lookup fields.

To hide ID or lookup fields, add the lookupDisplayType attribute to the ID field in the data source. This setting overrides the displayType value in afm_flds.attributes for this specific view field.

// display the ID field, hide the Lookup field
<field name= "bl_id" lookupDisplayType= "id" />
// hide the ID field, display the Lookup field
<field name= "bl_id" lookupDisplayType= "lookup" />
// display both the ID field and the Lookup field
<field name= "bl_id" lookupDisplayType= "both" />
// display the ID field value concatenated with the Lookup field value
<field name= "bl_id" lookupDisplayType= "concatenate" />

You can specify l ookupDisplayType only in the data source field, and not in the panel field.

Override the lookup display format or sort order

To override lookup display format or sort order for specific view field, add the lookupDisplayFormat and/or lookupOrderBy attributes to the ID field in the data source:

You can specify lookupDisplayFormat only in the data source field, and not in the panel field.

Edit forms

There are two uses cases for data entry forms.

ID field is a foreign key

ID and lookup values are in one field control.
When the form loads a record, the read-only lookup value is displayed below the ID field.
Users can enter or select the ID value using auto-complete or Select Value dialog. When the user selects an ID value, the form displays the corresponding lookup value.
If the user begins to type a new ID value, the form clears the lookup value.
Forms ignore the lookupDisplay preference.

ID field is a primary key OR lookup field is explicitly defined in the form

The form works the same way as when Automatic ID Lookup is disabled.

Column reports

Column-style reports are not used alone but in Wizards and multi-pane forms (such as the Examine form of the Group Move Wizard). For column-style reports with multiple columns, inserting the lookup fields after the ID fields (when ID field is not hidden) may alter the layout significantly.

For each ID field in column report, column-style reports:

add the lookup field ( dv.name ) to the panel just after the ID field ( dv.dv_id ), if the lookup field is not already visible in the panel.
display ID and lookup field values according to the lookupDisplay field attribute, and rules defined in above.

Grids and reports

For each ID field, the view:

Adds the lookup field ( dv.name ) to the panel just after the ID field ( dv.dv_id ), if the lookup field is not already visible in the panel.
Displays ID and lookup field values according to the lookupDisplay field attribute, and above rules.
Uses the Lookup + ID sort field for paging.
Exports the grid with the same visible fields as rendered in the browser when using exportPanel (xls,docx,pdf)
Uses Data Transfer to export and import fields that are explicitly defined in the panel

Grid Filter

When the user filters records using mini-console, the grid filters on visible values.

lookupDisplayType	WHERE	Comment
id	ID field LIKE '%value'
lookup	Lookup field LIKE '%value'
both	ID field LIKE '%value'	If the user entered value in the ID column.
both	Lookup field LIKE '%value'	If the user entered value in the Lookup column.
concatenate	(ID field LIKE '%value' OR Lookup field LIKE '%value')

Filter consoles always apply the restriction on the ID field, even if the grid concatenates ID and lookup values.

Grid Sort

When the user sorts records by clicking on the ID column header, the grid sorts by visible values. When lookupDisplayType=concatenate , you can optionally specify whether you want to sort first by ID value or by lookup values.

lookupDisplayType	ORDER BY	Comment
id	ID field
lookup	Lookup field
both	ID field	If the user clicks on the ID column
both	L ookup field	If the user clicks on the Lookup column
concatenate	ID field, Lookup field	Default, when lookupOrderBy attribute is not specified, or when lookupOrderBy=idFirst.
concatenate	Lookup field, ID field	When lookupOrderBy=lookupFirst

Grid Index

If the view defines an index field, the grid handles it as follows:

lookupDisplayType	Index by
id	ID field
lookup	Lookup field
both	ID field
concatenate	ID field

Select Values forms

Select Value forms follow the same rules as the Grid Panel, except the ID field is always visible. They ignore the above rules listed in the “Hide Numeric ID Fields” section of this topic.

If you have specified the sortFieldNames attribute, the dialog sorts by lookup and ID values. Sorting by lookup values makes the list appear correctly sorted, and sorting by ID values ensures that duplicate lookup values are consistently sorted.

If you have not specified the sortFieldNames attribute, the dialog sorts by all of the lookup fields and all ID fields. (e.g. ORDER BY dv.name , dv.dv_id , dp.name , dp.dp_id ).

Auto-complete

Auto-complete displays records that match user query either in the ID field or the name field.

It displays both the ID and the lookup fields matching form fields (e.g. wr.dp_id and dp.name ).

If the foreign key is a child key of a hierarchy, such as Division-Department, auto-complete also displays parent ID and lookup values (e.g. wr.dv_id and dv.name ).

Auto complete.png

Tree control

If any of the lookup fields are already displayed in the tree, the tree displays exactly as if automatic lookup is disabled (and it does not hide ID fields per "Hide ID Fields").

If the ID field is hidden per the above rules; trees display the lookup field instead of the ID field.

If the ID field is not hidden, trees display the lookup value next to the ID field.

The tree sorts nodes by the lookup + ID field.

Cross-tables and charts

If Automatic ID Lookup is enabled, the view:

Displays ID and/or lookup values per preferences.

Displays ID and/or lookup values per preferences.

Groups records by ID fields because they are guaranteed to be unique.
Group records also by lookup fields because they need to be in the GROUP BY clause in order to be available in the ORDER BY clause.
Sorts ID values by lookup values because the user wants visible values to appear sorted.

Grouping data sources modify their queries as follows:

Data source type	Grouping fields	GROUP BY	ORDER BY
1D	rm.dp_id	rm.dp_id, dp.name	dp.name
2D	rm.dp_id, rm.bl_id	rm.dp_id, dp.name , rm.bl_id, bl.name	dp.name , bl.name

Chart data axes and cross-table calculated fields do not use lookup fields. For example, count(em.em_id) works even if the ID field is numeric.

Export to XLS, DOCX, and PDF exports the chart or cross-table with the same visible fields as rendered in the browser.

Drawing labels

In drawing panels, label text for Employee, Building, Department/Division, Category/Type values by default display the ID field.

Drawing Labels.png

If Automatic ID Lookup is enabled, labels display ID and/or lookup values based on global per-field schema preferences and per-view overrides (same as grid reports).

Drawing highlights

Highlight data sources that do not explicitly include lookup fields, display ID and/or look up values based on global per-field schema preferences and per-view overrides (same as grid reports).

Leaflet map control

If Automatic ID Lookup is enabled, Leaflet map markers display lookup values based on global per-field schema preferences and per-view overrides (same as grid reports).

lookupDisplayType	Display this field value
id	The ID field.
lookup	The Lookup field.
concatenate	Not supported (displays the ID field).
both	Not supported (displays the ID field).

Leaflet Map Control.png .

Custom controls

Custom controls implements using Java Script do not support Automatic ID Lookup at this time.