Seems easy, and you seem to have figured it all out.
One employee has Many Annual Employment Snapshots.
One Employee Annual Snapshot has Many Annual Snapshot Mentor Assignments (new table that joins with Mentors and Annual Snapshots as its two parents.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com------------------------------
Original Message:
Sent: 11-03-2022 12:48
From: Hagos Hagos
Subject: Complex Table Relationships Question
I currently have the following:
- An employee table (built already)– where each individual gets a row with their demographic info
- An annual employment snapshot (built already) – where each individual gets a row for each year they're employed (e.g., if someone has been employed for five years, they get five rows)
Do you have any ideas on how I can achieve the following?
- Track each individual's mentors by year in the employee snapshot table (in any given year, an individual can have several mentors). I envision it looking like what's below.
- Have the mentor linked to the employee table (so, each mentor can be linked to their demographic information). I envision it looking like what's below.
Employment snapshot data:
Name (links to employee table) | Year | Mentors (links to a mentors table) |
Employee1 | 2020 | Mentors |
Employee1 | 2021 | Mentors |
When I click Employee1's 2020 mentors table link I get:
Name (links to employee table) | Year | Mentor (also links to employee table) | Role |
Employee1 | 2020 | Mentor1 | Role1 |
Employee1 | 2020 | Mentor2 | Role2 |
Employee1 | 2020 | Mentor3 | Role3 |
And the mentors are linked to the employee table (Mentors will always be someone in the people table)
Thank you.
------------------------------
Hagos Hagos
------------------------------