The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
Oct 17, 2024 09:32 AM
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?
Solved! Go to Solution.
Oct 19, 2024 06:30 AM
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
Oct 17, 2024 06:14 PM
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
Oct 18, 2024 07:35 AM
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).
Oct 19, 2024 06:30 AM
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
Oct 21, 2024 02:28 PM
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)