cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
Community Support
Community Support

Re: Deleting Multiple Rows in SQL

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
Highlighted
Super User III
Super User III

Re: Deleting Multiple Rows in SQL

@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."

Highlighted
Helper III
Helper III

Re: Deleting Multiple Rows in SQL

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

Highlighted
Community Support
Community Support

Re: Deleting Multiple Rows in SQL

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

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,637)