Description:
The task is: a View is bound to the medications table containing MedicationClass, MedicationName and MedicationDose fields. Lookup editors are used in the View for these fields for convenient editing. While the available medication names and doses must depend on a given medication class, it makes sense to filter the lookup list and display only those items, which are situated in a given class.
Answer:
We can suggest two ways to implement this task:
Way 1:
Create two lookup tables. The first table is bound to the column's editor. It must contain all rows to properly display lookup values in all View records. The second table should be assigned to the in-place editor, once it is opened. This table will be filtered to contain a limited recordset as appropriate for the current field/record.
Instead of a filtered table, you may use a query which contains the WHERE clause with appropriate parameters.
Way 2:
Instead of creating extra datasets, you may filter directly the dropdown lookup Grid. This Grid contains its own Data Controller and you may use all its properties and method similar to what you do for a Grid View.
Common part:
The Table View object has the OnInitEdit event, which allows you to set up the in-place editor before it is displayed. You should use this event to populate combo box items, filter lookup lists, and change other editor properties. Using the editor's OnPopup or OnInitPopup events instead is not appropriate in this situation for two reasons:
a) the editor needs correct lookup data to display an edit box value;
b) a user can change the edit value without opening the dropdown list, for example, by typing in a column cell.
Within the OnInitEdit event handler we:
- check whether the column is the one whose editor we need to modify;
- form a filter for the auxiliary lookup table using a value of the current View record;
Way 1: replace the editor's list source with the data source, which binds to the filtered (auxiliary) lookup table;
Way 2: set the filter of the lookup Data Controller.
The attached sample shows both ways. Which one to choose depends on the peculiarities of your application.
In addition, the sample may be extended with one more feature: the MedicationClass and MedicationName fields must be cleared when the MedicationDose value is changed. You can use the OnEditValueChanged or OnValidate event of the MedicationDose column to accomplish this. This task goes beyond this article and is discussed in the following topic:
A343
I do not see the sample code attachment.
Did it get lost?
You can find links to download attachments on the right side of this page in the orange rectangle. Please see the attached screenshot.
Does this sample code need to be updated? "Way 2" doesn't work. After you hit the dropdown in Medication Name once, any time you drop the list down after that, it is empty. "Way 1" is fine.
Hello Ron,
I've checked the project of this article and it works without problems (see the attached video).
It seems something is wrong with the project on your side. I've created a separate ticket on your behalf about the issue - T651630: The second approach of the A344 Knowledge Base article does not work. Let's continue discussing this problem there.
I was trying to use "Way 2" (directly filter the dropdown lookup Grid) but I ran into a problem. The values I'm using to filter on are in the dataset that drives the lookup grid but it looks like I can't use them in the filter unless I add those to the list columns in the lookup grid. Is this correct? For example, in your Medication demo for this, you filter the medication name dropdown based on medication class. You can do that because ClassID is one of the list columns. What if I don't want to see ClassID there? In my application I do not want to show the columns I'm using to filter on. And there doesn't appear to be a way to add a column and make the column not visible. Is there a way to use the filter to access fields in the dataset that are not in a column in the grid?
Hello Ron,
I've created a separate ticket on your behalf (T853467: How to filter Lookup Combo Box by a certain field). It has been placed in our processing queue and will be answered shortly.