The drop-down list is one of the innovations in the RoleTailored Client. It is also a feature that is not overwhelming documented. There is just one topic in the help files that explains how to control which fields are displayed. There are also a couple of blogs around this topic. However, there are some undocumented features that I recently discovered and that I want to share with you. I don’t know if these features are by design or just a result from the way how the DropDown Box is implemented, but they are worth to take a closer look at.
These undocumented features are about filtering a column, display or hide the New and Advanced button and dynamically filtering of the records (yes, it is possible).
Let’s start with the basics to get a complete picture of the drop-down list.
The purpose of the DropDown box is to quickly find a certain record by filter-as-you-type. Just start typing in a field and the drop-down list appears automatically, filtered by the value you are typing. The column that is being filtered on can be identified with the filter icon. It is possible to change the column that is used for filtering, by clicking on the column header.
To filter on the Description field, we first need to click on the Description column header.
Now the list is filtered on the Description column with all values that start with ‘Lo’. Notice also that the option ‘Set as default filter column’ in the lower right hand corner is enabled in the second screenshot. Click on this link to use the current filter column by default the next time.
Adding columns to the drop-down list
The columns that are displayed in the drop-down list are specified on table level. Open a table in design mode and choose View –> Field Groups.
Although it is possible to add multiple Field Groups here, only a Field Group with the name ‘DropDown’ is used. If a Field Group with this name is not found, then automatically the primary key and description fields will be used:
Click on the Assist Edit button in the Group field to modify the list of columns in the drop-down list.
Enabling filtering on columns
You may have noticed that in the screen prints above the columns Bill of Materials and Unit Price are grey. That indicates that it is not possible to use these columns as a filter. Thus, entering a price to filter the list with all items that have that price is not possible. Typically, it is only possible to search on Text and Code fields, and only if a key exists that starts with this column.
Let’s add a field Color to the Item table and the drop-down list:
The column header of the column Color is grey, so we can not sort on it. But after adding the field Color as a key to the Item table, the column header becomes black and we can filter on it:
The Advanced link in the lower left corner of the drop-down list opens the page that is specified in the LookupFormID. This is similar to the Classic Client. There are two places where the LookupFormID can be set:
- In the Table properties
- In the properties of the textbox control on the Page
The Advanced button in the drop-down list opens by default the Page that is specified as the LookupFormID in the Table properties. If the textbox on the Page has a LookupFormID specified, then the Advanced button will open that Page. So the LookupFormID on the textbox control overrides the LookupFormID on the Table.
If the LookupFormID is not set on the Table or textbox control, then the Advanced button will disappear from the drop-down list. The same counts for the New button. In the next screenprint, the LookupFormID was removed from the Item table:
The New button in the lower left hand corner opens a Page in insert mode. This is either the LookupFormID or the Card Page that is associated with the Lookup Page. The Card Page can be set in the Page properties of the Lookup Page (why didn’t they call it CardPageID?):
The New button only appears on the drop-down list if one of the Pages from the LookupFormID or CardFormID can insert new records. If both Pages have InsertAllowed=No or Editable=No, then the New button will disappear. If the CardFormID is specified, but the Card Page has no rights to insert new records, then the system will use the Lookup Page instead. If that Page has insert rights, of course.
Dynamically filtering of the drop-down list
The records that are displayed in the drop-down list are filtered based on the Table Relation. Again, this is a property that can be set on Table level and on Textbox level. This hasn’t been changed since the Classic Client. However, setting a filter in the Table Relation on Textbox level has no effect on the drop-down list.
Sometimes, we need to dynamically filter the drop-down list. For example, based on the user permissions or based on other properties that can not be set via the Table Relation. In the Classic Client, it is common use to program this on the OnLookup trigger. Unfortunately, when we use the OnLookup trigger, it is not possible to use the drop-down list. The Lookup Page will be opened as a pop-up window.
There is good news and some bad news. The good news is, that there are some (limited) possibilities to combine dynamic filtering with the drop-down list.
The secret is that the triggers OnInit, OnOpenPage and OnClosePage on the Lookup Page will run, prior to opening the drop-down list. The OnInit and OnClosePage trigger do not effect the filters. But, any filter that is set on the record in the OnOpenPage trigger will apply to the drop-down list. The bad news is that if a filter is specified in this trigger, the original filters from the Table Relation will be reset. And it is not possible to read the filters in the Lookup Page.
Let’s illustrate this behavior with an example.
First the Table Relation is extended with a filter on Color. (Ok, not very logical, but it is just an example):
Now the drop-down list is filtered on all black items:
Next we edit the OnOpenPage trigger in the Lookup Page to only display items that start with ‘7’:
The drop-down list now forgets everything about the Color filter, and only displays Items starting with 7:
Of course it would be nice if the filters from the Table Relation were put into a Filtergroup. I tried to scan a dozen Filtergroups, but to no avail. It is not just there.
So, as you can imagine, it will be hard to combine both Table Relation with filters and dynamic filtering through the OnOpenPage trigger. The only thing I can think of, is to make use of single instance CodeUnits to transfer information about the actual record and program the filter that was specified on the Table Relation.
But if you have a Table Relation without filters (or you can put them in code) and you need dynamic filtering and still want to have that nice drop-down list, than make use of the OnOpenPage trigger of the Lookup Page. If necessary, create a specific Page for your filtering and specify this Page in the LookupFormID on the Textbox control.
On the other hand, setting filters in the OnOpenPage trigger of a Page that is also used as Lookup Page can cause unexpected behavior. So use this feature carefully!