I am trying to work out a way to make 3 tables to reference one another but can’t get it to work.
I have 3 tables;
The ‘People’ table is a list of key people, each of which are tied into group (numbered 1 - 12). As a side note this is a single select as I want to be able to move the people around into different groups as I need using a Kanban view.
The ‘Planner’ table has a Link to ‘People’ and pulls across various fields which are relevant to the planning of my project.
At this point all is fine, everything updates accordingly in the planner table e.g. if I move person A to another group within the ‘People’ table that field automatically updates in the ‘Planner’ table. I have done this by having a formula in my primary field in ‘Planner’ which references the linked group field (the 1 - 12 I mention earlier) from ‘People’.
The problem I have is Key Dates. Again they are organised by group (1 - 12) as there are various dates that are fixed to the group.
What I want is to link the Key Dates tables into Planner. Problem I have when I do the link, and then change the group the People are in, it all goes out of sync. What I need is for the link from Key Dates to monitor the name of the Primary field, and if that moves down e.g. a Person is changes from group 1 to 2, the key dates for that group doesn’t move with it.
As an example
I move Person A from Group 1 to Group 2
Person A updates to the new group in Planner
The Key Dates link needs to stay linked to group 1, but instead it moves to group 2
I have no idea if this makes sense, it’s really stumped me. I thought an automation may help but can’t work out what it needs.
Happy to add more detail to anyone who can somehow muddle their way through my ramblings!