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
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.
Solved! Go to Solution.
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")
}
)
)
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.
@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.
@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.
@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 | |
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.
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")
}
)
)
Thanks Randy. I'll test it out. Hopefully this week.
Very delayed follow up, but @RandyHayes , your code works perfectly. Thank you!
User | Count |
---|---|
126 | |
87 | |
84 | |
75 | |
69 |
User | Count |
---|---|
214 | |
178 | |
140 | |
105 | |
83 |