Skip to main content
Solved

Mass deleting records from a custom Acumatica table

  • 24 March 2022
  • 8 replies
  • 719 views

Before anyone panics, I am not planning on deleting ANYTHING from a core Acumatica table! :-)

I’ve read this post but it involved deleting in a “one-time” fashion using export scenarios.  I don’t think that is an option for my use case.

My requirement to to create a SQL View that can pull data using a GI.  Got that under control.  The issue for me is that it is not possible (AFAIK) to create a View in SQL to pull the data and de-normalize it without using a loop in SQL, which is not allowed in a SQL View.

My plan is to create a custom screen that can use C# in an Acumatica customization to process the data and store it in a custom SQL table.  When a user uses the custom screen to pull data into the table, I want to delete EVERYTHING from that table that was put in there previously by that user.  Otherwise this table will grow very large for no useful purpose.

I know I can pull a record set and cycle through it and delete rows one at a time.  Is there an option in Acumatica I have not found to allow you to do a delete from a table for multiple records?  (delete joetable where UserID = ‘joeuser’)

I could do it easily with a stored procedure but I think that’s a big no-no in Acumatica (or if it is actually possible...I recall reading a post where someone wanted to execute a sproc and they were told never to try doing that.

Thanks,
Joe

Thank you @joe21 to bring this one. I too tried the same and did not get any solution. :)

 

Have you considered using PXDatabase.Delete?

For example: Using PXDatabase to Delete Records with Null Fields - Stack Overflow

Have you considered using PXDatabase.Delete?

For example: Using PXDatabase to Delete Records with Null Fields - Stack Overflow

I’ve never used that before.  I’ll look into it and see if I can utilize that.

Thanks for the reply!  

Hi @darylbowman 

If using PXdatabase.Delete, Taking more time to delete records and get a timeout error (We tried to delete logs which is  more than 25k records)  

@joe21  To delete less data the PXdatabase.Delete will work.

 

sample code:

  PXDatabase.Delete<TableName>(new PXDataFieldRestrict<TableName.createdDateTime>(PXDbType.DateTime, 500, clearDate, PXComp.LE));
 

@jinin - I’ve never tried it with that much data, but did you try putting it into a PXLongOperation? I know that runs in a separate thread. I don’t know if that would prevent a timeout.

Hi @darylbowman ,

I didn't try with PXLongOperation, will try with this.  Thank you for the reply.

 

Hello @joe21, You can try and create an index on the table which contains the log in the columns used inside the WHERE condition and add CompanyID in the query as well. That should help you fasten the query execution and eliminate timeout error.

I found a way to do what I needed.  

A solution was provided by https://community.acumatica.com/members/shawn-burt-5805 in another thread.  

This is what I used to delete a mass set of records:

            PXDatabase.Delete<QTCAuditHistoryReport>(new PXDataFieldRestrict<QTCAuditHistoryReport.reportingUserID>(_currentUser),
                new PXDataFieldRestrict<QTCAuditHistoryReport.screenID>(Filter.Current.ScreenID),
                new PXDataFieldRestrict<QTCAuditHistoryReport.changeDate>(Filter.Current.ChangeDate));
 

Reply


Terms & ConditionsCookie settings