Automate and manually edit multi-select list

[thaid]

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!!

[TheTimeSavingCo]

Hmm, I tried playing around with this to see if I could do it via automations and rollups / lookups alone but couldn't really figure it out, hopefully someone else has an idea for that.  The only way I can think to do it is via a script that'll consolidate all the tags from the linked records and update the record accordingly

[thaid]

Thanks for taking a shot! I had a similar experience and couldn't quite crack it. I've never dove into scripts, so not sure if I'll be able to solve it that way...