Sorry about the title on this one. I was having a hard time explaining it.
What I am trying to do this this.
I have a new employee that is hired. That employee is hired into a department. Based on that department, that employee may be able to work in many other departments.
I could look like this:
Employee 1234 is hired into department 619 - Therefore they can also work in 620, 622, 625 and 626
Employee abcd is hired into department 620 - Therefore they can also work in 619, 633, 645 and 700
I was thinking I could do an if/then function. I could have it return something like
If home = 619, then the return field could be 620, 622, 625, 626. Then maybe somehow transpose it? but then how would I get the employee id to also be associated with each line.
I think you should return to the data analysis. At the moment the employee / department table above might be deceptive because it hides the bidirectional one to many relationship i.e unless a department only has one employee, then ‘abcd’ could also work for department 620.
The other issue is that it is not clear whether the ‘department hired for’ is material - at the moment that property doesn’t appear.
IMHO I’d consider if you could semi normalise it, which implies an employee list and a department list, and then use your linking list in the middle, with the additional property of ‘hired for’ so a single true value would be placed against the relevant pair. You could then use ‘Lookup’ to pull the relationships together.
What happens is we get a list of new employees. Lets say that a nurse is hired into department 619, which means she can also work in 620, 622, 625, 626. That is known information and not necessarily on a data table anywhere as im not sure I would know how to set that up correctly.
The output I would need is what I copied above. The data I get would show me the new employees, which would be my first filter. Then I would have a list of them, the department they are hired in to. Then my secondary data source would be the table, like I just pasted, that shows the hire dept and the optional departments. We can get as man as 150 new employees a week. The output would need to be in the format of:
