cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abrae005
Kudo Collector
Kudo Collector

Using UPDATEIF and SUBSTITUTE at the same time

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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")
        }
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

7 REPLIES 7
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
abrae005
Kudo Collector
Kudo Collector

@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
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
abrae005
Kudo Collector
Kudo Collector

@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 NameOutage DateRecipients
System A8/1/22Bob, Jim, Karla
System B8/15/22Karla
System C8/2/22Jim, Marc, Anthony

 

Email List:

NameEmail
JimJim@Company.com
KarlaKarla@company.com
MarcMarc@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
Super User
Super User

@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")
        }
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
abrae005
Kudo Collector
Kudo Collector

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

abrae005
Kudo Collector
Kudo Collector

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,516)