Delete duplicate records of a table AX2012

Hi Guys,

A code snippet to delete duplicate records of a table and keeping just one of the duplicate set.

static void AKS_DeleteDuplicates(Args _args)
{
    EcomInboundInventoryUpdate      table, tableSelect, tableDelete;

    while select table group by wmsreferencenum
        where table.EcomProcessStatus == EcomProcessStatus::Waiting
    {

        select firstOnly RecId from tableSelect
            where tableselect.WMSReferenceNum == table.WMSReferenceNum;


        delete_from tableDelete
            where tableDelete.WMSReferenceNum == table.WMSReferenceNum &&
                  tableDelete.RecId != tableSelect.RecId;
    }
}

Delete all transactions Ax2012

There is a class named "SysDatabaseTransDelete" in Axapta which can be used to delete all the transactions from your Ax Db but will not delete the base setups. In that case we can use the same DB as Production Db.

NOTE: It works perfectly with Ax2012 CU3 and above as it is now updated for new DB structure. Otherwise you may face issues with General Journal/Trial balance data.

It deletes all data from tables in the Transaction, WorksheetHeader and WorksheetLine table groups plus the following tables...

       SalesTable

       PurchTable

       WMSPallet

       CustInterestJour

       CustCollectionLetterJour

       ProjControlPeriodTable

       ProjInvoiceJour

       ProjJournalTable

       PdsRebateTable

       PdsBatchAttributes

You can run it from the AOT by right clicking on the SysDatabaseTransDelete class node and selecting "open" - this isn't the sort of thing you would want to put on a menu anywhere.

Here is a job for the same:

static void deleteTransactions(Args _args)
{
SysDatabaseTransDelete delete;
;
delete = new SysDatabaseTransDelete();
delete.run();
}

Delete company in AX2012 using SQL

Sometimes we are not able to delete company from AX due to existing transactions then SQL comes to the rescue and does the job beautifully.

There is a Stored Procedure in "master" database named "sp_MSforeachtable". Use the below commands:

This will delete all the transactions from all the tables related to that particular company:

EXEC sp_MSforeachtable 'delete from ? where ?.DataAreaID = "CEU"'

This will delete the company name from the company list in AX:

DELETE FROM DATAAREA WHERE DATAAREA.ID = 'CEU'