Merging two records

For various reasons, data in the system such as customers, ledger accounts, configuration settings, and similar data may become obsolete. This could be because of changes in the business or it could simply be a user input error. For example, two salespeople could create two records for the same customer, start entering sales orders and post invoices. One of the ways to solve that is to merge both records into a single one.

In this recipe, we will explore how to merge one record into another one, including all related transactions. For this demonstration, we will merge two ledger reason codes into a single one.

How to do it...

Carry out the following steps in order to complete this recipe:

1. 1. Open General ledger | Setup | Ledger reasons to find two reason code records to be merged. In this example we will use COUNTER and AUCTION:

   

2. 2. Open the AOT, create a new job named LedgerReasonMerge with the following code:

   static void LedgerReasonMerge(Args _args)
   {
   ReasonTable reasonTableDelete;
   ReasonTable reasonTable;
   ttsBegin;
   select firstOnly forUpdate reasonTableDelete
   where reasonTableDelete.Reason == 'COUNTER';
   select firstOnly forUpdate reasonTable
   where reasonTable.Reason == 'AUCTION';
   reasonTableDelete.merge(reasonTable);
   reasonTable.doUpdate();
   reasonTableDelete.doDelete();
   ttsCommit;
   }
   

3. 3. Run the job to merge the records.

4. 4. Open the Ledger reasons form again and notice that one of the reasons were deleted and all related transactions have also been updated to reflect the change:

   

How it works...

First, we retrieve both records from the database and prepare them for updating.

The key method in this recipe is the merge()method. It will ensure that all data from one record will be copied into the second one and all related transactions will be updated to reflect the change.

Finally, we save changes on the destination record and delete the first one.

All code has to be within the ttsBegin/ttsCommit pair as we perform several database update operations in one go.

Such a technique could be used to merge two, or even more, records of any type.