First of all, sorry as I’m new with Airtable and perhaps I’m not using the proper terminology. I’ll do my best to explain myself. This is not a screenshot from my actual system, but I hope it helps me explain my problem.
I have a table of employees, a table of tasks, a table of projects and a junction table to join a Project (single link) with a task (single link) that also allows me to assign several Workers (multiple link to Employee table).
In this example, Captain America is assigned to the task of collecting the infinity gems (with other colleagues), but also to the task of returning them.
I want to see which employees are assigned to each project. So I’ve created a Lookup field in the “Project” Table.
As the same worker can be included in several tasks in the same project, when I create the lookup field, they appear duplicated.
Either using Lookup or Rollup (with the formula “Arrayunique”), Captain America appears duplicated. And if I try to count the number of workers it shows 5 people, when only 4 different workers are involved.
Arrayunique is useful in case there are several tasks with the exact same workers assigned on them. But if there are several workers and only some of them are duplicated, Arrayunique considers they are two different arrays, and shows all the workers, including duplicated ones. Let me explain this better.
I’ve added a new task “Back to normality”. Only Nick Fury is assigned to this task.
As Nick Fury is also the only worker assigned to “Call Captain Marvel”, Rollup realizes is a duplicated record and doesn’t show it, although Lookup is still adding it.
So my question is, how can I eliminate duplicates when there are several workers assigned to a task?
I hope is more clear now