How to change DeletedDatabaseRecord from 1 to 0

SQL script?

UPDATE *tableName* SET DeletedDatabaseRecord = 1 Where AcctCD = 'AACUSTOMER'

How can I execute an update to SQL from Acumatica?  Right now I only can create Views.  I actually use a view to retrieve the record with a field to spoof DeletedDatabaseRecord

Since I cannot add DeletedDatabaseRecord in my DAC, I use an alias field.

SELECT BAccountID, DeletedDatabaseRecord AS PreviouslyDeleted, * FROM Vendor WHERE AcctCD = ‘AACUSTOMER’

If there is a way to run an UPDATE statement against a table within the Acumatica framework, that would be life changing!

@Naveen Boga I am familiar with creating scripts as you show.  I use them quite often.  I am working withing the extension library.  My issue is that I want to be able to update the bit on the Vendor/Customer table to change the DeletedDatabaseRecord field to false within my library.  I cannot add that field to a DAC and I don’t see a way to change the field from within my DLL.  At least I can use a VIEW with an aliased field to pull the record and check if it was deleted.  Acumatica has some way to deal with this.  If you extend a vendor to a customer that was previously deleted, it has some way to change the deleted field to false.

My program runs in a process screen.  If I try to utilize the existing graphs in Acumatica to extend the vendor to a customer, it requires user interaction.  The predefined graphs have some magic to change a deleted field to false.  I just can’t find out how.

@darylbowman I’ve used that for only deleting records from the tables.  Just looked at it a bit.  OMG…  This is AWESOME!

@darylbowman I’ve been working on a Vendor/Customer merge utility for the better part of a year now.  In order to get around the integrity logic, I’ve had to use SQL Views.  Without skirting the integrity checking in Acumatica, this program would not be possible.  I have over 175 SQL Views to allow me to “do what I need to do”.  PXDatabase would allow me to do a lot of the work without using SQL Views.

It is amazing that merging one customer into another can affect almost 200 tables.  You can hopefully imagine the level of detail testing that goes into this.  It is no wonder that Acumatica does not offer this feature.  

Time for a re-write.  This will be the third re-write, but if I can ever get it finished to where I feel 100% sure I am not corrupting the database, this will be extremely useful.  Especially for new implementations where imports are done from old systems with duplicate vendors/customers.

FYI, each merge is done within a single transaction.  I will have to try to get PXDatabase to work within a single transaction so that the other caches see the changes made by the PXDatabase actions.  If anything fails in a merge, nothing gets corrupted.  That doesn’t mean that I’ve accounted for EVERYTHING.  For this application, if I miss ANYTHING, I have screwed up a database.  

If the only thing I use PXDatabase to do is change the DeletedDatabaseRecord flag on the Vendor and Customer table, I will still have a viable application.  Know my wheels are spinning on how I can leverage this to simplify my app by orders of magnitude.

Pray for me.  I’ve invested so much effort into this, I cannot give up on it.

@darylbowman One SQL View removed using PXDatabase Update.  175 left to go!  

If Acumatica saw my code, they would probably tell me it would be a cold day in Hell before they absorb it!  HA!!!