Is it possible to filter a dropdown (lookup) on a result from another dropdown in the same form?
I have a form for a ticket. On this form I have a dropdown with departments. When I choose a department, I would like to filter the next dropdown with employees for the selected department.
When I use a reference with department_id and employee_id, then the dropdown depends too much on the departments. I want to have a dropdown with all employees if I don’t select a department.
Is this possible with a reference, filter, view?
Can anyone help me?
Best answer by Erwin Ekkel
You could do the following:
Create a view of the employee table that also has a placeholder value that holds all records something like this:
Select department_id,employee_id, [other columns] from employee --original data union all Select -1,employee_id, [other columns] from employee -- placeholder show all rows
Then make an expression column on the table where you want to show these columns in the form. The expression column uses an isnull with a replace to the placeholder value (-1): isnull(department_id,-1).
Then create two references: a reference on the expression column and the employee_id to the view. a reference on department_id = department_id on the original table for the lookup.
Create a view of the employee table that also has a placeholder value that holds all records something like this:
Select department_id,employee_id, [other columns] from employee --original data union all Select -1,employee_id, [other columns] from employee -- placeholder show all rows
Then make an expression column on the table where you want to show these columns in the form. The expression column uses an isnull with a replace to the placeholder value (-1): isnull(department_id,-1).
Then create two references: a reference on the expression column and the employee_id to the view. a reference on department_id = department_id on the original table for the lookup.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
