cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular 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
Super User III
Super User III

@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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (13,610)