Forum Discussion

DavidChotin's avatar
DavidChotin
Qrew Trainee
8 years ago

Many to Many team assignments

Hi, I'm stuck with this and hoping someone has a solution. I have a many-to-many relationship set up with three tables: Projects, Employees, Proj&Empl. I'm using the P&E table to form the many-to-many relationship between the other two. Each Employee record has a field for "Team", and there are four possible options for "Team" (let's say A,B,C,D for simplicity). Each Project has one Employee from all four teams. I have already created a report that shows me all four Employees assigned to each Project. What I want is to have a field in the Projects table that shows me "Team A Employee Assigned", so that I can use it in reports. Is this possible?
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    8 years ago
    In the projects to P&E relationship, count the number of P&E records where team is equal to A.

    From there, you can create a formula checkbox on the Project table with the formula:
    if([# of P&E Team A Assigned]>0,true,false)

    Let me know if that helps.
    • DavidChotin's avatar
      DavidChotin
      Qrew Trainee
      8 years ago
      Oops my original question wasn't clear. I need to pull the NAME of the employee, not just if they've been assigned.

      I was able to come up with a way to get the RID of the right team member - can I use that to fill a formula field or something?
    • Eric_J_Hansen's avatar
      Eric_J_Hansen
      Qrew Cadet
      8 years ago
      You could create a formula field where if Team A Employee RID is 1, show the name of that person by hard coding it in the formula. If you have a bunch of people or lots of changes in staff, this wouldn't be the best way but could work in limited situations. Another way would be to relate the team members to the projects table 4 times, one time for each team. Then, with your 4 summary fields from the P&E table, you can use those and automatically populate the reference fields by changing them to a formula numeric field type, with the formula for the Team A employee to project reference field being: [Team A Employee RID], the summary from the P&E table related employee field where team is A.  
    • DavidChotin's avatar
      DavidChotin
      Qrew Trainee
      8 years ago
      Thanks, I used the first idea and made it work. One thing that was messing me up was how our tables were built - a lesson for the future.