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!
Pingback: The fastest way to select a customer on sales order in Dynamics NAV | Navisionary – Dynamics NAV
Pingback: Controlling the drop-down list in the RoleTailored Client | Pardaan.com
Looks like this only applies to R2?
I’m not sure, according to me this also applies to RTM and SP1.
yeah , I also tried in SP1 it doesn’t works..
We have changed the sorting column. But somethimes it’s set back to the original default. Do you know where this change is saved?
It is saved in a local file PersonalizationStore.xml. This file is stored in the ApplicationData folder, which is on my computer, the folder C:\Users\
\AppData\Roaming\Microsoft\Microsoft Dynamics NAV
Having a local file means that the setting is purely personal. And without setting up roaming profiles on the network, it will not travel with the user to other workstations.
Unfortunately, this PersonalizationStore.xml file contains encrypted data, so it is not possible to edit it manually.
about the filter bug…
Thanks for the reminder… and in return I leave you with this:
“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.”
It is possible to conditionally filter lookup forms from the OnOpenPage when no other filters are present by scanning the FILTERGROUPS
Consider this example which I placed in the OnOpenPage of the Item List page:
(Define x as integer)
FOR x := 0 TO 5 DO
IF GETFILTERS() ” THEN HasFilters := TRUE;
IF NOT HasFilters THEN
… Set your filters here
Thanks Arend-Jan, this was incredibly useful 🙂
One very small addition – the OnOpenPage trigger on the Lookup Page can also be used to change the sorting (with SETCURRENTKEY)
(Found this as a workaround to a funny NAV 2013 platform bug affecting table relations to any field which may be sorted ‘non-alphabetically’ due to a field preceding it in the primary key. This can make it impossible to enter certain values – e.g. if ‘ABC’ appears before ‘A’ according to the PK, then it is impossible to enter ‘A’)
Thanks for sharing this!
Pingback: Controlling the look up | Nav For Dummies Blog
Thank you for this useful document, I want to ask you, I want to apply styletxt to the drop-down list, (for example I need to highlight records with certain condition) I’ve done this on the page by adding on after get record trigger:
IF Highlited = TRUE THEN
StyleTxt := ‘Favorable’
StyleTxt := ‘Standard’;
but this doesn’t appear in the drop-down list!!
Any solution for this??
Hi Mr. Nasser,
did you find a solution for the problem with the styletxt in the drop down list?
I’ve the same demand here.
I set the font style of the “blocked item” entries in the Itemlist to “Attention”.
But it doesn’t appear in the drop-down list.
Any help is much appreciated.
Thanks in advance
Not yet 🙁
Thanks for this very valuable post! I’ve used it a several times to fix “strange” behaviour or add usefull functionality.
I would never have figured this out by myself and as far as I know most of the topics are not documented anywhere else.
This reminds me, fellas lets play 1.8.8 Survival Minecraft with bloggers
Very helpful. In my bookmark now
I had not known about drilldown and lookup properties so much. Thanks a lot for this great piece of information.