I believe this is an automation question, but would be happy to learn any solutions!

We use Airtable for a CRM that has 4 tables: City, Account, People, and Activity. (Cities have Account linked to them; Accounts have People linked to them; and People have Activities linked to them).

When creating new Activity records, we have "Activity Tags" that are multi-select for our different organizational programs.

We have already created a lookup field in the People table to see what Activities our various People have participated in. HOWEVER, we've found that lookup fields aren't the actual solution we need, because there are times where we need to associate a Tag with a People record outside of them formally participating in a recorded Activity.

So, we duplicated the same multi-select list Activity tags (i.e., the programs we offer) to the People table (and named the new field "People tags") and also to the Account table (and called them "Account" tags).

Is there a way via an automation or other Airtable trick to accomplish the following: Within the manually select "People Tags" dropdown list, automatically populate based on the Activity Tag lookup field, while also still allowing our staff to add manual selections?

From there, we'd repeat for Account Tags - lookup from People Tags, but also be able to edit manually for additional tags.

Let me know if you have ideas - thanks!!