let's say I have one list with my company's departments and one list with all employees. My list of employees has a lookup field to select the department.
Now I want to have one page with a list of all departments and below a list with all the employees. If I select one department in the list I'd like my employee list to only show employees from this department.
How can this be done?
Thanks in advance
You can simply achieve this by using webpart connections. Let's say you already have a webpart page with the two webparts, one below the other, and there is a lookup column in the employees list to the department list, as you said in your question.
You just have to edit your web part page and select from the webpart dropdown menu Connections-> Send Row of Data To -> Employees.
A little wizzard will open. There you select "Connection Type: Get Filter Values From" and click "Configure". On the next step select "Provider Field Name: Title" or whatever is the name of the field on which you want to filter on. In this case I suppose it's the Title of the Departments list. Below select "Cosnumer Field Name: Department" or whatever is the name of the lookup field in your Employees list pointing to the Departments list.
Then click Finish. Click Save or Stop Editing to save the page. Don't forget to publish it, if you have version control activated.
Now a new column appeared on the Departments list. It will allow you to select a department and it will filter automatically all employees below. The only disadvantage is, that OOTB, as far as I know at least, it cannot show all data, it is always applying filtering on the first row from the upper list.
I hope it was you were looking for. :)
You could also use the jQuery library (by Marc Anderson) to implement the connected look up fields, see example
I agree with Falak, you'd want to use something like Marc Anderson's SPServices library, but I'd recommend using the cascading dropdown function.
