cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EpicTriffid
Resolver I
Resolver I

Deleting Multiple Rows in SQL

Afternoon,

 

I'm trying to use Remove/Removeif to delete several rows in my SQL table that relate to a particular user ID. I've tried the All operator, but I think the problem is with delegation which, while set at 2000, is having trouble with my 8000+ items table.

 

I thought the easiest way was to filter the datasource at the beginning of the Remove statement, but while I can use a clear collect and see that the filter works and is picking up all the records I need deleting, the Remove does not seem to like me using the filter:

 

RemoveIf(
    Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID),
    Entry_ID = recordtoDelete.Entry_ID
);

 

There must be a way to do this. I'd be talking about 20 records to be deleting at max. I can't use Flow at the moment to help with this as I'm still in the grace period from the license changes, and I was told my MS Support that adding a new flow to my app would void the grace period.

 

Am I missing something obvious?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @EpicTriffid ,

Do you want to remove multiple records?

If so, I suggest you know more about Remove function and RemoveIf function.

1)Remove:

syntax:

Remove( DataSource, Record1 [, Record2, ... ] [, All ] )

The first parameter is the data source, the second parameter is the record that you want to remove.

However, please notice that you need to list the record that will be moved one by one .

It's not supported to directly use Filter() to represent all the records to remove.

So you should use formula like this:

Remove('[dbo].[Units]',
First(Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID)),
//the first record 
Last(FirstN(Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID),2)),
//the second record
....
)

2)RemoveIf

syntax:

RemoveIf( DataSource, Condition [, ... ] )

Since in your issue, you do not know the record number that you need to remove, so I more suggest you use RemoveIF function.

The first parameter is data source, the second parameter is condition.

However, in your original formula:

Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID),
    Entry_ID = recordtoDelete.Entry_ID

You have used the condition in the first parameter and you use the same condition in the second parameter.

Please try this formula:

RemoveIf(
   '[dbo].[Units]',
    Entry_ID = recordtoDelete.Entry_ID
);

 

Here's a doc about these two functions in details for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-remove-removeif

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
mdevaney
Super User
Super User

@EpicTriffid 

I would expect your could just use this shorter version of your formula since FILTER can be delegated

Remove(Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID));

 

Link to MS Documentation on Remove

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

 

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

 

Thank you for your reply! 

 

I was thinking the same thing, but I'm getting an error saying "Expected 2 or more arguments". I think it wants me to specify the item to remove, but there is no one item to remove, i need a few to be removed.

Hi @EpicTriffid ,

Do you want to remove multiple records?

If so, I suggest you know more about Remove function and RemoveIf function.

1)Remove:

syntax:

Remove( DataSource, Record1 [, Record2, ... ] [, All ] )

The first parameter is the data source, the second parameter is the record that you want to remove.

However, please notice that you need to list the record that will be moved one by one .

It's not supported to directly use Filter() to represent all the records to remove.

So you should use formula like this:

Remove('[dbo].[Units]',
First(Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID)),
//the first record 
Last(FirstN(Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID),2)),
//the second record
....
)

2)RemoveIf

syntax:

RemoveIf( DataSource, Condition [, ... ] )

Since in your issue, you do not know the record number that you need to remove, so I more suggest you use RemoveIF function.

The first parameter is data source, the second parameter is condition.

However, in your original formula:

Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID),
    Entry_ID = recordtoDelete.Entry_ID

You have used the condition in the first parameter and you use the same condition in the second parameter.

Please try this formula:

RemoveIf(
   '[dbo].[Units]',
    Entry_ID = recordtoDelete.Entry_ID
);

 

Here's a doc about these two functions in details for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-remove-removeif

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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