Is it possible to create a formula or script that will automatically add collaborators to a collaborator field, under a given condition?
Specifically, I’d like Airtable to automatically add a “collaborator” to one multi-select collaborator field when that collaborator is added to another specified field elsewhere in the base.
In my project mgmt base, we have separate collaborator fields for our Projects and Tasks table: (1) an “assigned to” field in Tasks (defining ONE person who is currently working on a given task) and (2) a “project team” field in Projects (defining ALL the people involved in the project).
Both fields are necessary for different types of tracking. The Tasks field allows a user to see the task on his to-do list. The Projects field allows the Project to show up in his “current projects” feed (essenitally allowing him to “follow” the project, even if a task is not currently assigned to him). Unfortunately, this approach often requires double-entry (since, by definition, if you’re “assigned to” the task, you’re a member of the “project team”). In our current workflow, the Tasks field changes constantly as a task gets passed between team members. The Projects field changes less often, but as a project goes on, different sets of people need to follow it.
So what I’d like is a formula or script that automatically adds a collaborator to the “project team” field if/when they are selected in the “assigned to” field of a related task.
Note that this action only has to go one-way. The collaborator never needs to be removed from the Projects field (unless he manually removes himself later), but (for example) if over the course of a week, 5 people are (at any point) assigned to a task (or tasks) related to a single project, all 5 should appear in that project’s “project team” field.