cancel
Showing results for 
Search instead for 
Did you mean: 

Function request : Delete all records in a table and update all records in some conditions( can delegate )

Sometimes , we need to delete all records in a Dataverse table (for initialize etc.) or update all records in some conditions.

I think this is not a special case and common business needs.
But this is not straightforward today ,because RemoveIf nor UpdateIf are not delegable.
If there are simple bulk update/delete delegable functions , it would make happy to all users who use Power Apps in Teams.

-----------------------------------------------------------------------------

Details:

 

RemoveIf(true) is not delegable , so we can only delete 500 records in one operation.
So is UpdateIf( some condition ) .

 

For RemoveIf (true), we can also take a workaround using timer control as follows:

Delete the records with RemoveIf (true) first, repeat the timer if there are other records, stop otherwise.

But it's not a simple solution and require much time to remove many records.

 

Update records is more complex if we want to update columns those are not key columns in the condition.
For example , we want to update all records with 'Cost' column to 200 where the 'Month' is 'October' in 'Order' table.


If the table has records under 500 rows , we can use
UpdateIf( Order , Month="October",{Cost : 200} );.

 

We  can also use below if the records count we want to update is under 500,

ClearCollect(_colUpdate , Filter(Order,Month="October") );

ForAll(_colUpdate As S,
     Patch( Order,

                 LookUp(Order As T, T.Order = S.Order) ,

                { Cost = 200 } );

);

 

But if the records number we want to update are much more 500 ,UpdateIf  nor ClearCollect/ForAll don't work .

(UpdateIf is not delegable , so we can't update correct the target records, ClearCollect/Collect  have limits to get records for delegable records limit number:500.)

 

I think below is one solution, but not so simple.

 

First, Collect records with Filter some keys for divide up to 500.

Seconds, update all records in collection with ForAll and Patch.

 

Like this:

(In this case , we have Id column( auto-number) and each Filter results would be under 500 .)
-------------------------
Clear(_colUpdate);
Collect( _colUpdate,
Filter(Order,Month="October",EndsWith(Id,"0"),
Filter(Order,Month="October",EndsWith(Id,"1"),
Filter(Order,Month="October",EndsWith(Id,"2"),
..........
Filter(Order,Month="October",EndsWith(Id,"9"),
);
ForAll(_colUpdate As S,
     Patch( Order,

                 LookUp(Order As T, T.Order = S.Order) ,

                { Cost = 200 } );

);
-------------------------
This works , but not so simple and if we can't divide records in the manner, we can't meet our requirements.
It would be more suitable if we can delegable bulk update with a single function.

Status: New
Comments
mgoto
Regular Visitor

I've just edited my comment to clarify the issue , Thanks.