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.
