2 tables linked via multiple fields. I want to have the 6 fields from one table roll up into 1

[Umberto]

I currently have 2 tables: 

1. People
2. Projects

On Each project there are assigned multiple roles from people which are each linked columns, i.e. 

• Role1
• Role2
• Role3
• Role4
• Role5
• Role6

The reason I do this and not via a look up is because you can have many people working on a project but only a few will assume full responsibility. 

So now on the people table I am left with 6 project columns, which is fine and I can just hide these, but what I would like is for them to roll up into a single column where I can still get the functionality of a linked cell so that when I use interfaces or navigate I can still access the linked records from a single place vs 6. 

[Sylvain_Dionne]

Hi can you show (png) your tables? 

[Umberto]

Person Table

Project Table

These are the column headers. As you can see for each project I want to break out to the different roles and the tables are linked to a many to many relationship. 

I would like for the 6 columns in the people table to aggregate into 1 column while allows me to keep the record links. 

Current Options I have that I don't like:

• Currently I can use a formula to concat the 6 columns into one, but I would like to avoid that since I lose the record link feature.
• Another thing I can do is have each column in the project table be a look up against people based on their role, but the problem is, is that the logic doesn't hold up as if someone is of that role and working on the project, it doesn't mean they will take a lead role within the project. It's more arbitrary. Also again, I lose the record link freature.

[Sylvain_Dionne]

I don't know if this do the job for you,
Add a Junction Table between your tables to define Role, and assigne project and people from that table
Add a formulas ToPeoples (Project / Role), ToProjects (People / Role)
Show these fields in both tables Peoples and Project.
(see attached file)

Let me know

[Umberto]

hey, thanks for the reply but not exactly what I was looking for. 

I would like to have a person be the record and the column to aggregate all of the projects within the 6 columns on the people table as a way to then plug into an interface down the road. The method you shows now creates a unique id tied to the combination of the person, project, and role. 

I think I'll just lose the record link and go with one of the methods I listed above. Still really appreciate the help here. 

[Sylvain_Dionne]

Ok, good luck, Happy New Year

[TheTimeSavingCo]

Hm, what if you had a junction table between `People` and `Projects` called `Roles` or something?

Then each record in `Roles` would be linked to both `People` and `Projects`, and you'd set each person's role per project as needed?

You'd then add a bunch of lookups to the `Roles` table to pull data to the other tables as needed

[Umberto]

yeah this is likely what I'll be moving towards, but I wanted to keep the ability to access the record from this field. With a look up I lose the ability to click on the record directly. Reason I want this is to have navigation available via the interface features. 

[TheTimeSavingCo]

Woah, apologies to you both.  I don't know how I missed the conversation between you two and realize now that I just repeated Sylvain's suggestion.  Thank you for being so gracious with me

Hmm, if it helps, I'm able to click on a linked record inside a lookup in an Interace of type "Record Summary"?

And here's how the data's set up 

===

You won't be able to do it via a "Grid" Interface element, but then when I tried to click through to a normal linked record via the "Grid" Interface element I couldn't get that to work either.  (Do let me know if I'm doing something wrong!)