Solved

Mass deleting records from a custom Acumatica table

  • 24 March 2022
  • 8 replies
  • 612 views

Userlevel 6
Badge +3

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

icon

Best answer by Joe Schmucker 27 March 2022, 19:52

View original

8 replies

Userlevel 7
Badge +11

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

 

Badge +10

Have you considered using PXDatabase.Delete?

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

Userlevel 6
Badge +3

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!  

Userlevel 7
Badge +11

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

Badge +10

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

Userlevel 7
Badge +11

Hi @darylbowman ,

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

 

Userlevel 2

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.

Userlevel 6
Badge +3

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


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved