I know this is “not possible”. However, Acumatica can do it. Why can’t I?
When creating the Sales Demo data starting in 21R2 or so, someone extended AACUSTOMER to a Vendor, then deleted that vendor account. This returned AACUSTOMER back to just a CU. However, it leaves a record in the Vendor table with DeletedDatabaseRecord = 1.
I want to programmatically “re-extend” the customer to a vendor again. I can’t. If I try to insert a new record in the Vendor table, it sees it as a duplicate. Also, since it is deleted, I can’t edit the existing “deleted” record.
Acumatica has some way to do this. If you use Acumatica to Extend to Vendor for AACUSTOMER, it either 1) permanently deletes the existing vendor record prior to inserting the new record, or 2) it changes the flag on the deleted field to zero. I am guessing it has some way to change the flag.
I thought I could try to re-use the business logic to extend the customer to a vendor using the CustMaintenance graph, but that will throw a redirect to the Vendor graph. My program is a process and cannot deal with user interactions.
Any ideas on how to change the flag on the deleted Vendor (or Customer) record? What is Acumatica’s secret?
Page 1 / 1
SQL script?
UPDATE *tableName* SET DeletedDatabaseRecord = 1 Where AcctCD = 'AACUSTOMER'
Oops, just realized the provided script template would set the value to being deleted. That one should be a zero. Too early in the morning.
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!
Hi, @Joe Schmucker Hope you are doing well!
yes, we can execute the scripts from the Customization package. Please find the steps below.
Open the Customization Project
Click on the Database Scripts → ADD SCRIPT → Add the SQL query
Click on the OK button
Publish the package. On publishing the package, SQL scripts will be executed.
NOTE: Include the scripts in the customization package, which takes less time to execute, otherwise package will not be published and hang the system.
@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.
So you’re trying to perform this programmatically. Got it.
@darylbowman I’ve used that for only deleting records from the tables. Just looked at it a bit. OMG… This is AWESOME!
Since you sound like Scrooge McDuck in a gold mine I’ll give you the obligatory, “use this only when necessary”
@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.
Maybe when it’s finished, Acumatica will absorb into the core product and it’ll be worth it all Cheers to that
@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!!!