I noticed that sometimes for various operational reasons, people by accident create duplicate records in the system like vendors, customers, ledger accounts, etc and start entering transactions against them. The reasons could vary from rushing to input new information to the lack of knowledge of how to use the system. But regardless of why this happened, they will always need someone with technical skills to fix it.
In this recipe, we will explore how to correct such a situation by merging two records including all their related transactions. For the demonstration, we will merge two vendor accounts 5001 and 5002 into a single one, that is, 5001.
Open AOT, create a new job called VendAccountMerge, and enter the following code:
static void VendAccountMerge(Args _args) { VendTable vendTable; VendTable vendTableDelete; PurchJournalAutoSummary jourSummary; #define.vend('5001') #define.vendDelete('5002') ; ttsbegin; delete_from jourSummary where jourSummary.VendAccount == #vendDelete; select firstonly forupdate vendTableDelete where vendTableDelete.AccountNum == #vendDelete; select firstonly forupdate vendTable where vendTable.AccountNum == #vend; vendTableDelete.merge(vendTable); vendTable.doUpdate(); vendTableDelete.doDelete(); ttscommit; }Open Account payable | Vendor Details to check the vendors to be merged:
Run the job to merge the vendor accounts.
Once vendor accounts are merged, all their related records are going to be merged too, that is, all transactions, contacts, addresses, configuration settings, and so on from both vendors will be moved to a single one. Normally, there are no issues with merged transactional data, but vendor configuration settings may issue duplicate errors.
For example, vendor 5001 has bank account Bank1 and vendor 5002 has bank account Bank2. After merging both vendors, vendor 5001 will have both bank accounts attached. If both bank accounts were named the same, then the system would issue a duplicate record error.
So before we start merging records, we need to either manually or programmatically delete or rename settings that might issue duplicate errors. In this example, in the first block of code, right after the variable declaration, we delete only the default auto-summary posting settings, which are stored in the PurchJournalAutoSummary table. Depending on the circumstances, other settings like vendor bank accounts, requests for quotes, and so on, have to be corrected before the actual merge.
Next two blocks of code find both vendor records for further updating.
Calling member method merge() on the record to be deleted transfers all of its data and related records to the destination, which is specified as a first argument.
The last thing to do is to save the destination record and delete the first one.
Such a technique can be used to merge two or even more records. Besides vendors, it could also be customers, ledger accounts, and other such similar records. Every case has to be investigated separately as each record type contains different relations with other tables.