Friday, January 27, 2012

Delete transactions in Dynamics AX 2012

We're in the process of implementing AX 2012 and we needed to flush out all the transactions from a company. There's a nifty class in AX that's called SysDatabaseTransDelete that does exactly what we need. It loops through the tables in the AOT and, depending on their TableGroup property, will flush out the data.

After cleaning the transactions we needed to delete a few vendors. However we kept getting an error about AOS validation failing. After investigating, we figured out that transactions in VendInvoiceInfoTable still existed... This table is flagged with a new TableGroup value which is TransactionHeader!

In fact, two new TableGroup types have been added in AX2012: TransactionHeader and TransactionLine. We'll need to modify the method SysDatabaseTransDelete.handleTable to support these 2 new cases :


void handleTable(SysDictTable sysDictTable)
{
TableGroup tableGroup;

if (tableSet.in(sysDictTable.id()))
return;
tableSet.add(sysDictTable.id());

if (sysDictTable && !sysDictTable.isTmp() && !sysDictTable.isMap())
{
tableGroup = sysDictTable.tableGroup();

// Handle company specific tables to be deleted
if (sysDictTable.dataPrCompany())
{
switch(tableGroup)
{
case TableGroup::Transaction:
case TableGroup::WorksheetHeader:
case TableGroup::WorksheetLine:
//FIX - Support new AX2012 transaction table types
case TableGroup::TransactionHeader:
case TableGroup::TransactionLine:
this.handleTransTable(sysDictTable);
break;
default:
this.handleNonTransTable(sysDictTable);
break;
}
}
else
{
// Handle global tables to be deleted
switch(tableGroup)
{
case TableGroup::Transaction:
case TableGroup::WorksheetHeader:
case TableGroup::WorksheetLine:
//FIX - Support new AX2012 transaction table types
case TableGroup::TransactionHeader:
case TableGroup::TransactionLine:
this.handleGlobalTransTable(sysDictTable);
break;
default:
break;
}
}
}
}

3 comments:

Tim said...
This comment has been removed by the author.
Tim said...

Hey Matt. I came across your blog while researching removing trail balances posted in the GL. We do a backup\restore from one test environment to another then run the sysdatabasetrandelete class. This does not remove items from GeneralJournalEntry or GeneralJournalAccountEntry so you end up with old items that can throw off you balances at closing. These are global tables and even your changes won't delete data from them since it looks like the sys'delete class does not really do anything for most global tables. Have you had a similar experience?

barath said...

Hey Matt,

Is their any way to delete data from those global tables. Eventually sysdatabasetransdelete class does not work in AX 2012.

Let me know.

Regards,
Barath