Skip to main content
Solved

Intelligently concatenating links to the same table

  • October 17, 2024
  • 4 replies
  • 48 views
J
Forum|alt.badge.img+4
  • JKrug
  • New Participant
  • 3 replies

Hi All,

I'm struggling with finding the right way to deal with multiple links between two tables.

I have a Base with a People table, and a Milestone table, as well as a Teams table. Milestones have an Owner (link to People), and several teams (link to Teams table). Each team has a link (multiple entries allowed) to the People table. 

This works great for assigning people to different roles on each milestone and have a singular Owner who is leading/responsible for the milestone.

However, I want to be able to go to the People table and have a list of all milestones a person is assigned to, no matter whether the person is the Owner or in a team. I have found a way to use a lookup to get all the milestones a person is assigned as a team member, but I need to add the Owner to this list. I don't want to do a simple formula doing a concatenate, because then I can't filter by People in the interface to show everything a person is working on in the calendar/timeline/lists.

So; How do I combine the "Milestones as Owner" link with the "Milestones as Team member" lookup into a single field I can filter/sort/group by?

Best answer by TheTimeSavingCo

Close!

Here's what I have: 

So, the challenge is when I have:

I want to have the "Entire Team" field to have both the Owner (1:1 link) and all the people in the Teams.

It is more of a Matrix organization: Each Milestone has a unique set of Teams, made up of People to best match the needs of that Milestone. No Team is on multiple milestones.

I can use a concatenate formula on Owner and People (from Milestone Teams), but that cannot be used in the Interface to, for example, list all Milestones a person is involved in (either as Owner or as Team member).


Ah I see!  Hmm, I feel like you're going to need to create a linked field to "People" and then use an automation to make sure that that linked field always contains the Owner + Team member records I'm afraid

4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hmm, I think I'm missing something.  Let me know what I've got wrong with the setup below and we can take it from there


Link to base

Adam, Airtable Consultant - thetimesaving.company
J
Forum|alt.badge.img+4
  • JKrug
  • Author
  • New Participant
  • 3 replies
  • October 18, 2024

Hmm, I think I'm missing something.  Let me know what I've got wrong with the setup below and we can take it from there


Link to base


Close!

Here's what I have: 

So, the challenge is when I have:

I want to have the "Entire Team" field to have both the Owner (1:1 link) and all the people in the Teams.

It is more of a Matrix organization: Each Milestone has a unique set of Teams, made up of People to best match the needs of that Milestone. No Team is on multiple milestones.

I can use a concatenate formula on Owner and People (from Milestone Teams), but that cannot be used in the Interface to, for example, list all Milestones a person is involved in (either as Owner or as Team member).

TheTimeSavingCo
Forum|alt.badge.img+31
  • TheTimeSavingCo
  • Brainy
  • 6410 replies
  • Answer
  • October 19, 2024

Close!

Here's what I have: 

So, the challenge is when I have:

I want to have the "Entire Team" field to have both the Owner (1:1 link) and all the people in the Teams.

It is more of a Matrix organization: Each Milestone has a unique set of Teams, made up of People to best match the needs of that Milestone. No Team is on multiple milestones.

I can use a concatenate formula on Owner and People (from Milestone Teams), but that cannot be used in the Interface to, for example, list all Milestones a person is involved in (either as Owner or as Team member).


Ah I see!  Hmm, I feel like you're going to need to create a linked field to "People" and then use an automation to make sure that that linked field always contains the Owner + Team member records I'm afraid

Adam, Airtable Consultant - thetimesaving.company
J
Forum|alt.badge.img+4
  • JKrug
  • Author
  • New Participant
  • 3 replies
  • October 21, 2024

Ah I see!  Hmm, I feel like you're going to need to create a linked field to "People" and then use an automation to make sure that that linked field always contains the Owner + Team member records I'm afraid


Thak you! That's what I was hoping to avoid, but I'll add an Automation to watch those fields and update a semi-hidden field for "All People".

(I already have a few automations as workarounds to "limitations" in Airtable's base functionality like 🙄 circular references)


Terms & ConditionsAccessibility statement