Assigned Tasks - this displays all open/closed assigned tasks, and their respective details including:
A. A unique “Assigned Task ID”
B. A “task ID” which identifies the specific type of task - as sourced from the “Task Library”
C. A “role ID” which identifies the role that is assigned to the given task - as sourced from the “Roles” table
D. A “employee ID” which identifies the specific employee ID linked to the “role ID” - as sourced from the the “Employees” Table
Primary field is - “Assigned Task ID”
Columns in the “Assigned Tasks” table:
-Assigned task ID
-Task ID
-Role ID
-Employee ID
-Employee Full Name
TABLE 2 - “TASK LIBRARY”
A table of all possible tasks that can be completed.
Primary field is - “Task ID”
Other columns include lots of attributes relating to the “Task ID”, such as description, time taken etc.
TABLE 3 - “ROLES”
A table of all roles in the business.
Primary field is - “Role ID”
Other columns include lots of attributes relating to the “Role ID”, such as business team, business department etc
TABLE 4 - “EMPLOYEE”
A table of all employees in the business.
Primary field is - “Employee ID”
Other columns include lots of attributes relating to the “Employee IDs”, such as full name, date of birth, proof of work etc
To help elaborate here is some detail as to how the different variables relate to each other:
-Every “assigned task ID” can only have one “task ID”, but every “task ID” can have many “assigned task IDs” - so “one to many”
-Every “task ID” can have only one ” role ID”, but every “role ID” can have many “task IDs” - so “one to many”
-Every “role ID” can have only one “employee ID, and every “employee ID” can have only one “role ID” - so “one to one”
-Every “employee ID” can have only one “employee full name, and every “employee full name” can hav
e only one “employee ID” - so “one to one”
DESCRIPTION OF CHALLENGE
I’m very new to Airtable, so seeking some guidance as to the best way to set up a base such as the above.
Originally, I tried to set this up through just have 4 tables and then using both functions of “linked to table…” and then also “lookup”.
The challenge with this approach is it ends up requiring a lot of duplication / manual effort.
To illustrate this, in the first table of “assigned tasks”:
I link the table to the table “Task Library” and select a given “task ID” >>> all good so far…
I then “lookup” a “role ID” using the “task ID” so that I have a cell now which lists the appropriate “role ID” for the given “Task ID selected” >>> all good so far…
Next is where the issue arises…there’s no way of me taking the “role ID” which has automatically been pulled in from the “Task library table” and retrieving the corresponding “employee ID” from the “roles table”.
Hopefully the challenge is clear from the above…I can’t use the “lookup” value of “role ID” from the “Task library” table to pull the relevant “Employee ID” from the “Roles table”
JUNCTION TABLE??
I’ve been searching around, and suspect that the required approach is a “junction table” based on my readings:
But then to create a junction table, I understand that I’d have to manually write out every single possible combination between the following variables, and then always keep this updated:
-Task ID
-Role ID
-Employee ID
-Employee name
This could be super time consuming.
In excel, I could have solved this entire issue with some simple vlookup formulas, so hoping there’s an easy solution that I am also missing here. I’m not even sure how I would go about creating a “junction table” to be able to end up with a good set up for these requirements.
