Choose Users for projects

Question

Good morning.&nbsp; I have an Employees Table, and a projects table.&nbsp; I set up a relationship where where 1 employee has many projects.&nbsp; But I need the project to have 2 drop downs - one where I choose a Sales Rep, and one where I choose a Project Manager, but both are based on the Employees Table.&nbsp; On the form for Projects, I want to be able to choose a sales rep from a drop down list, and have it display the Name &amp; Phone number for the Sales Rep, and then choose the Project Manger and have it display the same info.
How can I make this work?&nbsp; &nbsp;TIA------------------------------Allison Watson------------------------------

georgehambel · Answer

Hey Allison!
You can accomplish this by duplicating your relationship from your parent table Employees to your child table Projects. After this you would do two things...

Change the reference field of your 1st relationship to be titled "Related Sales Rep", and change the reference field of your 2nd relationship to be titled "Related Project Manager".
For your drop down lists I would recommend creating a report on your parent table for each. In these reports, filter the first to only show employees in the "Sales Rep" role, and the second to only show employees in the "Project Manager" role. You can then base your record pickers on the reports you just created.

Hope this helps you out!
------------------------------George HambelProject ManagerSynctivate Inc------------------------------

georgehambel · Answer

Happy to help!&nbsp;Duplicating relationships is not always a best practice and other options should be considered prior to doing so, but in your scenario you should be just fine.------------------------------George HambelProject ManagerSynctivate Inc------------------------------

allisonwatson · Answer

That helps a ton!&nbsp; I had no idea you could duplicate relationships.&nbsp; Thanks!------------------------------Allison Watson------------------------------

donlarson · Answer

Allison,

As long as you only have Sales Reps and Project Managers, I would do it the way George said.

However what if you have Auditors, Marketing Lead, Customer Liaison and Engineering Rep?

Then I would move to this:

This lets you have an unlimited number of people all defined by the Team Roles.&nbsp;

George's method makes a lot of reporting easier, you do not have to click Add Project Team and so on.&nbsp; However a big team really needs a different architecture.
------------------------------Don Larson------------------------------

Forum Discussion

Choose Users for projects