DropDown list of values depending on selected value in another column for the foreign key on two column

Hello I have three tables:

Departments(ID int, name varchar(20));

Products(ID int, name varchar(20), department_id int);

Sales(ID int autoincrement, salesdate date, product_id int, department_id int);

Created foreign key for Sales:

PRIMARY KEY (id), KEY sales_product_id_foreign (product_id, department_id), CONSTRAINT sales_product_id_foreign FOREIGN KEY (product_id, department_id) REFERENCES Products (id, department_id) ON DELETE CASCADE

When I select particular department_id in Sales, and afterwards when I select product_id in Sales, I get list of all products, not only filtered by specified department_id in table Sales.

Is it possible only to get related products from table Products based on selected department_id in table Sales?

You mean in the data tab, or ?

In the data tab, when you already have one WHERE filter active, the "Quick filter" menu replaces the active filter with the new filter. You can instead append the new filter when you press Shift with the mouse-click on the filter in the menu.

this will append the new filter using AND:

j IS NULL AND name IN ('abcd')

Thanks, ansgar, for the response

No, I need on the first step - select department in Sales in department_id column from dropDown (that works prperly). And in the column product_id is Sales I want to see a just list of products based on selected department_id on the first step using foreign key to the table Products (Table Products contains columns: id, name, department_id)

So I need list of items filtered by another column using foreign key on those two columns in dropdowns

Ah, I think I get what you mean now. That's not possible in HeidiSQL, the items in the foreign key dropdowns are never filtered.

Is it possible to add this feature into backlog? :)

I think the idea is only valid for a few use cases, and not in generic means for HeidiSQL. The sales table itself has no problem with products from a different department. That's just the logic you designed for your application. This is perfectly ok. But that should be part of some frontend UI for viewing these sales.

Thanks for the response. Got it.

Yes, that is certainly just front-end feature. If someone asks for the same capability, please note my vote

This will probably be a part of the solution for issue #762 - an enhanced foreign value finder, replacing the current drop-down menu.