thiI will try to rephrase it a little better, I am at home and the site is at work so I can't send screenshots but I attached a word doc to hopefully make it a little more clear.
Employees will request a transfer by entering the request in SharePoint via an InfoPath form. This will be in EmployeeList. The employee will enter their current department and all the departments that they want to transfer to (a Multi-select textbox) Once this information is entered, the pertinent information will show up in each department's view. Each department will have 2 views, One is "in Route" to show requests that have been entered but the paperwork has not arrived yet. The other view is "Pending" which will show requests that have been entered and the formal request has been received. I do a lot of database programming and maybe my brain is just stuck on relational tables and SQL queries to get the desired results.
Here are a few of my thoughts
One list with the employee ID, Name, currentDepartment, RequestedDepartments
This list will have only one entry for each employee requesting to transfer
one list with employee Id, ReceivedDepartment, ReceivedDate
This list will have an entry for each employee for each department they want to transfer to. So if an employee wants to transfer to 3 departments, there will be 3 entries.
With this method, I thought of a couple ways it might be handled but just can't make it work
Somehow have InfoPath submit the data to the two different lists, but I don't think that is possible
I could have one InfoPath form for the initial entry and then another form for the receiving departments entry. On the Receiving Department's InfoPath form, they will enter the employee ID and the employee's relevant data will be populated via a lookup column? and then enter the relevant data. With this, how would I validate the data so there will only be one employee entry for each department they are transferring to so then you don't Get duplicate entries.
And on a wild thought, have the EmployeeList with a field for each Department that will contain the date that the package was received. (there are 35 departments) then on the InfoPath form. There will be 35 date boxes (one for each Department) stacked on top of each other. Have a department selection list and when the department is selected, the corresponding date picker will become visible and the rest hidden.
I think the second one would be the path to go but not sure if there is a better method