Substitute one person for another

Hello all!
I’ve just learned that one of our team members is shifting to a different team and we are receiving a new person to replace her. That means every instance where team member A appears in our shared Coda needs to be replaced with team member B.

So far, I have tried various combinations with the Substitute function - no dice. A lot of return errors. When the action does go through, I either get a “replacement list” of the entire team, something like [unrecognized value], or a straight up error message “Unable to execute invalid action.”

Here’s the low down: I need to replace person A with person B in the people column labeled POC for the table Application Statuses. I have thus far been referencing a second table called Application POCs where the first column, called Reviewers, is simply a list of the people who might appear as POCs.

My most recent button attempts:
[Application Statuses].Filter(POC.Contains([Person A])).FormulaMap(ModifyRows(CurrentValue,POC,Substitute(CurrentValue,[Application reviewers].Reviewer=[Person A],[Application reviewers].Reviewer=[Person B])))

[Application Statuses].Filter(POC.Contains([Person A])).FormulaMap(ModifyRows(CurrentValue,POC,Substitute(CurrentValue,ToText([Application reviewers].Reviewer=[Person A]),ToText([Application reviewers].Reviewer=[Person B]))))

ModifyRows(Filter([Application Statuses], POC.Contains([Person A])), [Application Statuses].POC, Splice([Application Statuses].POC,Find([Application reviewers].Reviewer=[Person A],[Application Statuses].POC),1,[Application reviewers].Reviewer=[Person B]))

I feel like I’m making this overly complicated but since they are People and not text strings…not having great luck here. Any help is appreciated!

Haven’t figured it out yet, but for anyone else struggling to reference People, you need to type the @ to call that data. No need to reference them from another table :woman_facepalming:

SOLVED. The magic is two parts - using ToText and calling the People correctly using the @ symbol when typing them in.

Final button formula:
[Application Statuses].Filter(POC.Contains([@Person !])).FormulaMap(ModifyRows(CurrentValue,POC,Substitute(ToText(CurrentValue.POC),ToText([@Person A]),ToText([@Person B]))))

1 Like