Editable Lookups

[mshah72]

I have a base with 2 tables - Teams and Projects (as shown below).

Every 3 months the leader changes. Now if I update the leader name in Teams table, it updates the leader names in the previous records too in the Project table (because of Lookup). How can I prevent updating the lookup values in the previous records?

Please try to provide a detail solution.

[FlywheelConsult]

What do you mean by previous records? Are you trying to keep a running log of who has been in charge of a project? As it stands, your project table is always showing the current team leader which makes sense to me. 

If you mean that some of those projects are old projects and you want to know who the team lead was at the time, then make a separate team lead table and link it to project, don't do a lookup off of the team table. You can also link the team lead table to the teams table so you always have a record of the current team lead. This way you can update the team and the project lead separately and it won't affect the other. 

[mshah72]

@FlywheelConsult Yes, you have understood it right - some are old projects. But for the task assigner, it is difficult to remember the Team Leader's name, but it is easy to remember the team name. So is there any alternate solution?

[FlywheelConsult]

The key is to make the team leaders table. If you're creating a new project, when you assign a team to the project, you can have an automation automatically link the current team leader to the project. In your projects table have a lookup field that shows the current team leader of the linked team then the automation can take that value and apply it to another field that links to the team leader table. This way a person assigning the team doesn't have to remember the current team leader.

[mshah72]

@FlywheelConsult, can you help with what logic I can set for the automation to do this? So what my thought is, I have a table with all the team members in it. I will mark the leaders with some tag and then link a particular view to my Teams page. How and what can I automate to auto-populate in the project table?

[RdMedia_srl · Accepted solution]

you could have this structure

this is the PROJECTS table (here are shown all the field but you can hide the lookup field "Name (from Teams) and "Current Team Leader (from Teams)". 

this is the TEAM table where you have the "Current Team Leader" field for your Team that you can change (linked record to last table "Team Leader Proj")

and this is the last table "Team Leader Proj"

then the you have this automation that as soon as you assign a TEAM to your PROJECTS table update the record of the project with the "current leader" of the that TEAM

these are the conditions:

and here you update the "Team Leader Projects" linked field record of the PROJECTS table with the value of the "Current Team Leader (from Teams)"

hope this helps....