cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DanielS178
Frequent Visitor

RemoveIf and UpdateIf not working with SQL

I have an app that from time to time the removeif and updateif methods stop working.

I have a gallery that I'm trying to update a record or to remove if upon certain conditions. I have something like this:

ForAll(Gallery.AllItems,

If(ToUpdate,

UpdateIf(Table, ID = Value(Label_ID.Text), {...}),

If(ToDelete,

RemoveIf(Table, ID = Value(Label_ID.Text)));

 

I have a label that stores the ID of each record because the sql table has a couple transformations before being displayed.

Within this app this doesn't work but I have another app the has the exact same code and it's working fine.

 

Any idea why is this happening?

 

Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions

I'm just working on something similar and the following is working for me:

 

ForAll(Gallery1.AllItems,
With({wID: Value(lblID.Text)},
Patch('[ChemRec].[Recommendation]',LookUp('[ChemRec].[Recommendation]',ID = wID,
{
    User: txtEmailTo.Text
}
)))

 

Instead of using UpdateIf, I'm using Patch. For deletion, you might be able to use Remove (specifying a record) instead of RemoveIf (specifying a condition), but I haven't tried that.

To get the LookUp to delegate, I am setting a local variable (using With) to the Value of the label lblID in the gallery. If you try to include the Value(...) function within the LookUp it will become non-delegable, so you need to resolve outside of the LookUp.

 

Edit to Add: Just tried similar with Remove (where RemoveIf failed to remove all records as some were outside the delegation limit) and it was successful - the code I just ran is below:

ForAll(Gallery1.AllItems,
With({wID: Value(lblID.Text)},
Remove('[ChemRec].[WeedPestDiseaseOther]',LookUp('[ChemRec].[WeedPestDiseaseOther]',ID = wID )
)))

Note that it is not very quick - maybe 0.5 second per record updated or removed. Also note that this will be using up API calls and if memory serves you only get 100 calls per 10 seconds, so if you might have numerous users performing this operation at the same time, that could be a problem.

For performance and scalability a Flow calling a Stored Proc would be a better solution.

View solution in original post

8 REPLIES 8
mdevaney
Super User
Super User

@DanielS178 

Can you please elaborate on what is meant by 'stop working'?  Does a partial set of records still get updated?  Do none of the records still get updated?

Please provide any information I would need to re-create this issue on my side.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

@mdevaney 

 

None of the records are updating.

I have something like this:

 

ClearCollect(UserHours,
Filter(
Search(Table_View, UserID, "UserID"),

Day >= SelectedDate && Day <= SelectedDate + 12));

 

ClearCollect(UserHoursGroupBy,

GroupBy(UserHours, "Column1", "Column2", "Column3", "UsersGroupByColumn"));

 

Now with the UserHoursGroupBy collection I use a gallery like this

PowerApps_RegistoHoras.JPG

I don't know if this is the best way to do this but I have the ID of the record hidden behind each value so that I can use it when I execute the ForAll method to updated it or to remove it.

 

If you need me to elaborate more, please tell me.

 

Thank you.

 

Hi @DanielS178 

The reason why RemoveIf and UpdateIf work intermittently is because both of these functions are non-delegable.

There are some more details in the documentation here.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-remove-removeif

Because of this, these functions will not work correctly if your source table exceeds the maximum non-delegable query limit of 2,000 rows.

A workaround would be to create a Flow to delete the records, and to call the Flow from your app.

@DanielS178 
I shared @timl 's suspicion about the RemoveIf function but was simply trying to collect a little bit more information before I came to a conclusion.  Why don't you try his suggestion and removing the records via a Flow instead?

I'm just working on something similar and the following is working for me:

 

ForAll(Gallery1.AllItems,
With({wID: Value(lblID.Text)},
Patch('[ChemRec].[Recommendation]',LookUp('[ChemRec].[Recommendation]',ID = wID,
{
    User: txtEmailTo.Text
}
)))

 

Instead of using UpdateIf, I'm using Patch. For deletion, you might be able to use Remove (specifying a record) instead of RemoveIf (specifying a condition), but I haven't tried that.

To get the LookUp to delegate, I am setting a local variable (using With) to the Value of the label lblID in the gallery. If you try to include the Value(...) function within the LookUp it will become non-delegable, so you need to resolve outside of the LookUp.

 

Edit to Add: Just tried similar with Remove (where RemoveIf failed to remove all records as some were outside the delegation limit) and it was successful - the code I just ran is below:

ForAll(Gallery1.AllItems,
With({wID: Value(lblID.Text)},
Remove('[ChemRec].[WeedPestDiseaseOther]',LookUp('[ChemRec].[WeedPestDiseaseOther]',ID = wID )
)))

Note that it is not very quick - maybe 0.5 second per record updated or removed. Also note that this will be using up API calls and if memory serves you only get 100 calls per 10 seconds, so if you might have numerous users performing this operation at the same time, that could be a problem.

For performance and scalability a Flow calling a Stored Proc would be a better solution.

View solution in original post

@PaulD1 

Thanks for verifying that Remove works in a delegable way. That's very useful.

I agree that calling a Stored Proc with Flow would be a better solution. I guess you could pass a comma separated list of IDs to the Stored Proc, which would enable you to delete the multiple records with a single call.

@PaulD1 
Very nice code.  Thank you for sharing!

@PaulD1 

That works perfectly, thank you!

About the performance of the app with this solution, it's something that I'm going to take in consideration and evaluate with time.

Thank you for your help!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,388)