Using UPDATEIF and SUBSTITUTE at the same time

[abrae005]

I'd like to use an UpdateIf function to replace text (similar to the Substitute function) in a SharePoint list where some of the text in a particular field matches.

For example

• List Name: "Test List"
• Field Name: "Email_Recipients"
• Find: "Jim" anywhere in the field
• Replace with: "James"

So, if the list item currently has a value of "Bob, Jim, Mary", I'd like to update it to be "Bob, James, Mary". And if the list item currently has a value of "Jim", I'd like to update it to "James". And if the list item is "Bob, Mary", I don't want to update at all.

[RandyHayes]

@abrae005 

Yes, you can do this, but delegation is going to be a big concern as you are looking for text inside of field values.  Using operators like "in" will not be delegable. 

So, if your data is less than the maximum limit (2000) then you can do what you are looking to do.

But, I would address the question above before proceeding with a formula that is not going to produce what you want.

I hope this is helpful for you.

[abrae005]

@RandyHayes - Yes, the number of entries in the list will stay under the delegation limit. I'm thinking at most there'll be 150-200, and even that's probably a high estimate.

[RandyHayes]

@abrae005 
That is good.  It will do what you need then without delegation problems (you will most likely see warnings though about delegation that you can ignore).

Now the next question is - are you trying to change one name at a time or multiple names at a time.  In your example it seemed like just one, but wanted to get a better idea on what you are trying to do.

[abrae005]

@RandyHayes I've built a powerapp to send system outage emails to various people. There are 2 sharepoint lists - one has details about the systems and the outage dates, one has a list of people and their email address.

The recipients for each email are stored in the Systems list, as a text field.

System Name	Outage Date	Recipients
System A	8/1/22	Bob, Jim, Karla
System B	8/15/22	Karla
System C	8/2/22	Jim, Marc, Anthony

Email List:

Name	Email
Jim	Jim@Company.com
Karla	Karla@company.com
Marc	Marc@company.com

Supposing Jim leaves the company, and Gabby takes over. I'd update the Email sharepoint list to change "Jim" to "Gabby" and her email, then I'd want to update the Systems list to put Gabby's name in everywhere that Jim was. That's why I need to use the UpdateIf and Substitute.

[RandyHayes · Accepted solution]

@abrae005 

Got it!  Thanks for the explanation.  

So taking the exact example of changing Jim to Gabby, the formula would be the following:

Patch(
    SystemsList,
    ForAll(
        Filter(SystemsList, "Jim" in Recipients), //note: not delegable!
        {ID: ID,
         Recipients: Substitue(Recipients, "Jim", "Gabby")
        }
    )
)

[abrae005]

Thanks Randy. I'll test it out. Hopefully this week.

[abrae005]

Very delayed follow up, but @RandyHayes , your code works perfectly. Thank you!

[KathanJain]

You can use updateif with substitute like below  

UpdateIf(
        PG_Meetings,
        AnnouncementReqNumber = Value(varRequest.ReqNum) && varRequest.ManagerEmail in RequiredAttendeesEMail,
        {
            RequiredAttendeesEMail: Substitute(
                RequiredAttendeesEMail,
                varRequest.ManagerEmail,
                varMgrMail
            )
        }
    )